Export (0) Print
Expand All

column_definition (Transact-SQL)

Updated: 5 December 2005

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

Topic link icon Transact-SQL Syntax Conventions


column_name [ type_schema_name. ] type_name
    [ 
                ( { precision [ , scale ] | max | 
            [ { CONTENT | DOCUMENT } ] xml_schema_collection } ) 
    ] 
    [ 
        [ CONSTRAINT constraint_name ] DEFAULT constant_expression 
             [ WITH VALUES ] 
        | IDENTITY [ (seed , increment ) ] [ NOT FOR REPLICATION ] 
    ] 
    [ ROWGUIDCOL ] 
    [ COLLATE < collation_name > ] 
    [ <column_constraint> [ ...n ] ] 

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, column_name can be omitted for columns created with a timestamp data type. 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 SQL Server 2005 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.

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

[ CONSTRAINT constraint_name ]

Specifies the start of a DEFAULT 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 should 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 applied starting with the order in which those rows were originally added. 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). You cannot modify an existing table column to add the IDENTITY property.

ms187742.note(en-US,SQL.90).gifNote:
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

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. For more information, see Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION.

ROWGUIDCOL

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

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

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.

Release History

5 December 2005

New content:
  • Clarified behavior of the IDENTITY clause when it is used to add a new identifier column.
  • Clarified that you cannot modify an existing table column to add the IDENTITY property.

Community Additions

ADD
Show:
© 2014 Microsoft