MS SQL Server, indicating VARBINARY(MAX) field's datatype in VB.NET

views:

answers:

MS SQL Server, indicating VARBINARY(MAX) field's datatype in VB.NET

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?

+1 A:

How about a sql_variant ? See Using sql_variant Data.

Remus Rusanu 2010-10-13 17:13:18

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).

instantmusic 2010-10-13 17:27:43

You'd use the intrinsic `SQL_VARIANT_PROPERTY`: http://msdn.microsoft.com/en-us/library/ms178550.aspx to determine the type

Remus Rusanu 2010-10-13 17:30:00

sql_variant cannot store LOB types (so 'small' files would not work)

Remus Rusanu 2010-10-13 17:30:42

After going through your other comments, I'd recommend you read this whitepaper: http://sqlcat.com/whitepapers/archive/2008/09/03/best-practices-for-semantic-data-modeling-for-performance-and-scalability.aspx

Remus Rusanu 2010-10-13 17:33:12

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?

instantmusic 2010-10-13 18:07:07

I would consider adding a computed column `ALTER TABLE t ADD data_type AS SQL_VARIANT_PROPERTY(data, 'BaseType')` which would expose the type automatically as a new column.

Remus Rusanu 2010-10-13 18:24:30

Yea, I just noticed that you mentioned that above and I completely glazed over it. Sorry about that... excellent idea though. I think thats my solution. Thanks for your help guys!

instantmusic 2010-10-13 18:27:37

Also, about BLOBS: http://research.microsoft.com/pubs/64525/tr-2006-45.pdf

Remus Rusanu 2010-10-13 18:29:23

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.

Joe Stefanelli 2010-10-13 17:14:27

ansaurus

tags:

views:

answers:

MS SQL Server, indicating VARBINARY(MAX) field's datatype in VB.NET

related questions