Tuesday 6 November 2012

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

Related tropics


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

3 comments:

  1. Additional Note:
    ---------------
    One thing that I observed:

    If I try to insert the records like this, it will give some error.

    -- Insert Records
    INSERT INTO my_Table
    (DATA)
    VALUES (GETDATE()),('Joydeep Das'),(124),
    (124.22)

    Error Msg:
    Msg 241, Level 16, State 1, Line 1
    Conversion failed when converting date and/or time from character string.

    If anyone have any explanations please update it.

    Thank you.

    ReplyDelete
  2. The answer is found in Books Online under "Values Clause". The second sentance explains it all.

    "The values are specified as a comma-separated list of scalar expressions whose data type, precision, and scale must be the same as or implicitly convertible to the corresponding column in the column list."

    What they don't explicitly tell you is that it's a set-based operation instead of RBAR (pronounced as "ree-bar" and is a "Modenism" for "Row By Agaonizing Row"). That means that the INSERT has to pick a datatype for the virtual column of data and it does so according to the rules of datatype precedence. In the case you demonstrated, DATETIME will be the highest precedence and neither the string literal that you used won't convert to a DATETIME datatype.

    As a sidebar, you should never use the likes of ISNUMERIC or ISDATE to determine datatype from an SQL_VARIANT because things like the following:

    SELECT ISNUMERIC(2e3), ISDATE('2000')

    Both functions are reporting correct. 2e3 is valid scientific notation for the number 2000 and is correctly considered to be numeric. '2000' is shorthand for '2000-01-01' as a date and is correctly considered to be a date. The functions aren't broken. They are workinng precisely as designed.

    Please see the article at the following URL for more information on the correct workings of ISNUMERIC because it's not what most people think it is.
    http://www.sqlservercentral.com/articles/IsNumeric/71512/

    --Jeff Moden

    ReplyDelete