Transact-SQL Reference

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:

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 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
UNIQUE Constraints
FOREIGN KEY Constraints
DEFAULT Definitions
CHECK Constraints
Additional Constraint Information

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:

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)
   )

See Also

ALTER TABLE

COLUMNPROPERTY

CREATE INDEX

CREATE RULE

CREATE VIEW

Data Types

DROP INDEX

DROP RULE

DROP TABLE

sp_addtype

sp_depends

sp_help

sp_helpconstraint

sp_rename

sp_spaceused