SQLServer 2008 generic column type

Question

I've a table on SqlServer where a column type should be undefined, I mean, it should be able to contain any kind of type (string, int, boolean and datetime).

This because my users should be able to define which type the column is for each record. In the past when I encountered this problem I decided to create it as nvachar(MAX). When I accessed the value I automatically casted the string to the correct type. I know that this method wastes a lot of memory and has a performance implications.

Maybe is possible to create a different table for each type but I never had the time to test that.

Do you have any thoughts?

Thanks!

Answer 1

Database designs, where variant type of data is stored into columns will almost always come back and be a major issue later on. The quality of data almost always suffers.

I have seen this lead to non-atomic data (comma separated values), badly formed date values in string fields, multi-use of a single column and a host of other problems.

I would suggest that you look into what it is that they want to store and make the appropriate database design with properly normalized tables.

Having ranted (and thank you for listening), you can use SQL_Variant for your purposes. Read more here:

http://msdn.microsoft.com/en-us/library/ms173829.aspx