TSQL Tutorial : Creating Tables

Once the database is created, you're ready to begin adding objects to it. Let's begin by creating some

Tables using SQL's CREATE TABLE statement. To ensure that those tables are created in the new

database, be sure to change the current database focus to SQLSERVERQUEST before issuing any of these commands.

You can do this two ways: You can execute a USE command—USE SQLSERVERQUEST— in the query edit window prior to executing any other commands, or (assuming you're using Query Analyzer) you can select the new database from the DB: combo-box on the edit window's toolbar (select <Refresh> from this list if your new database is not visible at rst).

The DB: combo-box reflects the currently selected database, so be sure it points to SQLSERVERQUEST

Before proceeding.

Execute the following command to create the customers table:

USE SQLSERVERQUEST — Change the current database context to SQLSERVERQUEST

GO

CREATE TABLE customers

(

CustomerNumber int NOT NULL,

LastName char(30) NOT NULL,

FirstName char(30) NOT NULL,

StreetAddress char(30) NOT NULL,

City char(20) NOT NULL,

State char(2) NOT NULL,

Zip char(10) NOT NULL

)

Once the customers table is built, create the orders table using similar syntax:

CREATE TABLE orders

(

OrderNumber int NOT NULL,

OrderDate datetime NOT NULL,

CustomerNumber int NOT NULL,

ItemNumber int NOT NULL,

Amount numeric(9,2) NOT NULL

)

Most SQL concepts can be demonstrated using three or fewer tables, so we'll create a third table. Create

The items table using this command:

CREATE TABLE items

(

ItemNumber int NOT NULL,

Description char(30) NOT NULL,

Price numeric(9,2) NOT NULL

)

These commands are fairly self-explanatory. The only element that might look a little strange if you're

new to SQL Server is the NOT NULL specification. The SQL NULL keyword is a special syntax token that's

used to represent unknown or nonexistent values. It is not the same as zero for integers or blanks for

character string columns. NULL indicates that a value is not known or completely missing from the

column—that it's not there at all.

The difference between NULL and zero is the difference between having a zero account balance and

not having an account at all. (See Chapter 3, "Missing Values," for more information on NULLs.) The

NULL/NOT NULL specification is used to control whether a column can store SQL's NULL token. This is

formally referred to as column nullability. It dictates whether the column can be truly empty. So, you

could read NULL/NOT NULL as NOT REQUIRED/REQUIRED, respectively. If a field can't contain NULL, it

can't be truly empty and is therefore required to have some other value.

Note that you don't have to specify column nullability when you create a table—SQL Server will supply a

default setting if it's omitted. The rules governing default column nullability go like this:

• If you explicitly specify either NULL or NOT NULL, it will be used (if valid—see below).

• If a column is based on a user-dened data type, that data type's nullability specification is used.

• If a column has only one nullability option, that option is used. Timestamp columns always require

values, and bit columns can require them as well, depending on the server compatibility setting

(specified via the sp_dbcmptlevel system stored procedure).

• If the session setting ANSI_NULL_DFLT_ON is set to true (it defaults to the setting specified in the

database), column nullability defaults to true. ANSI SQL species that columns are nullable by default.

Connecting to SQL Server via ODBC or OLEDB (which is the normal way applications connect) sets

ANSI_ NULL_DFLT_ON to true by default, though this can be changed in ODBC data sources or by

the calling application.

• If the database setting ANSI null default is set to true (it defaults to false), column nullability is set

totrue.

• If none of these conditions species an ANSI NULL setting, column nullability defaults to false so that

columns don't allow NULL values.

TSQL Tutorial...