Your First SQL Server 2000 Database

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: