column_definition (Transact-SQL)

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

Transact-SQL Syntax Conventions

Syntax

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

Arguments

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

• 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 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 Getting Started with FILESTREAM Storage.

• [ 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. 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). You cannot modify an existing table column to add the IDENTITY property.

  Note

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

Remarks

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.

Examples

For examples, see ALTER TABLE (Transact-SQL).

See Also

Reference

ALTER TABLE (Transact-SQL)

Concepts

Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION