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
   
   

   


No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts