Export (0) Print
Expand All

column_definition (Transact-SQL)

Specifies the properties of a column that are added to a table by using ALTER TABLE.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

column_name <data_type>
    [ FILESTREAM ]
    [ COLLATE collation_name ] 
    [ NULL | NOT NULL ]
    [ 
        [ CONSTRAINT constraint_name ] DEFAULT constant_expression [ WITH VALUES ] 
      | IDENTITY [ ( seed , increment ) ] [ NOT FOR REPLICATION ] 
    ]
    [ ROWGUIDCOL ] 
    [ SPARSE ] 
    [ <column_constraint> [ ...n ] ]

<data type> ::= 
[ type_schema_name . ] type_name 
    [ ( precision [ , scale ] | max | 
        [ { CONTENT | DOCUMENT } ] xml_schema_collection ) ] 

<column_constraint> ::= 
[ CONSTRAINT constraint_name ] 
{     { PRIMARY KEY | UNIQUE } 
        [ CLUSTERED | NONCLUSTERED ] 
        [ 
            WITH FILLFACTOR = fillfactor  
          | WITH ( < index_option > [ , ...n ] ) 
        ] 
        [ ON { partition_scheme_name ( partition_column_name ) 
            | filegroup | "default" } ]
  | [ FOREIGN KEY ] 
        REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ] 
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ NOT FOR REPLICATION ] 
  | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) 
} 

column_name

Is the name of the column to be altered, added, or dropped. column_name can consist of 1 through 128 characters. For new columns, created with a timestamp data type, column_name can be omitted. If no column_name is specified for a timestamp data type column, the name timestamp is used.

[ type_schema_name. ] type_name

Is the data type for the column that is added and the schema to which it belongs.

type_name can be:

  • A Microsoft SQL Server system data type.

  • An alias data type based on a SQL Server system data type. Alias data types must be created by using CREATE TYPE before they can be used in a table definition.

  • A Microsoft .NET Framework user-defined type and the schema to which it belongs. A .NET Framework user-defined type must be created by using CREATE TYPE before it can be used in a table definition.

If type_schema_name is not specified, the Microsoft Database Engine references type_name in the following order:

  • The SQL Server system data type.

  • The default schema of the current user in the current database.

  • The dbo schema in the current database.

precision

Is the precision for the specified data type. For more information about valid precision values, see Precision, Scale, and Length (Transact-SQL).

scale

Is the scale for the specified data type. For more information about valid scale values, see Precision, Scale, and Length (Transact-SQL).

max

Applies only to the varchar, nvarchar, and varbinary data types. These are used for storing 2^31 bytes of character and binary data, and 2^30 bytes of Unicode data.

CONTENT

Specifies that each instance of the xml data type in column_name can comprise multiple top-level elements. CONTENT applies only to the xml data type and can be specified only if xml_schema_collection is also specified. If this is not specified, CONTENT is the default behavior.

DOCUMENT

Specifies that each instance of the xml data type in column_name can comprise only one top-level element. DOCUMENT applies only to the xml data type and can be specified only if xml_schema_collection is also specified.

xml_schema_collection

Applies to: SQL Server 2008 through SQL Server 2014.

Applies only to the xml data type for associating an XML schema collection with the type. Before typing an xml column to a schema, the schema must first be created in the database by using CREATE XML SCHEMA COLLECTION.

FILESTREAM

Optionally specifies the FILESTREAM storage attribute for column that has a type_name of varbinary(max).

When FILESTREAM is specified for a column, the table must also have a column of the uniqueidentifier data type that has the ROWGUIDCOL attribute. This column must not allow null values and must have either a UNIQUE or PRIMARY KEY single-column constraint. The GUID value for the column must be supplied either by an application when data is being inserted, or by a DEFAULT constraint that uses the NEWID () function.

The ROWGUIDCOL column cannot be dropped and the related constraints cannot be changed while there is a FILESTREAM column defined for the table. The ROWGUIDCOL column can be dropped only after the last FILESTREAM column is dropped.

When the FILESTREAM storage attribute is specified for a column, all values for that column are stored in a FILESTREAM data container on the file system.

For an example that shows how to use column definition, see FILESTREAM (SQL Server).

COLLATE collation_name

Specifies the collation of the column. If not specified, the column is assigned the default collation of the database. Collation name can be either a Windows collation name or an SQL collation name. For a list and more information, see Windows Collation Name (Transact-SQL) and SQL Server Collation Name (Transact-SQL).

The COLLATE clause can be used to specify the collations only of columns of the char, varchar, nchar, and nvarchar data types.

For more information about the COLLATE clause, see COLLATE (Transact-SQL).

NULL | NOT NULL

Determines whether null values are allowed in the column. NULL is not strictly a constraint but can be specified just like NOT NULL.

[ CONSTRAINT constraint_name ]

Specifies the start of a DEFAULT value definition. To maintain compatibility with earlier versions of SQL Server, a constraint name can be assigned to a DEFAULT. constraint_name must follow the rules for identifiers, except that the name cannot start with a number sign (#). If constraint_name is not specified, a system-generated name is assigned to the DEFAULT definition.

DEFAULT

Is a keyword that specifies the default value for the column. DEFAULT definitions can be used to provide values for a new column in the existing rows of data. DEFAULT definitions cannot be applied to timestamp columns, or columns with an IDENTITY property. If a default value is specified for a user-defined type column, the type must support an implicit conversion from constant_expression to the user-defined type.

constant_expression

Is a literal value, a NULL, or a system function used as the default column value. If used in conjunction with a column defined to be of a .NET Framework user-defined type, the implementation of the type must support an implicit conversion from the constant_expression to the user-defined type.

WITH VALUES

Specifies that the value given in DEFAULT constant_expression is stored in a new column added to existing rows. If the added column allows null values and WITH VALUES is specified, the default value is stored in the new column, added to existing rows. If WITH VALUES is not specified for columns that allow nulls, the value NULL is stored in the new column in existing rows. If the new column does not allow nulls, the default value is stored in new rows regardless of whether WITH VALUES is specified.

IDENTITY

Specifies that the new column is an identity column. The SQL Server Database Engine provides a unique, incremental value for the column. When you add identifier columns to existing tables, the identity numbers are added to the existing rows of the table with the seed and increment values. The order in which the rows are updated is not guaranteed. Identity numbers are also generated for any new rows that are added.

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 a tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) column. Only one identity column can be created per table. The DEFAULT keyword and bound defaults cannot be used with an identity column. Either both the seed and increment must be specified, or neither. If neither are specified, the default is (1,1).

Note Note

You cannot modify an existing table column to add the IDENTITY property.

Adding an identity column to a published table is not supported because it can result in nonconvergence when the column is replicated to the Subscriber. The values in the identity column at the Publisher depend on the order in which the rows for the affected table are physically stored. The rows might be stored differently at the Subscriber; therefore, the value for the identity column can be different for the same rows..

To disable the IDENTITY property of a column by allowing values to be explicitly inserted, use SET IDENTITY_INSERT.

seed

Is the value used for the first row loaded into the table.

increment

Is the incremental value added to the identity value of the previous row that is loaded.

NOT FOR REPLICATION

Applies to: SQL Server 2008 through SQL Server 2014.

Can be specified for the IDENTITY property. If this clause is specified for the IDENTITY property, values are not incremented in identity columns when replication agents perform insert operations.

ROWGUIDCOL

Applies to: SQL Server 2008 through SQL Server 2014.

Specifies that the column is a row globally unique identifier column. ROWGUIDCOL can only be assigned to a uniqueidentifier column, and only one uniqueidentifier column per table can be designated as the ROWGUIDCOL column. ROWGUIDCOL cannot be assigned to columns of user-defined data types.

ROWGUIDCOL does not enforce uniqueness of the values stored in the column. Also, ROWGUIDCOL does not automatically generate values for new rows that are inserted into the table. To generate unique values for each column, either use the NEWID function on INSERT statements or specify the NEWID function as the default for the column. For more information, see NEWID (Transact-SQL)and INSERT (Transact-SQL).

SPARSE

Indicates that the column is a sparse column. The storage of sparse columns is optimized for null values. Sparse columns cannot be designated as NOT NULL. For additional restrictions and more information about sparse columns, see Use Sparse Columns.

<column_constraint>

For the definitions of the column constraint arguments, see column_constraint (Transact-SQL).

If a column is added having a uniqueidentifier data type, it can be defined with a default that uses the NEWID() function to supply the unique identifier values in the new column for each existing row in the table.

The Database Engine does not enforce an order for specifying DEFAULT, IDENTITY, ROWGUIDCOL, or column constraints in a column definition.

ALTER TABLE statement will fail if adding the column will cause the data row size to exceed 8060 bytes.

Community Additions

ADD
Show:
© 2014 Microsoft