Quick Microsoft SQL Server Tutorial

Author: Yoshi Watanabe, December 2004

Intended Audience: Beginner Microsoft SQL Server Transact-SQL Programmer

This tutorial helps you feel comfortable with Microsoft SQL Server 2000. It provides you with a series of steps that you should follow. It will reveal the way Microsoft SQL Server 2000 database is organized and how it manages objects that you create for your applications. Give yourself an hour or so to go though these 31 steps and you probably feel a lot more comfortable with SQL Server 2000 when you are done.

Introduction

If you need to quickly get up to the speed with SQL Server 2000 programming, then this tutorial is for you. Four weeks ago, I started a new job where I am required to write stored procedures against existing schema and I was a bit overwhelmed, especially when I had basically zero professional experience writing stored procedures (I've been predominantly C/C++ and recently .NET programmer). In particular, I was not confortable with the fact that I did not know what all these tables that have names like sysxxx, are for. Appearently, these tables are created automatically for you even for a freshly created database. Obviously these tables are system tables, but their purposes were not clear to me. The reverse side of the coin is: if I learn the purposes of these tables, I will probably gain much better understanding of SQL Server. The best approach for me attach this problem was to explorer the database using just SQL statements. This means not to use any GUI-based front-end, namely SQL Server 2000 Enterprise Manager. With only SQL statements availalbe to me, I was forced to understand the contents of those sysxxx table, which allows me to gain bottom-up understanding of SQL Server's organization.

As mentioned above, SQL Server 2000 comes with a GUI front-end called Enterprise Manager, with which you can graphically interact with the SQL Server. On the other hand, a tool called SQL Query Analyzer, which has some GUI-elements but mostly a command-line level interface to SQL Server 2000 databases. Their relationship is simlilar to that of Windows Explorer and cmd.exe: one is a GUI and the other is a command-line front-end to a system. While using a command-line interface is combersome, you also gain insights to how the system works.

In this tutorial, we only use SQL Query Analyzer. However, you may also want to use Enterprise Manager to examine the sysytem: a different perspective usually enhances your understanding of the subject.

Step 0

I assume that you already have a Microsoft SQL Server 2000 installed on your machine. I have a Devloper edition installed on my local machine, so the database server is "localhost". You can use another database server on the network if you prefer as long as it has the Northwind sample database, which I am using in this tutorial.

Step 1

Launch "SQL Query Analyzer", then issue File->New menu command. Select "(localhost)" for SQL Server, then press OK. You will see a blank query window. That's our play ground. It is a good idea to save the file right now even though it is empty. Next, look at the toolbar of SQL Query Analyzer. It should have a drop down control that says "master". This means that you are currently working with 'master' database on localhost server.

In the blank query window, type the following line, and then hit F5.

use Northwind

A new pane appears at the bottom of the query window, which says "The command(s) completed successfully." Also look at the drop down control in the toolbar. You see the text "Northwind" in the control. Any SQL statement you execute in the query window from now on is done against Northwind database by default.

This is somewhat anologuous to:

C:\>cd Northwind

at Windows command prompt. It changes the current database to whichever one you specify.

Step 2

In a new line, type the following statement, select the line just being added using the mouse (or keyboar), and then hit F5.

sp_databases

You see a result table in Grids tab, which is a list of all databases in the SQL Server. It includes 'Northwind', 'master', 'tempdb', and all other databases that are currently available in that particular instance of SQL Server

Warning: Make sure that you select the line that contains 'sp_databases' or you will encounter an error. In SQL Query Analyzer, the only selected (highlighted) statements are executed. This is handy since we can keep building additional SQL statements and only execute a section of it.

This is somewhat anologuous to:

c:\>dir

at command prompt. It lists all the databases that you can change (using 'use' command described in Step 1).

By the way, 'sp_databases' is one of the many System Stored Procedures built in SQL Server. You can look it up in Help for more details about it.

Step 3.

In a new line, type the next statement, select the line just being added, and then hit F5.

use master

Now, you changed the current database back to 'master' database. One thing to remember about this 'master' database. This is the most important system database within SQL Server. You do not want to mess with 'master' database, just like you don't want to with C:\Windows\System32 directory. It contains critical information such as information about other databases.

Step 4

In a new line, append the next statement, select the line just being added, and then hit F5.

select * from sysdatabases

You see a result that is similar to what you got using 'sp_databases' stored procedure), but this time there are a lot more information about each database.

