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