sql_variant data type in SQL Server

Geeking with sql_variant.

I found a really cool way use on the sql_variant type that I need to post, but wanted to share one thing that I saw today.  Run the following script in SQL Server 2008: 

IF OBJECT_ID ('test') IS NOT NULL
       DROP TABLE test
GO 

CREATE TABLE test
             (ID int
              ,VAL int
              )

GO

INSERT INTO test VALUES (1,2), (2, 2)
GO

IF OBJECT_ID ('test') IS NOT NULL
       DROP TABLE test
GO

CREATE TABLE test
              (ID int
              ,VAL sql_variant
              )
GO

INSERT INTO test VALUES (1,'A'), (2, 2)
GO

/*
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'A' to data type int.
*/

There are some blogs on the internet stating to stay clear of this data type, and I probably would have to agree. However, at my latest client, I actually used the type in a really neat scenario and it worked like a charm. Well, almost.  I"m finding some really weird stuff happening when you use the data type.  Will be writing that one up soon.

Lee

----------------------------

“On, Donner! On, Blitzen! On, Chewy! On, Tavo!  C'mon, Becto! And then, the reindeers used ta take off into da sky and fly across da sky, man!”