By: Mitchell Harper

April 10th, 2002

Reader Rating: 9

Try surfing around the Internet and find a popular site that doesn't use some sort of database to store its content. I bet you'll have a tough time finding just one popular site (i.e. receiving over 5,000 unique visitors a day) that doesn't use some sort of DBMS, such as SQL Server 2000, MySQL, or Oracle.

As I'm sure you're aware, it's extremely difficult to build a dynamic Website without a way to access and manipulate your content dynamically. Back in 1995 when the majority of Websites were still serving static content (i.e. plain HTML files), database driven sites were nowhere near as popular as they are today. Why do you think this is? Are databases easier to setup today? Is Web server software more usable? Or was it just a natural progression that we as Web developers went through to "evolve" from the way we developed "back then"?

I like to think it's all of these things. Today we have databases such as MySQL and SQL Server that can be set up on a development machine with very little expertise: you simply activate the setup program, change a couple of options and you're up and running in 20 minutes or so. It also seems that databases are "the way to go" these days, which makes sense when you think about it: they're readily available (some such as MySQL are free), some are supported cross-platform, and others are supported with native providers or pure source code libraries.

In this article we're going to take a beginner's look at Microsoft SQL Server 2000. We'll look at how to create our first database, and also how to manipulate this database through both Enterprise Manager and Query Analyzer. If you've never worked with SQL Server 2000 before but have always been curious, then this article's guaranteed to get you up and running in no time.

Before we proceed, however, you should have SQL Server 2000 installed on your machine. I'm running Windows 2000 Advanced Server, but SQL Server 2000 can also be installed on Windows 2000 Server, Windows 2000 Datacenter Server, and Windows NT 4.0 with service pack 5. If you'd just like to fiddle around with SQL Server 2000 for the span of this article, then you'll be glad to know that you can download a free 120-day trial version of it by clicking here [1].

If you have any trouble with your installation then check MSDN [2] or consult the reference material that was included with your copy of SQL Server 2000.

Creating a Database with Enterprise Manager

Before we create our first SQL Server 2000 database, let me fill you in on a couple of things. Firstly, SQL Server 2000 is a RDBMS (Relational Database Management System), which means that it allows you to create, update and administer relational databases. A relational database is a collection of data items that can be accessed and manipulated in many ways without actually changing the organization of the tables within that database.

Secondly, SQL Server 2000 supports its own dialect of SQL (Structured Query Language), which is known as Transact-SQL, or TSQL for short. TSQL is an extremely powerful and advanced set of commands that can be used to create, modify, query and delete databases, tables, triggers, constraints, etc. It's based on the ANSI SQL 92 standard but takes this standard into its own hands so to speak, adding that extra touch of flexibility and robustness to the commands that it supports.

Thirdly, as with many RDBMSs, there's always more than one way to accomplish a particular task. For example, Enterprise Manager lets you create tables using a familiar point and click interface similar to basic desktop databases like MS Access. Query Analyzer, on the other hand, acts more like command-driven databases such as MySQL, allowing you to get right down to the bare metal and code everything yourself. Throughout this article we will accomplish many things firstly with Enterprise Manager, and then with plain TSQL code in Query Analyzer.

Now that we've got a bit of jargon out of the way, let's create our first database. Jump onto the PC where you installed SQL Server and click Start -> Programs -> Microsoft SQL Server. You should see the following menu:

The two programs that we're interested in working with are Enterprise Manager and Query Analyzer, so click on the Enterprise Manager item to load it up. When it loads, you'll notice two panels: the first is a tree view and the second is a list of items contained within the selected node of that tree.

Expand the "Microsoft SQL Servers" branch in the left panel and also expand the "SQL Server Group" branch. Enterprise Manager should look something like this:

My computer's Network name is SERVER, and as you can see in the screenshot above, SQL Server represents my computer as a node called SERVER. Obviously this node will appear with your computer's Network name next to it. Expand this node and you'll see a number of folders appear in the right pane: databases, data transformation services, management, replication, security, support services and meta data services. In this article we're only concerned with databases, so expand the databases tab.

Each installation of SQL Server 2000 also includes a master database. One of the most important tables in the master database is the sysdatabases table, which contains a list of databases that reside on your SQL Server. To view the contents of the sysdatabases table in your master database:

• expand the master node under your databases node in the left pane
• click on its tables node, and then
• find a table called sysdatabases in the right pane.

If you can't find the sysdatabases table then click on the right pane and start to type the first few letters of sysdatabases. Enterprise Manager will highlight the table for you. Once you've found it, right click on it and choose the Open Table -> Return all rows option. Take a look at the various fields and values in the table, and then close it by pressing Ctrl+F4.

If you take a look at the list of databases under the databases node in the left pane, then you'll also notice the Northwind and pubs database, which come bundled with the SQL Server 2000 installation. These databases are not tied to the workings of SQL Server 2000 in any way, and you're free to do whatever you want with them.

However, instead of playing around with the Northwind or pubs databases, let's create our own. Right click on the databases node in the left pane and choose the New Database option. The database properties dialog will appear:

The dialog contains three tabs; however, all we really need to do to create a new database is give it a name, so enter MyDatabase1 in the name field and click OK. If you're feeling confident then you can set the location of the database file and its growth settings by clicking on the Data Files tab. By default our new database will be created as C:\Program Files\Microsoft SQL Server\MSSQL\data\MyDatabase_Data.MDF, but you can change this to any location that you like. You can also change the location and growth settings of the transaction log for our database by clicking on the Transactions Log tab, but we won't worry about that for now.

If you take a look at the databases node in the left pane then you'll see that our MyDatabase1 database now exists in the list. Expand the MyDatabase1 node in the left panel. We're now going to create a table within it. Right click on the tables node and choose the New Table option. This will load the table designer, which allows us to add, edit and delete fields both to and from new and existing tables.

Each row in the designer represents one column in the table. Let's create a table that will hold the details of a set of widgets. Click on the first Column Name field and enter the value widgetId. The column name field is the name by which we refer to each particular column in the table, which we'll see shortly. Click on the Data Type field for widgetId and choose the int option, which represents an integer, or whole number. Notice that by selecting a data type, the length and allow null fields are automatically completed for us. However, we want the widgetId to always contain a value, so uncheck the Allow Nulls checkbox.

To make sure that each widget in our table is unique, we'll make the widgetId field a unique primary key. Right-click anywhere in this first row of the grid and choose 'set primary key'. You'll see a little key icon appear next to the column to indicate that it's now the primary key for the table.

To help generate unique values for this column, we want to make SQL Server automatically insert a unique number for each record that we add. We can do this by modifying the column's details at the bottom of the table designer window, like this:

Notice how I've set the identity value to Yes and also set the seed (the number that the column will have initially) and increment (the number of values by which the field will increase when a new record is added) values to 1.

Now that we've created a unique primary key column for our widgets, click on the next Column Name field and enter widgetName. Make this column a varchar (variable length character) and set its length to 20. Again, this column must contain a value, so uncheck the Allow Nulls table.

To keep our table simple, we will create just one last field. Click on the next Column Name field and enter the value widgetPrice. Make this column a money value that can't be null. Using the column properties tab at the bottom of the table designer window, change the Default Value of the column to 0.

Press Ctrl+F4 to close the table designer and choose Yes when asked if you'd like to save the changes to Table1. When prompted for the table's name, enter Widgets and click OK. If you select the Tables node under MyDatabase1 in the tree listing and take a look in the right-hand pane of Enterprise Manager, you'll see our new table, Widgets.

It's good to have a new table, but what use is this table if it doesn't contain any data? Right click on our Widgets table in the right pane and choose the Open Table -> Return all rows option. Normally you'd be presented with a complete list of all records in the table at this point, but because our table is empty, all we see are the column names and one empty record.

