In this lesson, you'll learn how to connect and log into SQL Server, how to issue SQL Server statements, and how to obtain information about databases and tables.
Making the Connection
Now that you have a SQL Server DBMS and client software to use with it, it would be worthwhile to briefly discuss connecting to the database.
SQL Server, like all client/server DBMSs, requires that you log into the DBMS before being able to issue commands. SQL Server can authenticate users and logins using its own user list, or using the Windows user list (the logins used to start using Windows). As such, depending on how SQL Server is configured, it may log you in automatically using whatever login you used for Windows itself, or it may prompt you for a login name and password.
When you first installed SQL Server, you were probably prompted for an administrative login (often named sa for system administrator) and a password. If you are using your own local server and are simply experimenting with SQL Server, using this login is fine. In the real world, however, the administrative login is closely protected because access to it grants full rights to create tables, drop entire databases, change logins and passwords, and more.
To connect to SQL Server, you need the following pieces of information:
The hostname (the name of the computer). This is localhost or your own computer name if you're connecting to a local SQL Server.
A valid username (if Windows authentication is not being used).
The user password (if required).
If you're using one of the client applications discussed in the previous lesson, a dialog box will be displayed to prompt you for this information.
Note: Using Other Clients - If you are using a client other than the ones mentioned previously, you still need to provide this information in order to connect to SQL Server.
After you are connected, you have access to whatever databases and tables your login name has access to. (Logins, access control, and security are revisited in Lesson 29, "Managing Security.")
Selecting a Database
When you first connect to SQL Server, a default database is opened for you. This will usually be a database named master (which as a rule you should never play with). Before you perform any database operations, you need to select the appropriate database. To do this, you use the USE keyword.
Plain English: Keyword - A reserved word that is part of the T-SQL language. Never name a table or column using a keyword. Appendix E, "T-SQL Reserved Words," lists the SQL Server keywords.
For example, to use the crashcourse database, you would enter the following (in a query window):
Command(s) completed successfully.
The USE statement does not return any results. Depending on the client used, some form of notification might be displayed (as seen here).
Tip: Interactive Database Selection - In SQL Server Management Studio (or SQL Query Analyzer), you may select a database from the drop-down list in the toolbar to use it. You'll not actually see the USE command being issued (although it is being issued for you), but the database will change and the window title bar will reflect this change.
Remember, you must always USE a database before you can access any data in it.
Learning About Databases and Tables
But what if you don't know the names of the available databases? And for that matter, how do the client applications obtain the list of available databases that are displayed in the drop-down list?
Information about databases, tables, columns, users, privileges, and more, are stored within databases and tables themselves (yes, SQL Server uses SQL Server to store this information). These internal tables are all in the master database (which is why you don't want to tamper with it), and they are generally not accessed directly. Instead, SQL Server includes a suite of prewritten stored procedures that can be used to obtain this information (information that SQL Server then extracts from those internal tables).
Note: Stored Procedures - Stored procedures will be covered in Lesson 23, "Working with Stored Procedures." For now, it will suffice to say that stored procedures are SQL statements that are saved in SQL Server and can be executed as needed.
Look at the following example:
DATABASE_NAME DATABASE_SIZE REMARKS ----------------- ------------- ------- coldfusion 9096 NULL crashcourse 3072 NULL forta 2048 NULL master 4608 NULL model 1728 NULL msdb 5824 NULL tempdb 8704 NULL
sp_databases; returns a list of available databases. Included in this list might be databases used by SQL Server internally (such as master and tempdb in this example). Of course, your own list of databases might not look like those shown above.
To obtain a list of tables within a database, use sp_tables;, as seen here:
sp_tables; returns a list of available tables in the currently selected database, and not just your tables; it also includes all sorts of system tables and other entries (possibly hundreds of entries).
To obtain a list of tables (just tables, not views, and not system tables and so on), you can use this statement:
sp_tables NULL, dbo, crashcourse, "'TABLE'";
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME TABLE_TYPE REMARKS --------------- ----------- ------------ ---------- ------- crashcourse dbo customers TABLE NULL crashcourse dbo orderitems TABLE NULL crashcourse dbo orders TABLE NULL crashcourse dbo products TABLE NULL crashcourse dbo vendors TABLE NULL crashcourse dbo productnotes TABLE NULL crashcourse dbo sysdiagrams TABLE NULL
Here, sp_tables accepts a series of parameters telling it which database to use, as well as what specifically to list ('TABLE' as opposed to 'VIEW' or 'SYSTEM TABLE').
sp_columns can be used to display a table's columns:
Note: Shortened for Brevity - sp_columns returns lots of data. In the output that follows, I have truncated the display because the full output would have been far wider than the pages in this book, likely requiring many lines for each row.
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME crashcourse dbo customers cust_id 4 int identity crashcourse dbo customers cust_name -8 nchar crashcourse dbo customers cust_address -8 nchar crashcourse dbo customers cust_city -8 nchar crashcourse dbo customers cust_state -8 nchar crashcourse dbo customers cust_zip -8 nchar crashcourse dbo customers cust_country -8 nchar crashcourse dbo customers cust_contact -8 nchar crashcourse dbo customers cust_email -8 nchar
sp_columns requires that a table name be specified (customers in this example), and returns a row for each field, containing the field name, its datatype, whether NULL is allowed, key information, default value, and much more.
Note: What Is Identity? - Column cust_id is an identity column. Some table columns need unique values (for example, order numbers, employee IDs, or, as in the example just shown, customer IDs). Rather than have to assign unique values manually each time a row is added (and having to keep track of what value was last used), SQL Server can automatically assign the next available number for you each time a row is added to a table. This functionality is known as identity. If it is needed, it must be part of the table definition used when the table is created using the CREATE statement. We'll look at CREATE in Lesson 20, "Creating and Manipulating Tables."
Lots of other stored procedures are supported, too, including:
sp_server_info: Used to display extensive server status information
sp_spaceused: Used to display the amount of space used (and unused) by a database
sp_statistics: Used to display usage statistics pertaining to database tables
sp_helpuser: Used to display available user accounts
sp_helplogins: Used to display user logins and what they have rights to
It is worthwhile to note that client applications use these same stored procedures you've seen here. Applications that display interactive lists of databases and tables, that allow for the interactive creation and editing of tables, that facilitate data entry and editing, or that allow for user account and rights management, and more, all accomplish what they do using the same stored procedures that you can execute directly yourself.
In this lesson, you learned how to connect and log into SQL Server, how to select databases using USE, and how to introspect SQL databases, tables, and internals using stored procedures. Armed with this knowledge, you can now dig into the all-important SELECT statement.