How to use SQL_VARIANT, TimeStamp, UniqueIdentifier and XML datatypes

Problem: What are the other datatypes in SQL Server

Solution:

sql_variant – dynamic data types.

timestamp – Row Version

uniqueidentifier - 16-byte GUID.

xml - XML data

SQL Server 2008 or higher.

These are few data types which we won’t be using in our day to day activity, but good to know these are available when are in a need of it.

Example:

Let’s See how we can use this data types and what can make a difference.

In this article I am going to show you how we can use this data types as a column inside an ordinary table. Also for us to see how this helps in our daily activity I am populating the table with some junk values.

CREATE TABLE dbo.OtherDataTypes (

                Varinatcolumn SQL_VARIANT,

                Row_version TIMESTAMP,

                Date_Time DATETIME DEFAULT GETDATE(),

                strGUID UNIQUEIDENTIFIER,

                XMLData XML

                )

INSERT INTO dbo.OtherDataTypes (

                [Varinatcolumn],

                [strGUID],

                [XMLData]

                )

SELECT substring(cast(newid() AS VARCHAR(68)), 1, 6) + cast(RAND() AS VARCHAR(100)),

                NEWID(),

                (SELECT TOP 1 * FROM sys.types FOR XML RAW('test'),    ROOT('values')  )

                GO 50

SELECT [Varinatcolumn],

                [Row_version],

                [strGUID],

                [Date_Time],

                [XMLData]

FROM .[dbo].[OtherDataTypes]

Output:

Advantages:

sql_variant – most data types compatible

timestamp – Helps on solving the lost updates, concurrent transactions etc.

uniqueidentifier – unique across the database.

xml – allows to bind multiple values in one string.

Disadvantages:

sql_variant – doesn’t allow to search using wild cards, text gets truncated to 4000. Can yield different results when comparing values of same base data type.

timestamp – cannot be used as primary key, index or dynamic cursor.

uniqueidentifier – Index consumes a lot of space, slower performance in larger datasets.

xml – indexing is painful and searching in general.