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.