Should I use SQL_Variant data type?

问题:

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

回答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

回答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.

回答3:

Its worth noting that it is not possible to copy the sql_variant column implicitly.

e.g. Create a backup schema of CPSync4D.ProjectProfilerOption called CPSync4D.ProjectProfilerOption_bkp

and then

Insert into CPSync4D.ProjectProfilerOption_bkp
(
    ProjectProfilerOptionID
   ,ProjectID
   ,ProfilerOptionID
   ,ProfilerOptionValue 
)
SELECT 
    ProjectProfilerOptionID
   ,ProjectID
   ,ProfilerOptionID
   ,ProfilerOptionValue 
FROM CPSync4D.ProjectProfilerOption 

All of the values for ProfilerOptionValue in the backup table will be varchar

Note also: I have been told that the SQL_Variant cannot be used in replication but this is not true. Certainly it can be done with SQL 2008 R2 (that I am using) because I have just done it but this may have been true for older versions (I don't have any older versions to check with so cannot confirm or deny this).

What is true though, is that if you do replicate a table with a SQL Variant in it and have a lot of data and then something goes wrong and you need to fix the data manually, then you might have a nasty piece of SQL to write. This is because when copying the data you cannot copy with several base types in the same copy statement. I guess that replication doesn't have this issue because it does not copy multiple rows (with the obvious exception of the snapshot but that uses bcp).

ps. I realise this is an old post but put this here for other future visitors with the same question.