Note that this statement had to be executed while 'master' is the current database. If you tried this statement while you are in Northwind (or any other database except 'master') then you get an error because 'sysdatabases' table exists only in 'master' database.

Step 5

Add the following three lines, highlight all three lines, and then hit F5.

use Northwind

go

select * from master.dbo.sysdatabases

You get the exact same result as before. We switched to Northwind database, but we were stil able to examine a table in 'master' database.

This is anologuous to:

C:\>cd "Program Files"

<Return>

C:\>dir c:\Windows

You can use this form of "fully qualified" names. Notice that there is "dbo" element in the fully qualified name. For now, don't worry about it.

Step 7

Add the following line, highlight it, and then hit F5

sp_helpdb Northwind

You see two tables returned by this statement and it contains some information about 'Northwind' database.

This is somewhat analoguous to viewing "Properties" of a file in Windows file system. For now, ignore the information in it.

Step 6

Add the following line, highlight it and hit F5.

select * from sysobjects

The result looks like this:

Note that the current database is 'Northwind' and the table we are examining is 'sysobjects' not 'sysdatabases'. We get a result this time because there is a table called 'sysobjects' in every database in SQL Server. Take a look at the rows. Did you get the feeling that there are several kinds of objects in the table? It does contain objects of many kinds, including tables, sprocs, views, etc.

This is somewhat analogous to:

c:\Northwind\>dir

