sql_variant

  • 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
  • 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

  • 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
  • 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.

  • 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.

    😎

  • 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
  • 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.

  • 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)

  • 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

  • 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.

  • 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)

  • 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

  • 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)

  • 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.

  • 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.

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

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

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