SQL_Variant.. Finally a use for it…

Wed May 11, 2005 by byrmol

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

Legacy Comments


Brett (Not just a Number...huh?) 2005-05-12	re: SQL_Variant.. Finally a use for it... 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

Jeff S 2005-05-12	re: SQL_Variant.. Finally a use for it... 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!

Bvalle 2006-05-05	re: SQL_Variant.. Finally a use for it... 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?

greg 2009-04-07	re: SQL_Variant.. Finally a use for it... 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

junk yards 2010-07-25	re: SQL_Variant.. Finally a use for it... 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.