Storing unknown datatype in MS SQL Server Database

Question

Hi,

Does any one has an idea of whats the best practice to store an unknown data type in a table. Basically I would need to store types like bit, smallint, int, real and nvarchar in the same "value" column, for later interpretation by a .NET application.

I was trying to achieve the best possible solution not to compromise the performance or growth of the database.

Any suggestions?

Thanks in advance!

Answer 1

Store it as a varchar (or nvarchar). This will handle all the types. You will have to determine what length to make it based on what you know about your data.

Answer 2

As I see it, you only have two options:

You're object will have to be saved as some kind of string. That string could be a raw string or XML. If you "serialize" it as XML ans store it in the database, again, you can choose nvarchar or XML. I would point out that "serializing it" will bloat the data. If you can reasonably determine the data type based on another column that you may be pulling at the same time, then I would suggest just putting it in as a string.

Answer 3

try this:

YourTableName
...more columns...
ColType char(1)       --B=bit, S=smallint, I=int, R=real, n=nvarchar, you can FK to a table to store these or just check constraint them
Col     nvarchar(x)   --where x is large enought to hold your longest string or other value
...more columns...

in the .Net app, read in ColType and convert the Col column to that type. When saving, SQL Server will convert the native data types to the nvarchar() for you.

Answer 4

Varchar(max) is probably the easiest way to do this.

sql_variant was designed for this purpose so you could use it, but read the Books Online entry to ensure it's going to do what you want.

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

Answer 5

I'm not sure what you mean by 'unknown type'. If you mean that a value is exactly one of a limited number of possible types then I would use a single-column -- strongly typed -- table for each possible type and a 'superclass' table to tell you which table to look in.