Columns to store different data types

Question

Columns to store different data types

ChiragNS

One Orange Chip

Points: 26137
More actions
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"

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

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

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

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 Roosevelt

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

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"

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 3

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)

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

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"

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 5

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)

ChiragNS One Orange Chip Points: 26137 More actions · Answer 6

ChiragNS

One Orange Chip

Points: 26137

December 16, 2007 at 10:55 pm

#760612

Thanks jeff

"Keep Trying"

Tim Mitchell SSCoach Points: 15685 More actions · Answer 7

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
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

ChiragNS One Orange Chip Points: 26137 More actions · Answer 8

ChiragNS

One Orange Chip

Points: 26137

December 20, 2007 at 11:55 pm

#761984

thanks

"Keep Trying"