To create a table, you use the CREATE TABLE Transact-SQL statement, which uses the following general syntax:
The diagram above contains Transact-SQL (T-SQL) syntax for creating a table in SQL Server.
Below is the extracted T-SQL code from the diagram.
CREATE TABLE table_name: This command is used to create a new table with the specified name.
Column definitions (<column_definition>): Defines the structure of each column in the table.
Computed columns (column_name AS computed_column_expression): Specifies a column whose value is derived from other columns using an expression.
Table constraints (<table_constraint>): Defines constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and DEFAULT.
ON {filegroup | DEFAULT}: Specifies the filegroup where the table will be stored.
TEXTIMAGE_ON {filegroup | DEFAULT}: Specifies where TEXT, IMAGE, and VARBINARY(MAX) columns are stored.
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:
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.
Additional 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:
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.
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.