0

So, I have this scenario where I use a VARBINARY(MAX) column, because I have no idea what is going to be put into it. All I know is that as of this point in time, the data will be one of many traditional data types(Integer, String, DateTime, etc.)

I also have another column that is meant to indicate the DataType so the .NET application can handle the data and validate input accordingly.

Right now, for testing, I have a DataType table in my database that stores the "supported" datatypes for the VARBINARY column, and a foreign key linking to the column meant to indicate the DataType. This works perfectly, but it feels akward.

Given this scenario, what would some of you to to appropriately represent the type of data stored in the VARBINARY column?

Improve this question
2
  • Couldn't you store all of your "traditional" datatypes just as easily in a VARCHAR(MAX) or NVARCHAR(MAX)? Commented Oct 13, 2010 at 17:05
  • At this present point in time, yes. That would suffice. However theres a degree of functionality I'll be adding in the application later that performs some intense operations depending on the actual type of the value. Scheduling(if date time), Cost calculations(If Money,Small Money, or applicable type). There will also be some instances where the value will indicate volume, distance, weight. There will be a degree of input validation based on the purpose of the value and its datatype. ...I suppose that would have been handy info in the original question would it? Srry... Commented Oct 13, 2010 at 17:22

2 Answers 2

Reset to default
1

How about a sql_variant ? See Using sql_variant Data.

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

7 Comments

That would work for storing the majority of the data. I assume I'd still have the issue with determining the type of data stored in the VB.NET application(without explicitly testing the type). I've been instructed to consider the possibility of storing small files in the field as well(again, I probably should have mentioned that in the initial post).
You'd use the intrinsic SQL_VARIANT_PROPERTY: msdn.microsoft.com/en-us/library/ms178550.aspx to determine the type
sql_variant cannot store LOB types (so 'small' files would not work)
After going through your other comments, I'd recommend you read this whitepaper: sqlcat.com/whitepapers/archive/2008/09/03/…
Thanks, that was a good read. I'm thinking that sql_variant might be the better option. I'll try to eliminate the idea of storing files at all in the column and develop a completely different solution for that requirement. I'm thinking of representing the DataType in an nvarchar column and creating an enumeration in the application to make checking against it easier(or I might just use System.Data.SqlDbType with an integer column). Whats your opinion on it?
|
0

Similar situation: In our EAV implementation, we created four separate columns in our "Value" table: a numeric, a short string (NVARCHAR(255)), a long string (NVARCHAR(MAX)) and a datetime. Then in the "Attribute" table we have a column indicating the datatype of the attribute. The separation helps us avoid awkward datatype conversions in our queries and allows us to improve our indexing by taking advantage of filtered indexes.

Improve this answer

Comments

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.