Using special data

Special data types refer to data types that do not fit into other data type classifications. For example, to store the value "yes" or "no" in a customer survey by 1 or 0, you can use the bit data type. microsoft® SQL server& #8482; 2000 there are several data types in this category:
Bit
Bit data does not have to be placed in single quotes. It is a numeric data similar to the integer and numeric data of SQL Server, but the bit column can only store 0 and 1.
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 the sql_variant column records the data value and the metadata that describes the value: the underlying data type, maximum size, scale, precision, and collation of the value.
The second table in the following example 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)
)
To obtain metadata information for any particular sql_variant instance, use the Sql_variant_property function.
Table
The table data type is similar to a temporary table that can be used to store a result set for later processing. This data type can only be used to define local variables for table types and return values for user-defined functions.
The definition of a table variable or return value includes the column, data type, precision, number of decimal places per column, and optional PRIMARY KEY, UNIQUE, and check constraints.
The format of a row stored in a table variable or in a user-defined function return value must be defined when a variable is declared or when a function is created. Its syntax is based on the CREATE
The syntax of the TABLE. 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
A table variable and a user-defined function that returns a table can be used only for some SELECT and INSERT statements, and where UPDATE, DELETE, and