 |
|
|
Home > Special data types-Part 1 |
|
|
|
|
|
|
Special data types-Part 1 |
|
| 07 Feb 2007 | McGraw-Hill |
|
|
The following sections cover the special data types.
timestamp
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.
uniqueidentifier
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
Character string '{BB7DF450-F119-11CD-8465-00AA00425D90}'
Binary constant 0xaf16a66f7f8b31d3b41d30c04fc96f46
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.
xml
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,'')
sql_variant
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 actual value
The metadata describing the variant: base data type, maximum size, scale, precision, and collation
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:
text
ntext
image
varchar(max)
nvarchar(max)
varbinary(max)
timestamp
sql_variant
any user-defined data types
But there are more serious restrictions on their use:
sql_variant columns are limited to 8,016 bytes.
sql_variant columns can be used in indexes and unique keys if the total length of the data in the key is shorter than 900 bytes. However, this is not a limitation of the sql_variant data type. Indexes cannot be based on columns that are larger than 900 bytes in total.
sql_variant columns cannot have an identity property.
sql_variant columns cannot be part of a computed column.
You must use functions for converting data types when assigning values from sql_variant objects to objects of other data types.
The comparison of sql_variant values has complex rules and is prone to errors.
sql_variant values are automatically converted to nvarchar(4000) when accessed from client applications using OLE DB Provider for SQL Server 7.0 or the SQL Server ODBC Driver from SQL Server 7.0. If stored values are longer than 4,000 characters, SQL Server will return just the first 4,000 characters.
sql_variant values are automatically converted to varchar(255) when accessed from client applications using the SQL Server ODBC Driver from SQL Server 6.5 or earlier, or using DB-Library. If stored values are longer than 255 characters, SQL Server will return just the first 255 characters.
sql_variant columns are not supported in the Like predicate.
sql_variant columns do not support full-text indexes.
sql_variant objects cannot be concatenated using the + operator, even if the stored values are strings or numeric. The proper solution is to convert values before concatenation.
Some functions—Avg(), Identity(), IsNumeric(), Power(), Radians(), Round(), Sign(), StDev(), StDevP(), Sum(), Var(), VarP()—do not support sql_variant parameters.
TIP
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
 Home: Introduction
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.
|
|
|
|
|
|
| TechTarget provides enterprise IT professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective IT purchase decisions and managing their organizations' IT projects - with its network of . |
|
| | |
All Rights Reserved, , TechTarget |
|
|
|
|
|
