Using Special Data

Article
12/03/2008

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 2005 has several data types that fit into this category:

bit
bit data does not need to be enclosed in single quotation marks. It is numeric data similar to SQL Server integer and numeric data, except that only 0s and 1s can be stored in bit columns.

The string values true and false can be converted to bit values as shown below.
```
SELECT CONVERT (bit, 'true'), CONVERT(bit, 'false')
```
True is converted to 1 and False is converted to 0.

sql_variant
The sql_variant data type in SQL Server allows a single column, parameter, or variable to store data values of different data types. Each instance of an sql_variant column records the data value and the metadata describing the value: its base data type, maximum size, scale, precision, and collation.

The second table in the following example contains an sql_variant column:
```
CREATE TABLE ObjectTable (ObjectID int CONSTRAINT PKObjectTable PRIMARY KEY, ObjectName nvarchar(80), ObjectWeight decimal(10,3), ObjectColor nvarchar(20))
CREATE TABLE VariablePropertyTable (ObjectID int REFERENCES ObjectTable(ObjectID), PropertyName      nvarchar(100), PropertyValue sql_variant, CONSTRAINT PKVariablePropertyTable PRIMARY KEY(ObjectID, PropertyName))
```
To get the metadata information for any specific sql_variant instance, use the SQL_VARIANT_PROPERTY function.

sysname
Instances of SQL Server include a user-defined data type named sysname. sysname is used for table columns, variables, and stored procedure parameters that store object names. The exact definition of sysname is related to the rules for identifiers; therefore, it can vary between instances of SQL Server. sysname is functionally equivalent to nvarchar(128). SQL Server version 6.5 or earlier only supports only smaller identifiers; thus, in earlier versions, sysname is defined as varchar(30).

Important

In databases that are case-sensitive, or that have a binary collation, sysname is recognized as a SQL Server system data type only if it appears in lowercase.

table
The table data type is like temporary tables and can be used to store a result set for later processing. This data type can only be used to define local variables of type table and the return value of a user-defined function.

The definition of a table variable or return value includes definitions of the columns, the data type, precision, and scale of each column, and optional PRIMARY KEY, UNIQUE, NULL, and CHECK constraints. A user-defined data type cannot be used as a data type.

The format of the rows stored in a table variable or returned by a user-defined function must be defined when the variable is declared or the function is created. The syntax is based on the CREATE TABLE syntax. For example:
```
DECLARE @TableVar TABLE (Cola int PRIMARY KEY, Colb char(3))

INSERT INTO @TableVar VALUES (1, 'abc')
INSERT INTO @TableVar VALUES (2, 'def')

SELECT * FROM @TableVar
GO
```
table variables and user-defined functions that return a table can be used only in certain SELECT and INSERT statements, and where tables are supported in the UPDATE, DELETE, and DECLARE CURSOR statements. table variables and user-defined functions that return a table cannot be used in any other Transact-SQL statements.

Indexes or other constraints applied to the table must be defined as part of the DECLARE variable or CREATE FUNCTION statement. They cannot be applied later, because the CREATE INDEX or ALTER TABLE statements cannot reference table variables and user-defined functions.

For more information about the syntax used to define the table variables and user-defined functions, see DECLARE @local\_variable (Transact-SQL) and CREATE FUNCTION (Transact-SQL).

timestamp
The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamp values 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. Each 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 and SQL Server 2000, @@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.

Alias data types
Alias data types allow you to extend an 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'
```
Be careful when choosing the base types for implementing 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.

Using Special Data

See Also

Other Resources

Help and Information

Additional resources