Creates a new table.
CREATE TABLE
[
database_name.[owner].
| owner.
] table_name
(
{ <column_definition>
| column_name AS computed_column_expression
| <table_constraint>
} [,...n]
)
[ON {filegroup | DEFAULT} ]
[TEXTIMAGE_ON {filegroup | DEFAULT} ]
<column_definition> ::= { column_name data_type }
[ [ DEFAULT constant_expression ]
| [ IDENTITY [(seed, increment ) [NOT FOR REPLICATION] ] ]
]
[ ROWGUIDCOL ]
[ <column_constraint>] [ ...n]
<column_constraint> ::= [CONSTRAINT constraint_name]
{
[ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[CLUSTERED | NONCLUSTERED]
[WITH FILLFACTOR = fillfactor]
[ON {filegroup | DEFAULT} ]]
]
| [ [FOREIGN KEY]
REFERENCES ref_table [(ref_column) ]
[NOT FOR REPLICATION]
]
| CHECK [NOT FOR REPLICATION]
(logical_expression)
}
<table_constraint> ::= [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)
}
Computed columns can be used in select lists, WHERE clauses, ORDER BY clauses, or any other locations in which regular expressions can be used, with the following exceptions:
Note Because each row in a table can have different values for columns involved in a computed column, the computed column may not have the same value for each row.
ON {filegroup | DEFAULT} can also be specified in a PRIMARY KEY or UNIQUE constraint. These constraints create indexes. If filegroup is specified, the index is stored in the named filegroup. If DEFAULT is specified, the index is stored in the default filegroup. If no filegroup is specified in a constraint, the index is stored on the same filegroup as the table. If the PRIMARY KEY or UNIQUE constraint creates a clustered index, the data pages for the table are stored in the same filegroup as the index.
The NULL/NOT NULL assignment for a user-defined data type can be overridden during the CREATE TABLE statement. However, the length specification cannot be changed; you cannot specify a length for a user-defined data type in a CREATE TABLE statement.
The ROWGUIDCOL property does not enforce uniqueness of the values stored in the column. It also does not automatically generate values for new rows inserted into the table. To generate unique values for each column, either use the NEWID function on INSERT statements or use the NEWID function as the default for the column.
You can specify CLUSTERED for only one constraint in a CREATE TABLE statement. If you specify CLUSTERED for a UNIQUE constraint and also specify a PRIMARY KEY constraint, the PRIMARY KEY defaults to NONCLUSTERED.
The NOT FOR REPLICATION CHECK constraint is applied to both the before and after image of an updated record to prevent records from being added to or deleted from the replicated range. All deletes and inserts are checked; if they fall within the replicated range, they are rejected.
When this constraint is used with an identity column, SQL Server allows the table not to have its identity column values reseeded when a replication user updates the identity column.
SQL Server can have as many as 2 billion tables per database and 1,024 columns per table. The number of rows and total size of the table are limited only by the available storage. The maximum number of bytes per row is 8,060. If you create tables with varchar, nvarchar, or varbinary columns in which the total defined width exceeds 8,060 bytes, the table is created but a warning message appears. Trying to insert more than 8,060 bytes into such a row or to update a row so that its total row size exceeds 8,060 produces an error message and the statement fails.
Each table can contain up to 249 nonclustered indexes and 1 clustered index. These include the indexes generated to support any PRIMARY KEY and UNIQUE constraints defined for the table.
SQL Server does not enforce an order in which DEFAULT, IDENTITY, ROWGUIDCOL, or column constraints are specified in a column definition.
You can create local and global temporary tables. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.
Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).
SQL statements reference the temporary table using the value specified for table_name in the CREATE TABLE statement:
CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
INSERT INTO #MyTempTable VALUES (1)
If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, SQL Server has to be able to distinguish the tables created by the different users. SQL Server does this by internally appending a numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sysobjects table in tempdb consists of table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters.
Temporary tables are automatically dropped when they go out of scope, unless they have already been explicitly dropped using DROP TABLE:
A local temporary table created within a stored procedure or trigger is distinct from a temporary table with the same name created before the stored procedure or trigger is called. All references to the name within the stored procedure or trigger are resolved against the temporary table created in the procedure or trigger, not the version that existed before the procedure or trigger was called. Nested stored procedures can also create temporary tables with the same name as a temporary table created by the stored procedure that called it. All references to the table name in the nested stored procedure are resolved to the table created in the nested procedure, for example:
CREATE PROCEDURE Test2
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (2)
SELECT Test2Col = x FROM #t
GO
CREATE PROCEDURE Test1
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (1)
SELECT Test1Col = x FROM #t
EXEC Test2
GO
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (99)
GO
EXEC Test1
GO
The results of running this script are:
(1 row(s) affected)
Test1Col
-----------
1
(1 row(s) affected)
Test2Col
-----------
2
When you create local or global temporary tables, the CREATE TABLE syntax supports constraint definitions with the exception of FOREIGN KEY constraints. If a FOREIGN KEY constraint is specified in a temporary table, the statement returns a warning message indicating that the constraint was skipped, and the table is still created without the FOREIGN KEY constraints. Temporary tables cannot be referenced in FOREIGN KEY constraints.
| SQL-92 niladic function | Value returned |
|---|---|
| CURRENT_TIMESTAMP | Current date and time |
| CURRENT_USER | Name of user performing insert |
| SESSION_USER | Name of user performing insert |
| SYSTEM_USER | Name of user performing insert |
| USER | Name of user performing insert |
CHECK CONSTRAINTS and rules serve the same function of validating the data during INSERT and DELETE statements.
If a table has FOREIGN KEY or CHECK CONSTRAINTS and triggers, the constraint conditions are evaluated before the trigger is executed.
For a report on a table and its columns, use sp_help or sp_helpconstraint. To rename a table, use sp_rename. For a report on the views and stored procedures that depend on a table, use sp_depends.
Space is generally allocated to tables and indexes in increments of one extent at a time. When the table or index is created, it is allocated pages from mixed extents until it has enough pages to fill a uniform extent. After it has enough pages to fill a uniform extent, another extent is allocated each time the currently allocated extents become full. For a report on the amount of space allocated and used by a table, execute sp_spaceused.
The nullability of a column determines whether or not that column can allow a null value (NULL) as the data in that column. NULL is not zero or blank. It means no entry has been made or an explicit NULL was supplied, and it usually implies that the value is either unknown or not applicable.
When you create or alter a table with the CREATE TABLE or ALTER TABLE statements, database and session settings influence and possibly override the nullability of the data type used in a column definition. It is recommended that you always explicitly define a column as NULL or NOT NULL or, if you use a user-defined data type, that you allow the column to use the default nullability of the data type.
When not explicitly specified, column nullability follows these rules:
·If ANSI_NULL_DFLT_ON is ON, NULL is assigned.
·If ANSI_NULL_DFLT_OFF is ON, NOT NULL is assigned.
·If ANSI null default is true, NULL is assigned.
·If ANSI null default is false, NOT NULL is assigned.
Note The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server both default to having ANSI_NULL_DFLT_ON set to ON. ODBC and OLE DB users can configure this in ODBC data sources, or with connection attributes or properties set by the application.
CREATE TABLE permission defaults to the members of the db_owner and db_ddladmin fixed database roles. Members of the db_owner fixed database role and members of the sysadmin fixed server role can transfer CREATE TABLE permission to other users.
This example shows the column definition for a PRIMARY KEY constraint with a clustered index on the job_id column of the jobs table (allowing the system to supply the constraint name) in the pubs sample database.
job_id smallint
PRIMARY KEY CLUSTERED
This example shows how a name can be supplied for the PRIMARY KEY constraint. This constraint is used on the emp_id column of the employee table. This column is based on a user-defined data type.
emp_id empid
CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED
A FOREIGN KEY constraint is used to reference another table. Foreign keys can be single-column keys or multicolumn keys. This example shows a single-column FOREIGN KEY constraint on the employee table that references the jobs table. Only the REFERENCES clause is required for a single-column FOREIGN KEY constraint.
job_id smallint NOT NULL
DEFAULT 1
REFERENCES jobs(job_id)
You can also explicitly use the FOREIGN KEY clause and restate the column attribute (note that the column name does not have to be the same in both tables).
FOREIGN KEY (job_id) REFERENCES jobs(job_id)
Multicolumn key constraints are created as table constraints. In the pubs database, the sales table includes a multicolumn PRIMARY KEY. This example shows how to reference this key from another table (an explicit constraint name is optional).
CONSTRAINT FK_sales_backorder FOREIGN KEY (stor_id, ord_num, title_id)
REFERENCES sales (stor_id, ord_num, title_id)
UNIQUE constraints are used to enforce uniqueness on nonprimary key columns. A PRIMARY KEY constraint column includes a restriction for uniqueness automatically; however, a UNIQUE constraint can allow null values. This example shows a column called pseudonym on the authors table. It enforces a restriction that authors’ pen names must be unique.
pseudonym varchar(30) NULL
UNIQUE NONCLUSTERED
This example shows a UNIQUE constraint created on the stor_name and city columns of the stores table, where the stor_id is actually the PRIMARY KEY; no two stores in the same city should be the same.
CONSTRAINT U_store UNIQUE NONCLUSTERED (stor_name, city)
Defaults supply a value (with the INSERT and UPDATE statements) when no value is supplied. In the pubs database, many DEFAULT definitions are used to ensure that valid data or placeholders are entered.
On the jobs table, a character string default supplies a description (column job_desc) when the actual description is not entered explicitly.
DEFAULT 'New Position - title not formalized yet'
In the employee table, the employees can be employed by an imprint company or by the parent company. When an explicit company is not supplied, the parent company is entered (note that, as shown here, comments can be nested within the table definition).
DEFAULT ('9952')
/* By default the Parent Company Publisher is the company
to whom each employee reports. */
In addition to constants, DEFAULT definitions can include functions. Use this example to get the current date for an entry:
DEFAULT (getdate())
Niladic-functions can also improve data integrity. To keep track of the user who inserted a row, use the niladic-function for USER (do not surround the niladic-functions with parentheses):
DEFAULT USER
This example shows restrictions made to the values entered into the min_lvl and max_lvl columns of the jobs table. Both of these constraints are unnamed:
CHECK (min_lvl >= 10)
and
CHECK (max_lvl <= 250)
This example shows a named constraint with a pattern restriction on the character data entered into the emp_id column of the employee table.
CONSTRAINT CK_emp_id CHECK (emp_id LIKE
'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' OR
emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]')
This example specifies that the pub_id must be within a specific list or follow a given pattern. This constraint is for the pub_id of the publishers table.
CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756')
OR pub_id LIKE '99[0-9][0-9]')
This example shows complete table definitions with all constraint definitions for three tables (jobs, employee, and publishers) created in the pubs database.
/* ************************** jobs table ************************** */
CREATE TABLE jobs
(
job_id smallint
IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
job_desc varchar(50) NOT NULL
DEFAULT 'New Position - title not formalized yet',
min_lvl tinyint NOT NULL
CHECK (min_lvl >= 10),
max_lvl tinyint NOT NULL
CHECK (max_lvl <= 250)
)
/* ************************* employee table ************************* */
CREATE TABLE employee
(
emp_id empid
CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED
CONSTRAINT CK_emp_id CHECK (emp_id LIKE
'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or
emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'),
/* Each employee ID consists of three characters that
represent the employee's initials, followed by a five
digit number ranging from 10000 to 99999 and then the
employee's gender (M or F). A (hyphen) - is acceptable
for the middle initial. */
fname varchar(20) NOT NULL,
minit char(1) NULL,
lname varchar(30) NOT NULL,
job_id smallint NOT NULL
DEFAULT 1
/* Entry job_id for new hires. */
REFERENCES jobs(job_id),
job_lvl tinyint
DEFAULT 10,
/* Entry job_lvl for new hires. */
pub_id char(4) NOT NULL
DEFAULT ('9952')
REFERENCES publishers(pub_id),
/* By default, the Parent Company Publisher is the company
to whom each employee reports. */
hire_date datetime NOT NULL
DEFAULT (getdate())
/* By default, the current system date will be entered. */
)
/* ***************** publishers table ******************** */
CREATE TABLE publishers
(
pub_id char(4) NOT NULL
CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED
CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756')
OR pub_id LIKE '99[0-9][0-9]'),
pub_name varchar(40) NULL,
city varchar(20) NULL,
state char(2) NULL,
country varchar(30) NULL
DEFAULT('USA')
)
This example creates a table with a uniqueidentifier column. It uses a PRIMARY KEY constraint to protect the table against users inserting duplicated values, and it uses the NEWID() function in the DEFAULT constraint to provide values for new rows.
CREATE TABLE Globally_Unique_Data
(guid uniqueidentifier
CONSTRAINT Guid_Default
DEFAULT NEWID(),
Employee_Name varchar(60),
CONSTRAINT Guid_PK PRIMARY KEY (Guid)
)
This example illustrates the use of an expression ((low + high)/2) for calculating the myavg computed column.
CREATE TABLE mytable
(
low int,
high int,
myavg AS (low + high)/2
)
This example uses the USER_NAME function in the myuser_name column.
CREATE TABLE mylogintable
(
date_in datetime,
user_id int,
myuser_name AS USER_NAME()
)
This example shows using the IDENTITY property on a table that is subscribed to a replication. The table includes a CHECK constraint to ensure that the SaleID values generated on this system do not grow into the range assigned to the replication Publisher.
CREATE TABLE Sales
(SaleID INT IDENTITY(100000,1) NOT FOR REPLICATION,
CHECK NOT FOR REPLICATION (SaleID <= 199999),
SalesRegion CHAR(2),
CONSTRAINT ID_PK PRIMARY KEY (SaleID)
)
| ALTER TABLE | DROP TABLE |
| CREATE INDEX | sp_addtype |
| CREATE RULE | sp_depends |
| CREATE VIEW | sp_help |
| Data Types | sp_helpconstraint |
| DROP INDEX | sp_rename |
| DROP RULE | sp_spaceused |