Your First SQL Server 2000 Database

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.