SQL Variant

SQL Variant

 Its data type that stores values of various SQL Server-supported data types.

 In certain conditions where we are not sure about datatype we can define it as sql_variant for testing purpose.

 Its supporting int,char and binary value but will not support char(max)/varchar(max)/nvarchar(max) .

CREATE TABLE test( id int ,

                   word varchar(100) ,

                   data sql_variant

                 );

                      

DECLARE

   @i int;

SET @I = 1;

WHILE @i <= 10

    BEGIN

    if @i %2 = 1

        INSERT INTO test

        SELECT @i ,

               'word' + CONVERT( varchar(10) , @i) ,

                     @i

    else

              INSERT INTO test

        SELECT @i ,

               'word' + CONVERT( varchar(10) , @i) ,

                      'word' + CONVERT( varchar(10) , @i)

       

        SET @i+=1;

    END;

    SELECT *,

               Sql_variant_property(DATA, 'BaseType') BaseType

    FROM   test

    We would like to know datatype of data stored in SQL_VARIANT.

    We can get this information from Sql_variant_property by using basetype argument.

    

    Limits

    According to BOL          

    sql_variant can have a maximum length of 8016 bytes. This includes both the base type information and the base type value. The maximum length of the actual base type value is 8,000 bytes.

    sql_variant cannot be used in CONTAINSTABLE and FREETEXTTABLE.

    sql_variant cannot be used in like search

    SELECT *

    FROM   test

    WHERE  data LIKE '%wo%'

    This will fail.

    We have to cast it to its basetype before doing such operation

    SELECT *

    FROM   test

    WHERE  CONVERT(VARCHAR(10), data) LIKE '%wo%'

   

    SELECT *

    FROM   test

    WHERE  CONVERT(INT, data) > 3

           AND Isnumeric(data) = 1