This empty record is the key to populating our database with records from within Enterprise Manager. Click on the empty widgetName column and enter the value "Red Widget". Click on the widgetPrice column and enter 9.95. Press enter when you're done and you've just added a new record to our Widgets table!

Repeat this process with the following values to add more widgets to our table:

Notice how I've left some widgetPrice values blank? When you don't enter a value for the widgetPrice field, SQL Server sees that you've set a Default Value of 0 for that column and puts in this value automatically. Close the table by pressing Ctrl+F4.

Believe it or not, we've created our first database with Enterprise Manager... see how easy it was? We didn't have to use one bit of TSQL because Enterprise Manager handled everything behind the scenes for us.

I don't know about you, but I'm more of an old-style programmer and I like to create my code by hand. In the next section we're going to do exactly that with the use of Query Analyzer.

Creating a Database with Query Analyzer

Ah! We've arrived at the fun part of the tutorial: coding a database and its tables by hand. Don't be scared: the manual creation of databases is not difficult at all and it's a great way to learn the TSQL syntax.

We're going to use Query Analyzer to create exactly the same database that we did with Enterprise Manager, so point to Start menu -> Programs -> Microsoft SQL Server and run Query Analyzer. When it loads, you'll be presented with the login screen. I'm assuming that you've just installed a fresh copy of SQL Server 2000, so the default username of sa and no password is fine for now. Click OK to login to your database and start working with Query Analyzer.

First off, let's delete the MyDatabase1 database that we created earlier with Enterprise Manager. The sysdatabases table in the master database contains entries for each SQL Server 2000 database that exists on our PC. When we created the MyDatabase1 database, a new record was added to the sysdatabases table. Here's how the sysdatabases table looks:

Using some simple TSQL statements, we can check if the MyDatabase1 database exists in the sysdatabases table of the master database. If it does, then we'll delete it. Enter the following code into the Query Analyzer window:

USE MASTER    
GO    
   
-- If the database already exists, drop it    
IF EXISTS(SELECT * FROM sysdatabases WHERE name='MyDatabase1')    
DROP DATABASE MyDatabase1    
GO

Note that lines that start with --- are comments.

In Enterprise Manager we used the tree view in the left pane to choose which database we wanted to work with. In Query Analyzer, we specify the USE [database name] statement to tell SQL Server 2000 which database we'd like to work with. The GO command executes all code up until that specific point of the script, so after the first GO command, SQL Server knows that any commands it encounters should be executed against the master database.

Obviously the MyDatabase1 database already exists. But let's pretend that we don't know it does. By using the SELECT * FROM sysdatabases WHERE name='MyDatabase1'

TSQL command in combination with the IF EXISTS... statement, we set up a condition: if any records are returned from the SELECT query to the sysdatabases table of the master database, then we proceed to execute the statements that are part of the IF block:

DROP DATABASE MyDatabase1    
GO

The DROP DATABASE [database name] statement removes a database from the sysdatabases table and also deletes all of its tables, stored procedures, triggers, etc. We use the DROP DATABASE command to remove the MyDatabase1 database if it exists. At this point we need to flush our TSQL commands to SQL Server, so we use the GO command to do so. We now know for sure that a database called MyDatabase1 doesn't exist.

Add the following code into the Query Analyzer window, right below the code you added previously:

-- Create the MyDatabase1 database      
CREATE DATABASE MyDatabase1      
GO      
   
USE MyDatabase1      
GO

Here we're creating a new database called MyDatabase1. We could specify the location of the data file and its growth settings amongst other things, but we're creating a simple database in this article, so we don't need to. Once we recreate the MyDatabase1 database, we're ready to work with it, and we tell SQL Server this with the USE MyDatabase1 command.

In our Enterprise Manager example we used a table designer to create a new table and set the names, values and properties of each column within that table. We can accomplish exactly the same thing with Query Analyzer using some TSQL statements. Add the following code under the existing code in the Query Analyzer window:

