Special data type: bit, sql_variant

一，bit data type

An integer data type that can take a value of 1, 0, or NULL.

The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.

The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.

Converting to bit promotes any nonzero value to 1.

1，Example

declare @bit_true bit
declare @bit_false bit
set @bit_true=‘true‘
set @bit_false=‘false‘
select @bit_true,@bit_false

2，Storage of bit

The bit data type can store a 0 or 1 and can consume only a single bit of storage space. However, if a table has only 1 bit column, that column will take up a whole byte. Up to 8 bit columns are stored in a single byte.

二，sql_variant data type

sql_variant can have a maximum length of 8016 bytes. This includes both the base type information and the base type value. The maximum length of the actual base type value is 8,000 bytes.

A sql_variant data type must first be cast to its base data type value before participating in operations such as addition and subtraction.

sql_variant can be assigned a default value. This data type can also have NULL as its underlying value, but the NULL values will not have an associated base type. Also, sql_variant cannot have another sql_variant as its base type.

A unique, primary, or foreign key may include columns of type sql_variant, but the total length of the data values that make up the key of a specific row should not be more than the maximum length of an index. This is 900 bytes.

A table can have any number of sql_variant columns.

sql_variant cannot be used in CONTAINSTABLE and FREETEXTTABLE.

ODBC does not fully support sql_variant. Therefore, queries of sql_variant columns are returned as binary data when you use Microsoft OLE DB Provider for ODBC (MSDASQL). For example, a sql_variant column that contains the character string data ‘PS2091‘ is returned as 0x505332303931.

1，Converting sql_variant Data                                    

declare @var_int sql_variant
declare @var_bit sql_variant

set @var_bit=‘true‘
set @var_int=10

select @var_bit,@var_int,cast(@var_bit as bit),cast(@var_int as int)

2，Storage of sql_variant

Internall, one column of type sql_variant is considered variable lenght. Its storage struture depends on the data type, but the first byte of every sql_variant field always indicates the actual data type being used in that row. 

参考文档：

sql_variant (Transact-SQL)

Special data type: bit, sql_variant

原文：http://www.cnblogs.com/ljhdo/p/5144774.html