Special data types are those that do not fit into any of the other data type categories. For example, to store data as 1 or 0 corresponding to yes or no values in a customer survey, use the bit data type. Microsoft® SQL Server™ has three data types that fit into this category:
bit data does not need to be enclosed in single quotation marks. It is numeric data similar to SQL Server’s integer and numeric data, except that only 0s and 1s can be stored in bit columns.
The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms. Every time a page was modified, it was stamped with the current @@DBTS value and @@DBTS was incremented by one. This was sufficient for recovery to determine the relative sequence in which pages had been modified, but the timestamp values had no relationship to time.
In SQL Server version 7.0, @@DBTS is only incremented for use in timestamp columns. If a table contains a timestamp column, every time a row is modified by an INSERT, UPDATE, or DELETE statement, the timestamp value in the row is set to the current @@DBTS value, and then @@DBTS is incremented by one.
Never use timestamp columns in keys, especially primary keys, because the timestamp value changes every time the row is modified.
To record the times data modifications take place in a table, use either a datetime or smalldatetime data type to record the events and triggers to automatically update the values when any modification takes place.
User-defined data types allow you to extend a SQL Server base data type (such as varchar) with a descriptive name and format tailored to a specific use. For example, this statement implements a birthday user-defined data type that allows NULLs, using the datetime base data type:
EXEC sp_addtype birthday, datetime, 'NULL'
Care should be taken with the base types chosen to implement user-defined data types. For example, in the United States, Social Security numbers have a format of nnn-nn-nnnn. While Social Security numbers contain numbers, the numbers form an identifier and are not subjected to mathematical operations. It is therefore common practice to create a user-defined Social Security number data type as varchar and create a CHECK constraint to enforce the format of the social security numbers stored in the table:
EXEC sp_addtype SSN, 'VARCHAR(11)', 'NOT NULL'
GO
CREATE TABLE ShowSSNUsage
(EmployeeID INT PRIMARY KEY,
EmployeeSSN SSN,
CONSTRAINT CheckSSN CHECK ( EmployeeSSN LIKE
'[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]' )
)
GO
If the SSN columns are typically used as key columns in indexes, especially clustered indexes, the size of the keys can be shrunk from 11 bytes to 4 if the SSN user-defined data type is instead implemented using the int base data type. This reduction in key size improves data retrieval. The improved efficiency of data retrieval and the elimination of the need for the CHECK constraint will usually outweigh the extra conversion processing from int to a character format when displaying or modifying SSN values.
| Data Types | Special Data |