-- Create the Widgets table in MyDatabase1      
CREATE TABLE Widgets      
(      
widgetId INT NOT NULL PRIMARY KEY IDENTITY(1,1),      
widgetName VARCHAR(50) NOT NULL,      
widgetPrice MONEY NOT NULL DEFAULT 0      
)      
GO

In the code above we've used the CREATE TABLE command to recreate our widgets table. Notice how the widgetId field is specified as a primary key and also as a unique identity. The IDENTITY(1,1) part of the code tells SQL Server that the widgetId field should have a start value of 1 and should be incremented by 1 every time a new record is added to the widgets table. The widgetName field is declared as a varchar of 50 characters in length and is also declared as not accepting null values with the NOT NULL expression. The last field, widgetPrice is a money field that's not null. I've also used the DEFAULT keyword to set its default value to 0.

Just to recap, here's the code that your query analyzer window should contain:

USE MASTER      
GO      
   
-- If the database already exists, drop it      
IF EXISTS(SELECT * FROM sysdatabases WHERE name='MyDatabase1')      
DROP DATABASE MyDatabase1      
GO      
   
-- Create the MyDatabase1 database      
CREATE DATABASE MyDatabase1      
GO      
   
USE MyDatabase1      
GO      
   
-- Create the Widgets table in MyDatabase1      
CREATE TABLE Widgets      
(      
widgetId INT NOT NULL PRIMARY KEY IDENTITY(1,1),      
widgetName VARCHAR(50) NOT NULL,      
widgetPrice MONEY NOT NULL DEFAULT 0      
)      
GO

Note that TSQL is not case sensitive.

To execute our TSQL code either press the F5 key or click on the green play button. You should see the following result in the bottom pane of the Query Analyzer window:

We've just dropped our original MyDatabase1 database and recreated both its structure and its widgets table. The one thing we're missing is the records of the actual widgets. Clear the code in the query analyzer window and enter the following code there:

USE MYDATABASE1      
GO      
     
INSERT INTO Widgets(widgetName, widgetPrice)      
VALUES('Red Widget', 9.95)      
INSERT INTO Widgets(widgetName, widgetPrice)      
VALUES('Blue Widget', 14.50)      
INSERT INTO Widgets(widgetName, widgetPrice)      
VALUES('Green Widget', 24.95)      
INSERT INTO Widgets(widgetName)      
VALUES('Orange Widget')      
INSERT INTO Widgets(widgetName, widgetPrice)      
VALUES('Purple Widget', 1.95)      
INSERT INTO Widgets(widgetName)      
VALUES('Yellow Widget')      
GO

Execute the code once again by pressing F5 or clicking on the green play button. For each new record that's added to the widgets table, SQL Server 2000 should respond with "(1 row(s) affected)" in the bottom pane of the query analyzer window.

Lastly, to view the records in the widgets table, clear the code in Query Analyzer and enter and execute the following TSQL code:

USE MYDATABASE1      
GO      
     
SELECT * FROM Widgets      
ORDER BY widgetName ASC

You should see a list of records from the widgets table in the lower pane of Query Analyzer, just like this:

And there you have it, one fully functional database that you now know how to create with both Enterprise Manager and Query Analyzer. Let's look at using some TSQL statements to manipulate our database on the next page.

Manipulating our Database

Now let's play around with a couple of TSQL commands and modify our database. The details of the commands you're about to see are available in the SQL Server 2000 help file, which you can access by pressing F1 in Query Analyzer.

Adding a column to our widgets table

Let's pretend that the boss of our widgets corporation wants to be able to set whether or not a particular widget is in stock. Most widgets are available; however, a select few have to be imported from Widgetville first. We could add a new column called widgetAvailable with this TSQL code:

USE MYDATABASE1      
GO      
     
ALTER TABLE widgets      
ADD widgetAvailable BIT NOT NULL DEFAULT 1      
GO

The code above creates the new widgetAvailable column and also sets the value of that column to 1 for every record that currently resides in the widgets table.

Add a trigger to our widgets table

One of the most powerful features of SQL Server is the trigger. A trigger is a piece of code that is fired whenever an INSERT, UPDATE or DELETE command is executed against a particular table. Let's create a trigger on our widgets table so that whenever we add a new widget to the table, SQL Server will tell us how many widgets exist in the table:

USE MYDATABASE1        
GO        
     
CREATE TRIGGER trigShowTotalWidgets        
ON widgets        
FOR INSERT        
AS        
DECLARE @numWidgets INT        
SELECT @numWidgets = (SELECT COUNT(*) FROM Widgets)        
PRINT 'There are '+CAST(@numWidgets AS VARCHAR)+' in the widgets table'        
GO

Run the code above, clear the Query Analyzer window and then enter and run the following code:

USE MYDATABASE1        
GO        
     
SET NOCOUNT ON        
     
INSERT INTO widgets(widgetName, widgetPrice, widgetAvailable)        
VALUES('Maroon Widget', 21.45, 1)        
GO

We've setup a trigger that will execute whenever an INSERT statement is executed against our widgets database. The trigger prints how many records exist in the widgets table. When you run the code above, the bottom pane of Query Analyzer should read something like "There are 7 in the widgets table". If you'd like to read more about triggers, read this article [3].

Backing up our database

SQL Server 2000 is one seriously robust and flexible RDBMS. It includes several stored procedures, which are simply blocks of TSQL statements grouped together. The master database contains dozens of stored procedures, and you can see them using Enterprise Manager by expanding the master database and clicking on the stored procedures node. Double click on any stored procedure in the right pane to view its code.

To backup our MyDatabase1 database, we first need to create a backup device, which is a place where the backup will reside. We can use the sp_adddumpdevice stored procedure to do this:

USE MASTER        
GO        
     
sp_addumpdevice 'disk', 'MyDatabase1_device', 'c:\MyDatabase1.dat'        
GO

In the code above, we've created a new backup device with the alias of MyDatabase1_device. We can then back up our database to this device using TSQL's BACKUP DATABASE command, like this:

BACKUP DATABASE MyDatabase1 TO MyDatabase1_device        
GO

Query Analyzer will respond with something similar to this:

Processed 96 pages for database        
'MyDatabase1', file 'MyDatabase1' on file 1.        
Processed 1 pages for database 'MyDatabase1', file        
'MyDatabase1_log' on file 1.        
BACKUP DATABASE successfully processed 97 pages in        
0.217 seconds (3.635 MB/sec).

Your database has now been backed up to a file called MyDatabase1.dat in the root of your C: drive (don't forget to delete it when you're finished experimenting!).

Retrieve data as XML

SQL Server 2000 supports the FOR XML clause, which we can use to easily return records as XML data. Use the following code in Query Analyzer to return all rows from our widgets table as XML elements:

USE MyDatabase1        
GO        
       
SELECT 1 AS Tag,        
NULL AS Parent,        
widgetName AS [Widget!1!Name],        
widgetPrice AS [Widget!1!Price],        
widgetAvailable AS [Widget!1!Available]        
FROM widgets        
FOR XML EXPLICIT        
GO

The XML will be returned as one record and will something look like this:

<Widget Name="Red Widget" Price="9.9500" Available="1"/>        
<Widget Name="Blue Widget" Price="14.5000" Available="1"/>        
<Widget Name="Green Widget" Price="24.9500" Available="1"/>        
...

For more detail on retrieving XML data from SQL Server, see my article "Retrieving Data as XML from SQL Server [4]".

Conclusion

In this article we've created an SQL Server 2000 database from scratch. We used Enterprise Manager and Query Analyzer to accomplish the same goals, and we finished off by looking at some TSQL commands we can use to manipulate our database. If you've never worked with SQL Server 2000 before then hopefully this article has given you the information you need to get started!

[1] http://microsoft.com/sql/evaluation/trial/2000/default.asp
[2] http://msdn.microsoft.com/
[3] http://www.devarticles.com/art/1/36
[4] webmasterbase.com/article/515