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 in sql server 2005

Also refer
Behaviour Changes in SQL Server 2005

Published Friday, February 20, 2009 9:26 AM by Madhivanan
Filed under: , ,

Comments

# sql_variant in SQL Server and ANYDATA Type in Oracle « Systems Engineering and RDBMS

Pingback from  sql_variant in SQL Server and ANYDATA Type in Oracle « Systems Engineering and RDBMS