• Article

Using Special Data Types

This topic describes the special data types that are available in SQL Server. Special data types are those that do not fit into any of the other data type categories. In SQL Server, the special data types include the bit, hierarchyid, sql_variant, sysname, table, timestamp and alias data types.

bit

The bitdata type is a numeric data type that stores either 0 or 1. The string values true and false can be converted to bit values, as shown in the following example:

SELECT CONVERT (bit, 'true')
SELECT CONVERT(bit, 'false')

In this example, true is converted to 1 and false is converted to 0. bit data does not have to be enclosed in single quotation marks.

hierarchyid

The hierarchyid data type is used to manage hierarchical data and tables that have a hierarchical structure. To work with hierarchical data in Transact-SQL code, use hierarchyid functions. For more information, see Using hierarchyid Data Types (Database Engine).

sql_variant

The sql_variant data type enables a single column, parameter, or variable to store data values of different data types. Each instance of a sql_variant column records the value and the metadata that describes the value. The following metadata is available:

  • Base data type

  • Maximum size

  • Scale

  • Precision

  • Collation

To retrieve metadata for a specific sql_variant instance, use the SQL_VARIANT_PROPERTY function.

In the following example, the second table contains a 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))

sysname

The sysname data type 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 the same as nvarchar(128) except that, by default, sysname is NOT NULL. In earlier versions of SQL Server, 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 when it appears in lowercase.

table

The table data type functions like a temporary table. It is 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 table cannot be used as a data type.

The format of the rows that are stored in a table variable or that are 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 that are 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 that is used to define the table variables and user-defined functions, see DECLARE @local_variable (Transact-SQL) and CREATE FUNCTION (Transact-SQL).

timestamp

The timestamp data type has nothing to do with times or dates. timestamp values are binary numbers that indicate the relative sequence in which data modifications have occurred in a database.

Never use timestamp columns in keys, especially primary keys, because the timestamp value changes every time that the row is modified.

To record the times when data modifications occur in a table, use either a datetime2 or smalldatetime data type to record the events and triggers to automatically update the values when any modification occurs.

Alias Data Types

Alias data types enable you to extend a SQL Server base data type, such as varchar, with a descriptive name and format that can be customized for a specific use. For example, the following statement implements a birthday user-defined data type that is based on the datetime data type and allows for null values (NULL):

EXEC sp_addtype birthday, datetime, 'NULL'

Be careful when you select 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. Although Social Security numbers contain numbers, the numbers form an identifier and are not subjected to mathematical operations. Therefore, it is 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 that are stored in the table, as shown in the following example:

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 reduced from 11 bytes to 4 when the SSN user-defined data type is implemented by using the int base data type instead. 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 the SSN values are being displayed or modified.