Looking for something else?
The following sections cover the special data types.
The timestamp data type is not designed to store date or time information, but rather is a binary value that serves as a version number of the record. The value is updated every time the record is updated, and the value is unique in the database. It is used to implement optimistic locking. You can find more details about this subject in "Optimistic Locking Using timestamp Values" in Chapter 15. Only one field in a table can be defined as the timestamp value. It occupies 8 bytes.
The uniqueidentifier data type stores 16-byte binary values. These values are often called globally unique identifiers (GUIDs). When a system generates a new GUID value, it is guaranteed that the same value cannot be produced again, neither on the same computer nor on any other computer in the world. GUIDs are generated using the identification number of the network card and a unique number obtained from the computer's clock. Manufacturers of network cards guarantee that the identification number of a network card will not be repeated in the next 100 years.
A uniqueidentifier constant is usually presented as
However, you will rarely type such values. In Transact-SQL, GUIDs should be generated using the NEWID function. There is also a Win32 API function that a client application can use to produce a GUID value.
uniqueidentifier values are used relatively often for implementations of web applications and distributed database systems. In web applications, designers might use the uniqueidentifier data type to generate a unique identifier before the record is sent to the database. In distributed systems, this data type serves globally unique identifiers.
The xml data type is a major new feature of SQL Server 2005. Before it was introduced, users were storing XML documents as strings or binary data. The new data type allows SQL Server to parse an XML document stored in an XML column and verify that it is compliant with the schema that the user associated with the column.
It is also useful in that a user can issue queries (in the XQuery language) against data in xml columns (for example, to find rows that contain XML documents that have specified values in specified attributes or elements). It is very exciting that it is possible to index xml columns and their attributes and elements, so that the engine does not have to do table and column scans.
However, apart from additional features, the xml data type is based on the varchar(max) data type and it is therefore limited to 2GB. The following example creates a table that has an xml column:
Create table dbo.Eq2( EqId int, EqCompList xml)You should use quotes as delimiters around XML constants:
INSERT INTO dbo.Eq2(EqId, EqCompList) VALUES(123,'
The sql_variant data type is based on the same idea as the variant data type in Visual Basic. It is designed to allow a single variable, column, or parameter to store values in different data types. Internally, variant objects record two values:
The following statement creates a lookup table that can store values of different types:
Create table Lookup( LookupGroupId tinyint, LookupId smallint, LookupValue sql_variant) Go
Before SQL Server 2000, more than one field was needed to store lookup values of different data types.
The following statements illustrate how you can insert different types of values in one column:
Insert Lookup (LookupGroupId, LookupId, LookupValue) Values (2, 34, 'VAR') Insert Lookup (LookupGroupId, LookupId, LookupValue) Values (3, 22, 2000) Insert Lookup (LookupGroupId, LookupId, LookupValue) Values (4, 16, '1/12/2000') Insert Lookup (LookupGroupId, LookupId, LookupValue) Values (4, 11, $50000)
A sql_variant object can store values of any data type except these:
But there are more serious restrictions on their use:
You should be very conservative in using the sql_variant data type. Its use has serious performance and design implications.
Basic Transact-SQL Programming Constructs
Tip 1: T-SQL identifiers
Tip 2: Database object qualifiers
Tip 3: Character string data types
Tip 4: Date, time and number data types
Tip5: Special data types-Part 1
Tip 6: Special data types-Part 2
Tip 7: Local variables
Tip 8: Global variables
Tip 9: Table variables
Tip 10: Flow control statements
Tip 11: Blocks, looping and conditional statements
Tip 12: Unconditional and scheduled execution
Tip 13: Transact SQL cursors
Tip 14: Cursor related statements
Tip 15: Problems and justified use of cursors
The previous tip is from "15 tips in 15 minutes: Basic Transact-SQL Programming Constructs," excerpted from Chapter 3, of the book "Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET" by Dejan Sunderic, courtesy of McGraw-Hill Publishing.