Lesson 3: Working with SQL Server

August 14, 2007

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):

Input

USE crashcourse;

Output

Command(s) completed successfully.

Analysis

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:

Input

sp_databases;

Output

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

Analysis

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:

Input

sp_tables;

Analysis

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:

Input

sp_tables NULL, dbo, crashcourse, "'TABLE'";

Output

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

Analysis

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:

Input

sp_columns customers;

---

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.

---

Output

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

Analysis

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.

Summary

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.