What is different from a file system is that database contains many different kinds of objects such as "system table", "user table", "stored procedures", while a file system typically contains two kinds of objects, namely files and directories (If you include shortcuts, then that's three.)

Step 7

select * from sysobjects where xtype = 'S'

This is a slight modification of the previous statement.

Note that I added "where xtype = 'S'" caluse to the select statement. This returns only the "system tables" entries in 'sysobjects' table because 'xtype' column of "system tables" have value set to "S". You notice that all returned items start with "sys" prefix. These tables contain lots of interesting information as we will see later.

Step 8

select * from sysobjects where xtype = 'U'

Now, this returns all "user tables" from the current database.

The returned items include 'Orders' table, 'Products' table, 'Order Details' table etc. Notice that you don't see any of the tables that starts with sys prefix.

Step 9

select * from sysobjects where xtype = 'P'

This is yet another variation of querying 'sysobjects' table.

This time we told it to fetch rows that are indicated as 'Stored Procedure" by looking for those with xtype='P' as in Stored Procedures. You see items like 'Ten Most Expensive Products' and 'Employee Sales by Country'. These are the stored procedures that are part of Northwind database and you can execute them.

Step 10

Add the following line exactly, select the entire line, and then hit F5

"Ten Most Expensive Products"

Congratulations! You have just executed one of the user-defined stored procedures. Here is the result of executing that stored procedure.

It is anologuous to making a function call in a procedural programming languages like C++ and C#. Notice that in SQL, you can name a stored procedures that have spaces. This particular procedure does not take any parameter, as in C/C++ function void Foo(void), so you did not have to provide any parameter to it.

Step 11

"Sales by Year" '12/1/1996', '12/31/1996'

This is another stored procedure in Northwind database, but this one takes two parameters. Examine the stored procedure by doing the following:

Hit F8 to open Object Browser windiw in Query Analyzer
Under your machine's name, find Northwind node, and expand the subtree.
Fine Stored Procedure node, and expand the subtree.
Toward the end, find "dbo.Sale by Year"
Right-mouse click it and select "Edit" menu. A window with the stored procedure opens.
When you are done, close the window and say "No" when prompted to save the file.

If you look at the stored procedure definition, you see that it takes a begining date and ending date. Check the returned rows to see that rows are constrained to the time range you specified.

Note that immediately following the name of the procedure, "Sales by Year", are two prameters. These are the parameters that you passed in the above example.

@Beginning_Date DateTime
@Ending_Date DataTime

Step 12

select * from Orders

This shows all columns and all rows in 'Orders' table.

Note that "select *" the way to instruct the select statement to show all coumns, that include OrderID, CustomerID, EmployeeID, etc. It is often confused that this "select * " is the cause of getting all rows. Which rows you will get is controled by other means and not by "select *".

Step 13

sp_help Orders

This system sproc returns a lot of information.

This is analoguous to looking at Properties dialog box for Orders table. It contains information like columns in the table, primary key, foreign key(s), name of the tables that 'Orders' table references, and tables that references 'Orders' table.

From the result you can learn:

There are 14 columns in 'Orders' table (you must scroll to see the hidden ones)
'Orders' table references three other tables: 'Customers', 'Employees', 'Shippers' tables. (Again, you must adjust the pane to see the hidden information)
'Order Details' table references 'Orders' table.

Note that 'sp_help' is not just for Tables but also for any objects that are in sysobjects table. Try specifying the name of any item in sysobjects table to see what sort of information is provided by this system sproc.

Remember that when you want to learn anything about an object such as table, use sp_help system sproc.

Step 14

sp_columns Orders

Using 'sp_columns', you can get even more information about the columns of a table. I like to use this sproc when I just want to learn about columns of a table.

You can learn:

Name of each column and its type
Lots more informatoin...

Remember that when you want to learn the details about columns of a table, use sp_columns system sproc.

Step 15

select * from syscolumns

This SQL statement shows all columns that exist in the current database. Below, I scrolled to the part so that it shows the columns for user tables such as Orders.

Yes, all columns in all tables are recorded in syscolumns table. 'sp_columns' sproc is nothing more than a filteration function into this system table. Right now, it returns too many rows and isn't very useful. Let's control the rows that get returned.

Step 16

select * from syscolumns where id = 1

We added a simple condition to filter the large result set into a meaningful subset.

Now, the result contains items that correspond to the columns of a table that is identified by the number 1. Where does this value 1 come from?

Let's find that out.

Step 17

select * from sysobjects where id = 1

Note that we are querying sysobjects here, not syscoumns!

You see a single result entry which is an object called 'sysobjects'. Yes, sysobjects table itself is a member of sysobjects and it has id=1.

It is nice to know that id of 1 is for 'sysobjects' table, but we want to know ids of user tables which we are more interested in, like the tables in 'Northwind' database.

Step 18

select * from sysobjects where name = 'Orders'

This returns a single row result table, and it is our 'Orders' table.

What we wanted to find out here is the id of 'Orders' table. I am not sure if the value is the same on your SQL Server installation but mine says '21575115'. So, within Northwind database, 'Orders' table is uniquely identified with id= '21575115'. With this information, we can find out the columns that belong to 'Orders' table.

Step 19

select * from syscolumns where id = 21575115

We are back to querying columns, but this time we apply a filter to obtain only those that belong to 'Orders' table.

Compare the result with what 'sp_columns' gives to you. They are very similar. The select statement you just executed is a manual way to acheive what 'sp_columns' does. Its just that 'sp_columns' is a nice wrapper procedure to do pretty much the same thing.

Step 20

select id from sysobjects where name = 'Orders'

Now, here is a chance to see if you are still awake. This statement is slightly different from Step 18. Can you spot the difference? Yes, we specify 'id' instead of '*' at select statement. This is a way to retrieve a single column value from the result rows as the result shows below.

In the Grids pane (or Messages pane if you configured Messages pane to be the output), you should see a single "cell" that contains 21575115 (could be different on your machine)

Step 20

select * from syscolumns where id = (select id from sysobjects where name = 'Orders')

OK, now we got some nesting action going on.

First of all, note that the result is identical to the one you get using the statement described in Step 19. What we accomplished here is not to use any hard-coded values like 21575115 but instead retrieved using another SQL query statement and simply plugged it to the parent query statement. This is bettern than Step 19 because it makes it portable accross different machines where table ids may be different.

Step 21

sp_helpconstraint 'Orders'

This is easier way to learn about relationships a given table participates. Notice that there are three FOREIGN KEY constraints. The result pane has more columns values so select the pane and scroll horizontally to see the rest of the columns.

For example, 'Orders' table references three other table and also being referenced by one table. 'sp_help' sproc also provided the same information but sp_helpconstraint is more focused on just providing "constraint" information. You notice the keyword "foreign key" appearing in many places. Foreign key is what connects two tables together. 'Orders' table has three foreign keys.

Remember to use 'sp_helpconstraints' when you want to know table references.

Step 22

sp_fkeys 'Orders'

This is when you are interested in knowing just "who references" me. In the result, locate value for the following columns:

FKTABLE_NAME
FKCOLUMN_NAME

They tell you which table has the foreign key and the name of the column that is the foreign key. Think of it as a "pointer" in another table that is point to a column in Orders table.

There are many system sprocs that provide overlapping information (e.g. both 'sp_helpconstraint' and 'sp_fkeys' provide the same information about "who references" the table) and it is up to you to use either one. You can even avoid using either and roll your own using select statement againt the system table.

Step 23

select * from sysforeignkeys

It is not a surprise now that there is a system table that describe all foreign keys defined within a database. The above statement shows the content of 'sysforeignkeys' table, which include the three foreign keys defined in 'Orders' table.

This table does not have names so it is hard to understand which foreign keys are for which table relationships.

In the next step, we select only those that are foreign keys into Orders table.

Step 24

select * from sysforeignkeys where fkeyid = (select id from sysobjects where name = 'Orders')

This is an extension of Step 23 but we provide id of 'Orders' table to the equality condition in order to get only the foreignkeys for 'Orders' table.

The returned table shows three foreign keys as we expected. Note that we use the same sub-query expression that we used in Step 20.

Step 25

sp_depends 'Orders'

This system sproc is convenient when you want to find out how risky it is to modify the table schema by checking which stored procedures and views depends on the table. In this case, five "stored procedures" and eight "views" depend on 'Orders' table. This way, you know which sproc and views you should test to see if any of the modifications you make to a table is valid or not.

The following table is showing which stored procedures and views depend on the Orders table.

It is important to remember that the notion of "depends" exists only between sproc/view to tables, but not between tables to tables. The "references" relationship between two tables is expressed in terms of foreign key constraints.

Step 26

select * from sysdepends where depid = (select id from sysobjects where name = 'Orders')

Yes, sp_depends is nothing but a thin wrapper function to access a system table, called 'sysdepends', that contains information about dependencies. One odd thing about result returned from the above statement is that there more than 13 rows.

How come there are more than 13? If you look at 'id' column carefully, you see some duplicates. I began to wonder that perhaps sysdepends table record all references to the said table within stored procedure code body and views. For example, if a stored procedures refers 'Orders' table 5 times in its code body, then there are 5 entries made in sysdepends.

Step 27

select id from sysdepends where depid = (select id from sysobjects where name = 'Orders')

This is a slight modification from Step 26. We requested SQL Server to return only the values of 'id' column.

It is clear that there are lots of duplicates. Now, is there any UNIX uniq kind of command to show only unique enties? Yes, theres is.

Step 28

select distinct id from sysdepends where depid = 21575115

This version is a further modification to Step 27. It specifies "distinct" keyword so that the result set will not contain any duplicate rows.

If you count the number of rows returned, you realized that it is 13. So, 'sysdepends' does contains information about dependents but its not a simple one entry per dependent. Instead, it records every single refernce points within stored procedures and views. When the references are collapsed, you get the number of stored procedures and views that depend on the object, just like sp_depends reported to you. The point here is not to understand this complexity but to realize that system tables record alot of things about your database.

Step 29

select * from sysobjects where xtype = 'U' and name like 'e%'

OK, let's switch a gear and get back to listing objects from 'sysobjects' table. A couple of useful string matching pattern is going to be helpful when you look into these system tables (and user tables). This statement shows how you can do a simple partial string matching search.

The result set contains two tables:

Employee
EmployeeTerritories

It is roughly equlivalent of doing this:

c:\>dir e*

where only files that start with a letter 'e' is displayed.

Step 30

select * from sysobjects where name like '[e-g]%'

Another example for string pattern matching.

This time, the result set contains any object whose name start with either 'e', 'f' or 'g' (case insensitive).

That' all. Here are the summary of what have been discussed in this tutorial.

There are many system stored procedures that help you obtain information about objects within a database.
There are system tables, with "sys" prefix, that contain information about all kinds of attributes of and relationships among objects.
SQL Server database feels like a file system and you can navigate it almost like a file system.
There are many ways to obtain the same kind of information and it is up to you to choose which one you would use.

Do you want more?

In this tutorisal, we covered these system tables: sysdatabases, sysobjects, columns, sysforeignkeys, sysdepends. There are more systables that are straightforward and easy to understand. The ones I recommend you to investigate are:

syscomment (hint: where are the stored procedures actually stored?)
sysindexes and sysindexkeys (hint: you can create indexes for tables)
sysfiles, sysfiles2, and sysfilegroups (hint: a database has an associated set of disk files)