SQL_Variant.. Finally a use for it...

The “object” type of SQL Server is hardly used and with good reason.  RDBMS are meant to be “strongly typed“.. it is the foundation of the domain/type concept. 

RDBMS : “What are you INSERTing Dave?”

Dave: “I don't know!“

RDBMS : “Get stuffed!”

Anyway I am knocking out a stored proc that returns “Quick Stats” to the client.  Basically just UNIONing..

USE PUBS
GO
SELECT 'Sales Count' as Stat, COUNT(*) as Score
FROM  sales
UNION ALL
SELECT 'Title Count', COUNT(*)
FROM titles

Too easy...But a request comes to include a “Last Date” statistic...

SELECT 'Sales Count' as Stat, COUNT(*) as Score
FROM sales
UNION ALL
SELECT 'Last Published',  MAX(pubdate)
FROM titles

“So we sold '1900-01-22 00:00:00.000' titles?..Good work Dave.. You're fired!”

“But wait, I have a better idea..“

SELECT 'Sales Count' as Stat, CAST(COUNT(*) AS SQL_VARIANT) as Score
FROM sales
UNION ALL
SELECT 'Last Published', MAX(pubdate)
FROM titles

Print | posted on Wednesday, May 11, 2005 10:06 AM

Feedback

# re: SQL_Variant.. Finally a use for it...

left by Brett (Not just a Number...huh?) at 5/12/2005 12:52 AM Gravatar
Very cool...from a reporting perspective...but from a pure data perspective, I would imagine this would be problematic....

The Dr.: "This is a presentation issue"

Me personally, I've always converted everything varchar

# re: SQL_Variant.. Finally a use for it...

left by Jeff S at 5/12/2005 7:24 AM Gravatar
very interesting -- i had used sql_variant in my "custom aggregate functions" article a while back.

It's actually the opposite of a presentation issue here, though -- most presentation layers don't know what to do with a sql_variant! It's one thing if the column is a variant but all values are the same type, but when each row might be a different type, that is pretty crazy indeed!

# re: SQL_Variant.. Finally a use for it...

left by Bvalle at 5/5/2006 3:40 AM Gravatar
If you were to keep another ID on the table for the type of the variable that is store in the field, woundn't that fix the issue of the unknown? Right now I have a table Options that has the following format:
ID (numeric) | DataType (numeric) | Value (sql_var)

Now using the Datatype I know what Value contains. This way I can have numeric, string, date, etc... data in the same field.

What do you think?

# re: SQL_Variant.. Finally a use for it...

left by greg at 4/7/2009 12:14 AM Gravatar
a function run against the sql_variant will tell you the underlying type... so there is no need to persist this again in a seperate column

# re: SQL_Variant.. Finally a use for it...

left by junk yards at 7/25/2010 2:54 PM Gravatar
Once in the procedure you could use functions like IsNumeric or IsDate to test what type of parameter you had. And you could use the sql_variant_property function for more detailed information. This is very similar to passing in a parameter as a varchar. It's benefit is primarily one of precision. There won't be rounding errors when numeric or float values are passed in.
Comments have been closed on this topic.