Are there any benefits to using sql_variant over varchar in SQL Server?

Question

I currently have a database table setup as follows (EAV - business reasons are valid):

• Id - int (PK)
• Key - unique, varchar(15)
• Value - varchar(1000)

This allows me to add in mixed values into my databse as key/value pairs. For example:

1   | 'Some Text'      | 'Hello World'
2   | 'Some Number'    | '123456'
etc.

In my C# code I use ADO.Net using reader.GetString(2); to retrieve the value as a string, then have my code elsewhere convert it as needed, for example... Int32.ParseInt(myObj.Value);. I'm looking at enhancing my table by possibly changing the value column to a sql_variant datatype, but I don't know what the benefit of this would be? Basically, is there any advantage to having my value column be of sql_variant vs varchar(1000)?

---

To be more clear, I read somewhere that sql_variant gets returned as nvarchar(4000) back to the client making the call (ouch)! But, couldn't I cast it to it's type before returning it? Obviously my code would have to be adjusted to store the value as an object instead of a string value. I guess, what are the advantages/disadvantages of using sql_variant versus some other type in my current situation? Oh, and it is worth mentioning that all I plan to store are datetimes, strings, and numerical types (int, decimal, etc) in the value column; I don't plan on storing and blob or images or etc.

Answer 1

The good thing about sql variant is that you can store several types in a column and you keep the type information.

Insert into MySettings values ('Name','MyName'); Insert into MySettings values ('ShouesNumber',45); Insert into MySettings values ('MyDouble',31.32);

If you want to retrieve the type:

select SQL_VARIANT_PROPERTY ( value , 'BaseType' ) as DataType,* from mysettings

and you have:

Datatype Name          Value
-----------------------------
varchar  Name          MyName
int      ShoesNumber   45
numeric  MyDouble      31.32

Unfortunately this has several drawbacks:

1. not very fast
2. not well supported by ORM frameworks

Answer 2

If you change the type to sql_variant, you will have to use the IDataRecord.GetValue method. It will preserve the type all the way.

So in .NET it will allow you to have this kind of code:

// read an object of SQL underlying data type 'int' stored in an sql_variant column
object o = myReader.GetValue(); // o.GetType() will be System.Int32

// read an object of SQL underlying data type '(n)varchar' or '(n)char' stored in an sql_variant column
object o = myReader.GetValue(); // o.GetType() will be System.String

// read an object of SQL underlying data type 'datetime' stored in an sql_variant column
object o = myReader.GetValue(); // o.GetType() will be System.DateTime

etc...

Of course, it supposes you do the same when saving. Just set SqlParameter.Value to the opaque value, don't use the DbType.

EAV with various (standard) types as value is the one case where I personally think sql_variant is interesting.

Of course "SQLServer-focused guys" (read: DBAs) don't like it at all :-) On the SQL Server side, sql_variant is not very practical to use (as noted in the comments), but if you keep it as an opaque "thing" and don't have to use it in SQL procedure code, I think it's ok. So, it's more an advantage on the .NET/OO programming side.