sql_variant

Introduction

Data type is a very important factor. Think about situations when we the developers are little bit confused related to data type and don't actually know what type of data actually stores. To solve this type of data type related problem the MS SQL Server provides a special kind of data type called variant. In MS SQL Server it is called sql_variant data type.

In this article, I am trying to demonstrate the feature of sql_variant data type. Hope it will be interesting.

It can't hold

We can use the sql_variant to store data of an unspecified or inconsistent type. We store data of almost any SQL Server data type.

sql_variant can hold any data type except text, ntext, image, and timestamp without conversion.

Example of   sql_variant

-- Create the Base Table

IF OBJECT_ID('my_Table') IS NOT NULL

   BEGIN

     DROP TABLE my_Table;

   END

GO  

CREATE TABLE my_Table

       (ID    INT         NOT NULL IDENTITY(1,1) PRIMARY KEY,

        DATA  sql_variant NOT NULL);

       

GO

-- Insert Records

INSERT INTO my_Table

       (DATA)

VALUES (GETDATE());

GO

INSERT INTO my_Table

       (DATA)

VALUES ('Joydeep Das');

GO

INSERT INTO my_Table

       (DATA)

VALUES (124);

GO

INSERT INTO my_Table

       (DATA)

VALUES(124.22); 

GO

-- Display Records

SELECT * FROM   my_Table;

Result Set:

ID          DATA

----------- ------------------------

1           2012-11-06 15:58:36.197

2           Joydeep Das

3           124

4           124.22

(4 row(s) affected)

Stores original data type in native format

Actually the sql_variant stores the data type in its native format. We can use the SQL_VARIANT_PROPERTY function to see the native data type.

Let's see the use of SQL_VARIANT_PROPERTY with above example.

-- To see the Base Data type.

SELECT ID,

       SQL_VARIANT_PROPERTY(DATA, 'BaseType')   BaseType

FROM   my_Table; 

Output:

ID          BaseType

----------- ----------

1           datetime

2           varchar

3           int

4           numeric

(4 row(s) affected)

To see the Precisions

-- Precision

SELECT ID,

       SQL_VARIANT_PROPERTY(DATA, 'Precision') Precisions

FROM   my_Table; 

To see the Scale

-- Scale

SELECT ID,

       SQL_VARIANT_PROPERTY(DATA, 'Scale')   Scale

FROM   my_Table;

Practical use

There are 2 practical use of it

1.    As a parameters of stored procedure.

Example:

      CREATE PROCEDURE spTest (@parm sql_variant)

      AS

      Select @parm, SQL_VARIANT_PROPERTY(@parm, 'BaseType')

GO

We can us the functions like IsNumeric or IsDate to test what type of parameter. 

2.    Make the user define fields of the Table.

Some times in application the user can create user define fields (UDF)in a table to store something depends on their needs.

References

http://msdn.microsoft.com/en-us/library/ms178550.aspx

Related tropics

1.    http://sqlknowledgebank.blogspot.in/2012/11/columns-without-data-type.html

2.    http://sqlknowledgebank.blogspot.in/2012/05/computed-columns.html

Summary

Please share you knowledge by making comments related to the practical use of sql_variant.

Hope you like it.

Posted by: MR. JOYDEEP DAS