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 mismatchThe 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 @testThis is one of the behavioural changes in sql server 2005
Also refer
Behaviour Changes in SQL Server 2005