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

   @i int;
SET @I = 1;
WHILE @i <= 10
    if @i %2 = 1
        INSERT INTO test
        SELECT @i ,
               'word' + CONVERT( varchar(10) , @i) ,
              INSERT INTO test
        SELECT @i ,
               'word' + CONVERT( varchar(10) , @i) ,
                      'word' + CONVERT( varchar(10) , @i)
        SET @i+=1;

    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.
    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


No comments:

Post a Comment

Popular Posts