Post reply

Columns to store different data types

  • December 13, 2007 at 3:39 am

    #184819

    Hi

    I need to create a table whose columns will store char,varchar,nchar,nvarchar,int and datetime data types.

    Ex: Table1 - CustomerID INT ,Column1 SQL_Variant, Column2 SQL_Variant.

    In Column1 and Column2 data could be any of these types - char, varchar, nchar, nvarchar,int or datetime. Iam planning to use SQL_Variant as the data type for the columns (Column1 and Column2). Any alternate suggestions or anything else that i need to consider ?

    Thanks

    "Keep Trying"

  • December 13, 2007 at 6:09 am

    #759783

    Indexing for data retrieval would be my biggest concern. Why would you store... anything, in a column like that?

    Personally, I'd probably look to storing the stuff as XML instead of using the variant type. At least it offers XQuery and some indexing methods.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • December 13, 2007 at 6:23 am

    #759792

    hi

    thanks for info Grant.

    my clustered index is CustomerId and i believe most if not all queries would be based on customerID.

    This is a case of customisable fields which can be customised by the users. One customerID may be storing date data in the columns where as another customerid would have varchar data.

    "Keep Trying"

  • December 13, 2007 at 7:03 am

    #759810

    Any alternate suggestions or anything else that i need to consider ?

    Yes... don't forget that SQL_Variant can take up to 8016 bytes... if you have more than one SQL_Variant column in your table, you will get warnings about you exceeding the 8060 limit of a row. If some data actually exceeds the limit, BOOM!

    I believe that if such a table is actually necessary, I'd create a "name/value" table and I'd be really careful about exceeding the 8060 limit. You might have to make a "sister" table with a 1:1 relationship.

    --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)
    Intro to Tally Tables and Functions

  • December 13, 2007 at 11:05 pm

    #760085

    Thanks Jeff.

    Named value pair was what i had suggested but due to reasons it was not acceptable. Querying the data then showing rows as columns being the main reason.

    As it is i dont see the data going above teh 8060 kb limit.

    Is SQL_Variant better than say NVARCHAR ?

    "Keep Trying"

  • December 14, 2007 at 8:22 am

    #760279

    Querying the data then showing rows as columns being the main reason.

    But, that's so easy. Heh, guess ya gotta do what ya gotta do.

    SQL_Variant will take just about anything you can throw at it except for text/image and, I think, Binary datatypes (double check BOL to make sure). If ya gotta do it this way, SQL_Variant is probably the way to go...

    --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)
    Intro to Tally Tables and Functions

  • December 16, 2007 at 10:55 pm

    #760612

    Thanks jeff

    "Keep Trying"

  • December 20, 2007 at 7:32 pm

    #761967

    Use caution when deciding to use the SQL Variant data type. It's one of those things that can be easy to implement but may come back to bite you in the backside.

    With the information provided, I would recommend using NVARCHAR instead of the SQL Variant type. In my opinion, the latter data type is about as safe as storing groceries and gasoline in the same container.

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • December 20, 2007 at 11:55 pm

    #761984

    thanks

    "Keep Trying"

Viewing 9 posts - 1 through 8 (of 8 total)

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