Saving unknown data type in MS SQL Server database

Question

Does anyone have any idea that it is best to store an unknown data type in a table. Basically, I will need to store types such as bit, smallint, int, real and nvarchar, in one column of "value", for subsequent interpretation of the .NET application.

I tried to achieve the best possible solution so as not to degrade the performance or growth of the database.

Any suggestions?

Thanks in advance!

Answer 1

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

sql_variant was designed for this purpose, so you can use it, but read the entry "Books on the Internet" to make sure that it will do what you want.

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

Answer 2

As I can see, you have only two options:

You object should be saved as some string. This string can be a source string or XML. If you "serialize" it as XML ans, save it in the database, you can also choose nvarchar or XML . I would like to point out that "serialization" will inflate data. If you can intelligently determine the data type based on another column that you can pull out at the same time, I would suggest just inserting it into a row.

Answer 3

I'm not sure what you mean by "unknown type". If you mean that the value is exactly one of a limited number of possible types, I would use a table with one column — strongly typed for each possible type and a “superclass” table to tell you which table to look for.

Answer 4

Save it as varchar (or nvarchar ), This will handle all types. You will need to determine what length to do based on what you know about your data.

Answer 5

try the following:

 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 a .Net application, read in ColType and convert the Col column to this type. When you save, SQL Server will convert your own data types to nvarchar () for you.