Using Special Data

Accessing and Changing Relational Data

Using Special Data

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™ 2000 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.

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 this 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 meta data information for any specific sql_variant instance, use the SQL_VARIANT_PROPERTY function.

table
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, and CHECK constraints.

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 and CREATE FUNCTION.
timestamp
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. 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.
User-defined data types
User-defined 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

Accessing and Changing Relational Data

Using Special Data

See Also

Get in touch