Should I use SQL_Variant data type?

Question

using SQL Server 2005 with SP4 and I am designing a database table.

Here is the table DDL

CREATE TABLE CPSync4D.ProjectProfilerOption (     ProjectProfilerOptionID     INT  IDENTITY(1,1) CONSTRAINT PK_ProjectProfilerOption_ProjectProfilerOptionID PRIMARY KEY     ,ProjectID                   INT  CONSTRAINT FK_ProjectProfilerOption_Project_ProjectID FOREIGN KEY(ProjectID) REFERENCES CPSync4D.Project(ProjectID) ON DELETE CASCADE    ,ProfilerOptionID            TINYINT CONSTRAINT FK_ProjectProfilerOption_ProfilerOption_ProfilerOptionID  FOREIGN KEY(ProfilerOptionID) REFERENCES CPSync4D.ProfilerOption (ProfilerOptionID)     ,ProfilerOptionValue         sql_variant  NOT NULL     ) Go 

profileroptionvalue column can hold either a string upto 30 characters, integer or decimal values e.g. values are "ProfilerValueType", or 12.52 or 20 etc. (no more than than two decimals and integer values are less than 100)

Should I use sql_variant or varchar(30)...? I never used sql_variant before and not sure any implication of not using in terms of database design.

Any pitfalls of using sql_variant...with .net code

Answer 1

10 reasons to explicitly convert SQL Server data types

As a general rule, you should avoid using SQL Server’s sql_variant data type. Besides being a memory hog, sql_variant is limited:

• Variants can’t be part of a primary or foreign key. (this doesn't hold as of SQL Server 2005. See update below)
• Variants can’t be part of a computed column.
• Variants won’t work with LIKE in a WHERE clause.
• OLE DB and ODBC providers automatically convert variants to nvarchar(4000) — ouch!

To avoid problems, always explicitly convert sql_variant data types as you use them. Use any method you please, just don’t try to work with an unconverted sql_variant data type.

I haven't used sql_variant before but with these restrictions and performance implications in mind, I would first look at alternatives.

Following would be my most to least prefered solution

• Simply create three different columns. 3 Different data types (should) mean 3 different ways of interpreting it both at the client side and server side.
• If that is not an option, use a VARCHAR column so you can at least use LIKE statements.
• Use the sql_variant data type.

Edit Cudo's to ta.speot.is

Variants can be part of a primary of foreign key

A unique, primary, or foreign key may include columns of type sql_variant, but the total length of the data values that make up the key of a specific row should not be more than the maximum length of an index. This is 900 bytes

Answer 2

I know my answer is a bit late but the table being made here looks a bit like an application configuration table. As an alternative to the suggestions given, let's think about not limiting ourselves to 30 or even 8000 characters. Let's also make it a bit more self contained and user definable.

With those thoughts in mind, why not save the "profile" information as an XML data type which would even allow multiple levels of settings? You probably wouldn't need such columns as ProfilerOptionID anymore and might be able to get this down to one simple control table.