CREATE TABLE
Creates a new table.
Syntax
CREATE TABLE
[ database_name.[ owner ] . | owner. ] table_name
( { < column_definition >
| column_name AS computed_column_expression
| < table_constraint > ::= [ CONSTRAINT constraint_name ] }
| [ { PRIMARY KEY | UNIQUE } [ ,...n ]
)
[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]
< column_definition > ::= { column_name data_type }
[ COLLATE < collation_name > ]
[ [ 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 ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
]
| CHECK [ NOT FOR REPLICATION ]
( logical_expression )
}
< table_constraint > ::= [ CONSTRAINT constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
{ ( column [ ASC | DESC ] [ ,...n ] ) }
[ WITH FILLFACTOR = fillfactor ]
[ ON { filegroup | DEFAULT } ]
]
| FOREIGN KEY
[ ( column [ ,...n ] ) ]
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ]
( search_conditions )
}
Arguments
database_name
Is the name of the database in which the table is created. database_name must specify the name of an existing database. database_name defaults to the current database if not specified. The login for the current connection must be associated with an existing user ID in the database specified by database_name, and that user ID must have create table permissions.
owner
Is the name of the user ID that owns the new table. owner must be an existing user ID in the database specified by database_name. owner defaults to the user ID associated with the login for the current connection in the database specified in database_name. If the CREATE TABLE statement is executed by a member of the sysadmin fixed server role, or a member of the db_dbowner or db_ddladmin fixed database roles in the database specified by database_name, owner can specify a user ID other than the one associated with the login of the current connection. If the CREATE TABLE statement is executed by a login associated with a user ID that has only create table permissions, owner must specify the user ID associated with the current login. Members of the sysadmin fixed server role, or logins aliased to the dbo user are associated with the user ID dbo; therefore, tables created by these users default to having dbo as the owner. Tables created by any logins not in either of these two roles have owner default to the user ID associated with the login.
table_name
Is the name of the new table. Table names must conform to the rules for identifiers. The combination of owner.table_name must be unique within the database. table_name can contain a maximum of 128 characters, except for local temporary table names (names prefixed with a single number sign (#)) that cannot exceed 116 characters.
column_name
Is the name of a column in the table. Column names must conform to the rules for identifiers and must be unique in the table. column_name can be omitted for columns created with a timestamp data type. The name of a timestamp column defaults to timestamp if column_name is not specified.
computed_column_expression
Is an expression defining the value of a computed column. A computed column is a virtual column not physically stored in the table. It is computed from an expression using other columns in the same table. For example, a computed column can have the definition: cost AS price * qty. The expression can be a noncomputed column name, constant, function, variable, and any combination of these connected by one or more operators. The expression cannot be a subquery.
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:
- A computed column cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition. However, a computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint, if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns.
For example, if the table has integer columns a and b, the computed column a+b may be indexed, but computed column a+DATEPART(dd, GETDATE()) cannot be indexed because the value may change in subsequent invocations.
- A computed column cannot be the target of an INSERT or UPDATE statement.
Note Each row in a table can have different values for columns involved in a computed column, therefore the computed column may not have the same value for each row.
The nullability of computed columns is determined automatically by SQL Server based on the expressions used. The result of most expressions is considered nullable even if only non-nullable columns are present because possible underflows or overflows will produce NULL results as well. Use the COLUMNPROPERTY function (AllowsNull property) to investigate the nullability of any computed column in a table. An expression expr that is nullable can be turned into a non-nullable one by specifying ISNULL(check_expression, constant) where the constant is a non-NULL value substituted for any NULL result.
ON {filegroup | DEFAULT}
Specifies the filegroup on which the table is stored. If filegroup is specified, the table is stored in the named filegroup. The filegroup must exist within the database. If DEFAULT is specified, or if ON is not specified at all, the table is stored on the default filegroup.
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.
Note DEFAULT, in the context of ON {filegroup | DEFAULT} and TEXTIMAGE_ON {filegroup | DEFAULT}, is not a keyword. DEFAULT is an identifier for the default filegroup and must be delimited, as in ON "DEFAULT" or ON [DEFAULT] and TEXTIMAGE_ON "DEFAULT" or TEXTIMAGE_ON [DEFAULT].
TEXTIMAGE_ON
Are keywords indicating that the text, ntext, and image columns are stored on the specified filegroup. TEXTIMAGE ON is not allowed if there are no text, ntext, or image columns in the table. If TEXTIMAGE_ON is not specified, the text, ntext, and image columns are stored in the same filegroup as the table.
data_type
Specifies the column data type. System or user-defined data types are acceptable. User-defined data types are created with sp_addtype before they can be used in a table definition.
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.
DEFAULT
Specifies the value provided for the column when a value is not explicitly supplied during an insert. DEFAULT definitions can be applied to any columns except those defined as timestamp, or those with the IDENTITY property. DEFAULT definitions are removed when the table is dropped. Only a constant value, such as a character string; a system function, such as SYSTEM_USER(); or NULL can be used as a default. To maintain compatibility with earlier versions of SQL Server, a constraint name can be assigned to a DEFAULT.
constant_expression
Is a constant, NULL, or a system function used as the default value for the column.
IDENTITY
Indicates that the new column is an identity column. When a new row is added to the table, Microsoft® SQL Server™ provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).
seed
Is the value used for the very first row loaded into the table.
increment
Is the incremental value added to the identity value of the previous row loaded.
NOT FOR REPLICATION
Indicates that the IDENTITY property should not be enforced when a replication login such as sqlrepl inserts data into the table. Replicated rows must retain the key values assigned in the publishing database; the NOT FOR REPLICATION clause ensures that rows inserted by a replication process are not assigned new identity values. Rows inserted by other logins continue to have new identity values created in the usual way. It is recommended that a CHECK constraint with NOT FOR REPLICATION also be defined to ensure that the identity values assigned are within the range wanted for the current database.
ROWGUIDCOL
Indicates that the new column is a row global unique identifier column. Only one uniqueidentifier column per table can be designated as the ROWGUIDCOL column. The ROWGUIDCOL property can be assigned only to a uniqueidentifier column. The ROWGUIDCOL keyword is not valid if the database compatibility level is 65 or lower. For more information, see sp_dbcmptlevel.
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.
collation_name
Specifies the collation for the column. Collation name can be either a Windows collation name or a SQL collation name. The collation_name is applicable only for columns of the char, varchar, text, nchar, nvarchar, and ntext data types. If not specified, the column is assigned either the collation of the user-defined data type, if the column is of a user-defined data type, or the default collation of the database.
For more information about the Windows and SQL collation names, see COLLATE.
CONSTRAINT
Is an optional keyword indicating the beginning of a PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, or CHECK constraint definition. Constraints are special properties that enforce data integrity and they may create indexes for the table and its columns.
constraint_name
Is the name of a constraint. Constraint names must be unique within a database.
NULL | NOT NULL
Are keywords that determine if null values are allowed in the column. NULL is not strictly a constraint but can be specified in the same manner as NOT NULL.
PRIMARY KEY
Is a constraint that enforces entity integrity for a given column or columns through a unique index. Only one PRIMARY KEY constraint can be created per table.
UNIQUE
Is a constraint that provides entity integrity for a given column or columns through a unique index. A table can have multiple UNIQUE constraints.
CLUSTERED | NONCLUSTERED
Are keywords to indicate that a clustered or a nonclustered index is created for the PRIMARY KEY or UNIQUE constraint. PRIMARY KEY constraints default to CLUSTERED and UNIQUE constraints default to NONCLUSTERED.
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.
[WITH FILLFACTOR = fillfactor]
Specifies how full SQL Server should make each index page used to store the index data. User-specified fillfactor values can be from 1 through 100, with a default of 0. A lower fill factor creates the index with more space available for new index entries without having to allocate new space.
FOREIGN KEY...REFERENCES
Is a constraint that provides referential integrity for the data in the column or columns. FOREIGN KEY constraints require that each value in the column exists in the corresponding referenced column(s) in the referenced table. FOREIGN KEY constraints can reference only columns that are PRIMARY KEY or UNIQUE constraints in the referenced table or columns referenced in a UNIQUE INDEX on the referenced table.
ref_table
Is the name of the table referenced by the FOREIGN KEY constraint.
(ref_column[,...n])
Is a column, or list of columns, from the table referenced by the FOREIGN KEY constraint.
ON DELETE {CASCADE | NO ACTION}
Specifies what action takes place to a row in the table created, if that row has a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.
If CASCADE is specified, a row is deleted from the referencing table if that row is deleted from the parent table. If NO ACTION is specified, SQL Server raises an error and the delete action on the row in the parent table is rolled back.
For example, in the Northwind database, the Orders table has a referential relationship with the Customers table. The Orders.CustomerID foreign key references the Customers.CustomerID primary key.
If a DELETE statement is executed on a row in the Customers table, and an ON DELETE CASCADE action is specified for Orders.CustomerID, SQL Server checks for one or more dependent rows in the Orders table. If any, the dependent rows in the Orders table are deleted, as well as the row referenced in the Customers table.
On the other hand, if NO ACTION is specified, SQL Server raises an error and rolls back the delete action on the Customers row if there is at least one row in the Orders table that references it.
ON UPDATE {CASCADE | NO ACTION}
Specifies what action takes place to a row in the table created, if that row has a referential relationship and the referenced row is updated in the parent table. The default is NO ACTION.
If CASCADE is specified, the row is updated in the referencing table if that row is updated in the parent table. If NO ACTION is specified, SQL Server raises an error and the update action on the row in the parent table is rolled back.
For example, in the Northwind database, the Orders table has a referential relationship with the Customers table: Orders.CustomerID foreign key references the Customers.CustomerID primary key.
If an UPDATE statement is executed on a row in the Customers table, and an ON UPDATE CASCADE action is specified for Orders.CustomerID, SQL Server checks for one or more dependent rows in the Orders table. If any exist, the dependent rows in the Orders table are updated, as well as the row referenced in the Customers.
Alternately, if NO ACTION is specified, SQL Server raises an error and rolls back the update action on the Customers row if there is at least one row in the Orders table that references it.
CHECK
Is a constraint that enforces domain integrity by limiting the possible values that can be entered into a column or columns.
NOT FOR REPLICATION
Keywords used to prevent the CHECK constraint from being enforced during the distribution process used by replication. When tables are subscribers to a replication publication, do not update the subscription table directly, instead update the publishing table, and let replication distribute the data back to the subscribing table. A CHECK constraint can be defined on the subscription table to prevent users from modifying it. Unless the NOT FOR REPLICATION clause is added, however, the CHECK constraint also prevents the replication process from distributing modifications from the publishing table to the subscribing table. The NOT FOR REPLICATION clause means the constraint is enforced on user modifications but not on the replication process.
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.
logical_expression
Is a logical expression that returns TRUE or FALSE.
column
Is a column or list of columns, in parentheses, used in table constraints to indicate the columns used in the constraint definition.
[ASC | DESC]
Specifies the order in which the column or columns participating in table constraints are sorted. The default is ASC.
n
Is a placeholder indicating that the preceding item can be repeated n number of times.
Remarks
SQL Server can have as many as two 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.
CREATE TABLE statements that include a sql_variant column can generate the following warning:
The total row size (xx) for table 'yy' exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added.This warning occurs because sql_variant can have a maximum length of 8016 bytes. When a sql_variant column contains values close to the maximum length, it can overshoot the row's maximum size limit.
Each table can contain a maximum of 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.
Temporary Tables
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 explicitly dropped using DROP TABLE:
- A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.
- All other local temporary tables are dropped automatically at the end of the current session.
- Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.
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. If a query references a temporary table, and two temporary tables with the same name exist at that time, it is not defined which table the query is resolved against. 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
Here is the result set:
(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.
Consider using table variables instead of temporary tables. Temporary tables are useful in cases when indexes need to be created explicitly on them, or when the table values need to be visible across multiple stored procedures or functions. In general, table variables contribute to more efficient query processing. For more information, see table.
PRIMARY KEY Constraints
- A table can contain only one PRIMARY KEY constraint.
- The index generated by a PRIMARY KEY constraint cannot cause the number of indexes on the table to exceed 249 nonclustered indexes and 1 clustered index.
- If CLUSTERED or NONCLUSTERED is not specified for a PRIMARY KEY constraint, CLUSTERED is used if there are no clustered indexes specified for UNIQUE constraints.
- All columns defined within a PRIMARY KEY constraint must be defined as NOT NULL. If nullability is not specified, all columns participating in a PRIMARY KEY constraint have their nullability set to NOT NULL.
UNIQUE Constraints
- If CLUSTERED or NONCLUSTERED is not specified for a UNIQUE constraint, NONCLUSTERED is used by default.
- Each UNIQUE constraint generates an index. The number of UNIQUE constraints cannot cause the number of indexes on the table to exceed 249 nonclustered indexes and 1 clustered index.
FOREIGN KEY Constraints
- When a value other than NULL is entered into the column of a FOREIGN KEY constraint, the value must exist in the referenced column; otherwise, a foreign key violation error message is returned.
- FOREIGN KEY constraints are applied to the preceding column unless source columns are specified.
- FOREIGN KEY constraints can reference only tables within the same database on the same server. Cross-database referential integrity must be implemented through triggers. For more information, see CREATE TRIGGER.
- FOREIGN KEY constraints can reference another column in the same table (a self-reference).
- The REFERENCES clause of a column-level FOREIGN KEY constraint can list only one reference column, which must have the same data type as the column on which the constraint is defined.
- The REFERENCES clause of a table-level FOREIGN KEY constraint must have the same number of reference columns as the number of columns in the constraint column list. The data type of each reference column must also be the same as the corresponding column in the column list.
- CASCADE may not be specified if a column of type timestamp is part of either the foreign key or the referenced key.
- It is possible to combine CASCADE and NO ACTION on tables that have referential relationships with each other. If SQL Server encounters NO ACTION, it terminates and rolls back related CASCADE actions. When a DELETE statement causes a combination of CASCADE and NO ACTION actions, all the CASCADE actions are applied before SQL Server checks for any NO ACTION.
- A table can contain a maximum of 253 FOREIGN KEY constraints.
- FOREIGN KEY constraints are not enforced on temporary tables.
- A table can reference a maximum of 253 different tables in its FOREIGN KEY constraints.
- FOREIGN KEY constraints can reference only columns in PRIMARY KEY or UNIQUE constraints in the referenced table or in a UNIQUE INDEX on the referenced table.
DEFAULT Definitions
- A column can have only one DEFAULT definition.
- A DEFAULT definition can contain constant values, functions, SQL-92 niladic functions, or NULL. The table shows the niladic functions and the values they return for the default during an INSERT statement.
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.
- constant_expression in a DEFAULT definition cannot refer to another column in the table, or to other tables, views, or stored procedures.
- DEFAULT definitions cannot be created on columns with a timestamp data type or columns with an IDENTITY property.
- DEFAULT definitions cannot be created for columns with user-defined data types if the user-defined data type is bound to a default object.
CHECK Constraints
- A column can have any number of CHECK constraints, and the condition can include multiple logical expressions combined with AND and OR. Multiple CHECK constraints for a column are validated in the order created.
- The search condition must evaluate to a Boolean expression and cannot reference another table.
- A column-level CHECK constraint can reference only the constrained column, and a table-level CHECK constraint can reference only columns in the same table.
CHECK CONSTRAINTS and rules serve the same function of validating the data during INSERT and DELETE statements.
- When a rule and one or more CHECK constraints exist for a column or columns, all restrictions are evaluated.
Additional Constraint Information
- An index created for a constraint cannot be dropped with the DROP INDEX statement; the constraint must be dropped with the ALTER TABLE statement. An index created for and used by a constraint can be rebuilt with the DBCC DBREINDEX statement.
- Constraint names must follow the rules for identifiers, except that the name cannot begin with a number sign (#). If constraint_name is not supplied, a system-generated name is assigned to the constraint. The constraint name appears in any error message about constraint violations.
- When a constraint is violated in an INSERT, UPDATE, or DELETE statement, the statement is terminated. However, the transaction (if the statement is part of an explicit transaction) continues to be processed. You can use the ROLLBACK TRANSACTION statement with the transaction definition by checking the @@ERROR system function.
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 about the amount of space allocated and used by a table, execute sp_spaceused.
Nullability Rules Within a Table Definition
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 was 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 for noncomputed columns 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 the column is defined with a user-defined data type:
- SQL Server uses the nullability specified when the data type was created. Use sp_help to get the default nullability of the data type.
- If the column is defined with a system-supplied data type:
- If the system-supplied data type has only one option, it takes precedence. timestamp data types must be NOT NULL.
- If the setting of sp_dbcmptlevel is 65 or lower, bit data types default to NOT NULL if the column does not have an explicit NULL or NOT NULL. For more information, see sp_dbcmptlevel.
- If any session settings are ON (turned on with the SET statement), then:
If ANSI_NULL_DFLT_ON is ON, NULL is assigned.
If ANSI_NULL_DFLT_OFF is ON, NOT NULL is assigned.
- If any database settings are configured (changed with sp_dboption), then:
If ANSI null default is true, NULL is assigned.
If ANSI null default is false, NOT NULL is assigned.
- If the system-supplied data type has only one option, it takes precedence. timestamp data types must be NOT NULL.
- When neither of the ANSI_NULL_DFLT options is set for the session and the database is set to the default (ANSI null default is false), then the SQL Server default of NOT NULL is assigned.
- If the column is a computed column, its nullability is always determined automatically by SQL Server. Use the COLUMNPROPERTY function (AllowsNull property) to find out the nullability of such a column.
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.
Permissions
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.
Examples
A. Use PRIMARY KEY constraints
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
B. Use FOREIGN KEY constraints
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)
C. Use UNIQUE constraints
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)
D. Use DEFAULT definitions
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
E. Use CHECK constraints
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]')
F. Complete table definitions
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 through 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 is 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')
)
G. Use the uniqueidentifier data type in a column
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)
)
H. Use an expression for a computed column
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
)
I. Use the USER_NAME function for a computed column
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()
)
J. Use NOT FOR REPLICATION
This example shows using the IDENTITY property on a table 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)
)