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

c#, performance, sql-server-2012, sql-variant, varchar

Solution

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:

- not very fast

- not well supported by ORM frameworks

Problem

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.

Original source