Assigning a Data Type to a Column
Assigning a data type to each column is one of the first steps to take in designing a table. Data types define the data value allowed for each column. Data types can be assigned to a column by one of the following:
- Using SQL Server 2005 system data types
- Creating alias data types that are based on system data types.
- Creating user-defined types from types you create in the Microsoft .NET Framework common language runtime.
For example, if you want to include only names in a column, you can assign one of the character data types to the column. Similarly, if you want a column to contain only numbers, you can assign one of the numeric data types. For more information about data types, see Data Types (Database Engine).
SQL Server 2005 also supports SQL-92 synonyms for several base data types. For more information, see Data Type Synonyms (Transact-SQL).
Enforcing Data Integrity
System, alias, and user-defined types can be used to enforce data integrity. This is because the data entered or changed must comply with the type specified in the original CREATE TABLE statement. For example, you cannot store a surname in a column defined as datetime because a datetime column accepts only valid dates. Generally, keep numeric data in numeric columns, especially if calculations must be performed on the numeric data at a later date.