7

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!

Improve this question
1
  • 2
    If you are going down the EAV route, I suggest you do some reading as to why this is a bad data structure in a relational database. see:simple-talk.com/opinion/opinion-pieces/bad-carma If you really need to do this, you might consider a nosql solution instead. Commented Oct 7, 2010 at 14:30

5 Answers 5

Reset to default
11

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I don't like much the varchar solution, but I'm definitely going to try the sql_variant and test the conversion performance hit. If it is acceptable, than that's going be the way it is! Thank you for your suggestion.
1

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.

Improve this answer

2 Comments

The solution seems fine, but wouldn't the serialized string or Xml be a lot bigger (in bytes) than base data itself?
@Guilherme, yes, which is why i said 'I would point out that "serializing it" will bloat the data' But if you have no other way of determining the actual data type, this can be a fall-back.
1

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.

Improve this answer

2 Comments

Thanks for the suggestion. but I was avoiding the varchar (nvarchar) solution because of database growth and conversions performance hit.
ha, your selected answer: "sql_variant" how do you think that will perform better?
1

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.

Improve this answer

1 Comment

In fact that's precisely my current solution. But I was trying to enhance the table crossings, and select queries, by having a single "values" table. If there's is no other choice, that's the way it's going to stay.
0

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.

Improve this answer

4 Comments

Thanks for the suggestion. but I was avoiding the varchar (nvarchar) solution because of database growth and conversions performance hit.
You can't have your cake and eat it too; if you want to store different types in the same column, what other type would you use? String/varchar is the only type that can represent all the other types (except binary, or course).
**Note: I just read down about the sql_varient type and that seems like a viable solution, but I wonder how .NET will react to casting the objects in that column to the type that you desire.
Well in fact my .NET solution already stores the value in an object property, so probably there's not going to be much castings.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.