Lesson 10	Creating tables in SQL-Server
Objective	Use Transact-SQL to create a basic table.

Creating tables (Use Transact-SQL)

To create a table, you use the CREATE TABLE Transact-SQL statement, which uses the following general syntax:

Create Table Two

This is the name that you will give your table.

This is a group of properties indicating the attributes of each column that makes up the table.

This is a constraint defined at the table level, not for each column.

This is the name of the filegroup that will store the table. ON DEFAULT specifies that the table is to be stored on the default filegroup.

This is the name of the filegroup that will store text, ntext, and image datatype values. TEXTIMAGE_ON DEFAULT specifies that text, ntext, and image datatype values are to be stored on the default filegroup.

Note that column_definition and table_constraint statements each accept additional arguments.

column_definition accepts Arguments

column_definition accepts the following arguments:

{ column_name data_type }
[ NULL | NOT NULL ]
[ IDENTITY [(seed, increment )
[NOT FOR REPLICATION] ] ]
[ ROWGUIDCOL ]
[ < column_constraint> ::=
[CONSTRAINT constraint_name]
{ { PRIMARY KEY | UNIQUE }
[CLUSTERED | NONCLUSTERED]
[WITH [FILLFACTOR = fillfactor]
]
[ON {filegroup | DEFAULT} ]
| [FOREIGN KEY]
REFERENCES ref_table
[ ( ref_column ) ]
[NOT FOR REPLICATION]
| DEFAULT constant_expression
| CHECK [NOT FOR REPLICATION]
(logical_expression)
}
] [ ...n]

table_constraint accepts the following arguments:

[CONSTRAINT constraint_name]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED]
{ ( column[,...n] ) }
[ WITH [FILLFACTOR = fillfactor]
]
[ON {filegroup | DEFAULT} ]
]
| FOREIGN KEY
[(column[,...n])]
REFERENCES ref_table [(ref_column[,...n])]
[NOT FOR REPLICATION]
| CHECK [NOT FOR REPLICATION]
(search_conditions)
}

Table with one column

At a minimum, you must specify one column within the table, and its datatype. For example, if you wanted to create an employee table with only one column, named "salary," you could issue the following Transact-SQL statement:

CREATE TABLE employee (salary smallmoney)

This example doesn't mean much, you have defined a salary column, but there are no other columns in the table to serve as a frame of reference. For example, we don't know which employee has what salary. In most real-world situations, you will need to define more than one column per table.

More columns

Now, let us create a more complex table, by adding pertinent columns for the Employee table. Such columns include the name of the employee, along with address and contact information. You could create such a table, like this:

CREATE TABLE employee
(
 EmployeeID  int,
 DepartmentID  int,
 Salary   smallmoney
 LastName  varchar (30),
 FirstName  varchar (30),
 AddressLine1  varchar (30),
 AddressLine2  varchar (30),
 City   varchar (30),
 State   char (2),
 Zip   char (5),
 HomePhone  varchar (10),
 HomeFax  varchar (10) 
)

Using the IDENTITY column

Finally, suppose you did not want to supply a value for each new employee as he/she is hired. You could make the EmployeeID column an IDENTITY column, whereby each new employee will be assigned a number that is incremented from the last EmployeeID. You also want the number to start at 1001. You could create the table, like this.

CREATE TABLE employee
(
 EmployeeID  int IDENTITY (1001, 1),
 DepartmentID  int,
 Salary   smallmoney
 LastName  varchar (30),
 FirstName  varchar (30),
 AddressLine1  varchar (30),
 AddressLine2  varchar (30),
 City   varchar (30),
 State   char (2),
 Zip   char (5),
 HomePhone  varchar (10),
 HomeFax  varchar (10) 
)

Create SQL-Server Table - Exercise

Click the Exercise link below on the left to practice creating tables.
Create SQL-Server Table - Exercise
In the next lesson, you will learn how to modify an existing table.