Export (0) Print
Expand All

Modifying Column Properties

SQL Server 2000

Each column in a table has a set of properties, such as name, data type, nullability, and data length. The entire set of properties for a column makes up the definition of the column in a table.

The column properties can be specified directly in a database table by using a database diagram. Three column properties are required before you can create a table in the database:

  • Column name

  • Data type

  • Data length

The properties of a column can be changed, for example, by renaming it, altering its length, specifying a default value, and so on.

Column Data Type

The data type of an existing column can be changed provided that the existing data in the column can be implicitly converted to the new data type. For more information, see ALTER TABLE.

Column Data Length

When you select a data type, length is defined automatically. You can increase or decrease the length property only for a column with a data type of binary, char, nchar, varbinary, varchar, or nvarchar. For columns with other data types, the length is derived from the data type and cannot be changed. If the new specified length is smaller than the original column length, all values in the column that exceed the new length are truncated without any warning. It is not possible to change the length of a column defined with a PRIMARY KEY or FOREIGN KEY constraint.

Note  Changing the column data length re-creates the table in the database when you save the table or database diagram using SQL Server Enterprise Manager.

Column Precision

The precision of a numeric column is the maximum number of digits used by the selected data type. The precision of a nonnumeric column generally refers to either the maximum length or the defined length of the column.

For all data types except decimal and numeric, precision is defined automatically. You can change the column precision for the decimal and numeric data types if you want to redefine the maximum number of digits these columns use. SQL Server Enterprise Manager prevents you from changing the precision of a column that does not have one of these assigned data types.

Note  Changing the column precision re-creates the table in the database when you save the table or database diagram using SQL Server Enterprise Manager.

Column Scale

The scale of a numeric or decimal column is to the maximum number of digits to the right of the decimal point. When you select a data type, the column scale by default is set to 0. For columns with approximate floating point numbers, the scale is undefined because the number of digits to the right of the decimal point is not fixed. You can change the scale for a numeric or decimal column if you want to redefine the number of digits that can appear to the right of the decimal point.

Note  Changing the column scale re-creates the table in the database when you save the table or diagram using SQL Server Enterprise Manager.

Column Nullability

A column can be defined to either allow or disallow null values. By default, a column permits null values. An existing column can be changed to disallow null values only if no existing null values exist in the column and there is no existing index created on the column. To disallow null values in an existing column that contains null values:

  1. Add a new column with a DEFAULT definition that inserts a valid value in place of NULL.

  2. Copy the data in the old (existing) column to the new column.

  3. Delete the old column.

An existing column that does not allow null values can be changed to allow null values unless a PRIMARY KEY constraint is defined on the column.

Note  Changing the nullability on a new, nonkey column re-creates the table in the database when you save the table or database diagram using Database Diagrams within SQL Server Enterprise Manager.

To set column properties

Transact-SQL

Enterprise Manager

SQL-DMO

To view column properties

Transact-SQL

Enterprise Manager

SQL-DMO

To rename a column

Transact-SQL

Enterprise Manager

SQL-DMO

See Also

Working with Tables

Database Objects

Precision, Scale, and Length

Show:
© 2014 Microsoft