SQL_VARIANT datatype and behaviour changes

When you use sql_variant datatype in sql server 2000, and add data to a table
using union all, it will work though datatype of data differs from each other

declare @test table(variant_column sql_variant)
insert into @test
select 'test' union all
select 345 union all
select getdate() union all
select '3245.23'
select variant_column from @test

But in sql server 2005, it will throw error for datatype mismatch.

The proper way of doing this is to explicitely cast any one of the values to be of sql_variant datatype

declare @test table(variant_column sql_variant)
insert into @test
select cast('test' as sql_variant) union all
select 345 union all
select getdate() union all
select '3245.23'
select variant_column from @test

This is one of the behavioural changes itroduced in sql server 2005 and is followed in all the succeeding versions
 

Advertisement