sql_variant

Question

sql_variant

Christopher Stobbs

SSC-Insane

Points: 21098
More actions
July 8, 2008 at 8:49 am

#189225

Hi All,
I still can't seem to find a use or reason for "sql_variant" datatype.
Surely you should always use the specific datatype?
Also won't a sql_variant take up more space than a normal datatype?
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
SQL-4-Life

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

Honestly, I think it's a crutch. I haven't seen a well-planned design use it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

Thanks Grant.

Thats what I thought.

If anyone has found a reason to use I'd be interested know?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

The only semi-good use of it I have ever seen was in a table that was for user-defined fields. The table included a "field type" and the data allowing the user to define the actual data type. It could have been a VARCHAR and it would have worked just as well.

I don't remember ever having used it myself.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

I have used sql_variant in a database I was using for data analysis of data stored in a database. The only thing I couldn't store was blob data, or text data greater than 8000 bytes. Worked quite well analyzing data for a data warehouse project I was involved in at my previous employer.

I could use it here, but just haven't found the time to recreate it.

😎

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

Thanks all for your posts 🙂

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

qtsohg SSC-Addicted Points: 443 More actions · Answer 6

Just my quick 2 cents worth.

If you have to capture chagnes in the database on multiple Columns (and hence Column types) in one table, it would come in handy.

I have used it in the past to capture bugs and users deleting, updating and modifying all kinds of table, by creating triggers that simply write which column has been update with which data. So to make sure you can use one column for all the types you use the variant field.

I do however belive it should be avoided if possible.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

There are three reasons I can see for it... and I use neither because of what Grant said... would probably be a bad design...

Someone already said to build "user defined 'Fields'"... that would be in an EAV (Entity Attribute Value) or NVP (Name Value Pair) table. Most consider that to be a bad design but does allow for users to build "user defined 'Fields'".

The other thing it could be used for is a "universal" audit log table (would also be an EAV table) that would need to handle every data type possible without conversions. The problem with that is that it takes up a full 8060 bytes... doesn't leave an room for the "EA" of the "EAV" table.

Last but not least is when build a "universal" proc that accepts just about any parameter. Proc could say that if it's a date, do one thing, if it's something else, do another, etc, etc.

Again, like Grant said, those normally entail a bad design of one form or another. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

noeld SSC Guru Points: 96590 More actions · Answer 8

Some functions return sql_variant datatype like SESSIONPROPERTY and you many not want to perform conversions on the output so you use sql_variant to store the results.

* Noel

Steve Mong SSC Rookie Points: 45 More actions · Answer 9

Certain classes of apps can't anticipate the data types they'll be required to store, manipulate, etc.

Master Data Management apps have always been prominent examples (wherein Oracle's product uses its own version of sql_variant). Microsoft's CRM product is another case in point...they elected to go with XML 'property bags', but that has its own strengths and weaknesses. The challenge is basically the same, and variants offered the way out.

Further, I believe this challenge is shaping up to be a central modeling issue for the future of IT: highly configurable SaaS offerings. In a nutshell, experience with variant data types will grow to be a necessity for archs, modelers and developers.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

Steve Mong (3/13/2009)
Certain classes of apps can't anticipate the data types they'll be required to store, manipulate, etc.
Master Data Management apps have always been prominent examples (wherein Oracle's product uses its own version of sql_variant). Microsoft's CRM product is another case in point...they elected to go with XML 'property bags', but that has its own strengths and weaknesses. The challenge is basically the same, and variants offered the way out.
Further, I believe this challenge is shaping up to be a central modeling issue for the future of IT: highly configurable SaaS offerings. In a nutshell, experience with variant data types will grow to be a necessity for archs, modelers and developers.

Heh... yep... just like the folks that use VARCHAR(MAX) for everything... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 11

Hi

I'm using sql_variant alike Jeff stated for logging. I've got a usp_Log like this:

CREATE PROCEDURE dbo.usp_Log

@msg VARCHAR(4000),

@p1 SQL_VARIANT = NULL,

@p2 SQL_VARIANT = NULL,

@p3 SQL_VARIANT = NULL,

@p4 SQL_VARIANT = NULL,

@p5 SQL_VARIANT = NULL,

@p6 SQL_VARIANT = NULL,

@p7 SQL_VARIANT = NULL,

@p8 SQL_VARIANT = NULL,

@p9 SQL_VARIANT = NULL

AS

-- ...

So I can specify 0 to 9 place holders (%1 to %9) to be replaced with any information.

Greets

Flo

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

Florian Reischl (3/14/2009)
Hi
I'm using sql_variant alike Jeff stated for logging. I've got a usp_Log like this:
CREATE PROCEDURE dbo.usp_Log
@msg VARCHAR(4000),
@p1 SQL_VARIANT = NULL,
@p2 SQL_VARIANT = NULL,
@p3 SQL_VARIANT = NULL,
@p4 SQL_VARIANT = NULL,
@p5 SQL_VARIANT = NULL,
@p6 SQL_VARIANT = NULL,
@p7 SQL_VARIANT = NULL,
@p8 SQL_VARIANT = NULL,
@p9 SQL_VARIANT = NULL
AS
-- ...
So I can specify 0 to 9 place holders (%1 to %9) to be replaced with any information.
Greets
Flo

You've probably hit on one of the only places I've ever used SQL_Variant... logging in EAV's/NVP's.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bill Talada SSChampion Points: 11954 More actions · Answer 13

I think the main reason for sql_variants has been missed here and are therefore being bashed unfairly. Their purpose is to allow different data types to be stored in a column so each row potentially has a different data type for that column. This is useful for allowing end users to decide what to store in "their" column on a per row basis. Think of this as intentional late binding...which always adds flexibility and power at the cost of performance. The SQL_VARIANT_PROPERTY function will tell you the type a user has used for a given row.

MMartin1 One Orange Chip Points: 27795 More actions · Answer 14

The author of this article makes use of the sql variant. When capturing configuration baseline data one column is the configuration name and another the value. Of course the configurations are not all the same data type.

http://www.sqlservercentral.com/articles/baselines/94656/

I think it is a valid use.

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