We use sql_variant data type for value in EAV, but I think it's better that we use one table for each data type value because sql_variant is 8000 byte and I'm worried about this data type for huge value.
What's your idea? Which them is better?
ThanX
I realize this question is quite old but I thought I'd answer it anyway.
SQL_VARIANT does NOT use 8000 bytes for every row. It uses the same number of bytes as the "base data type" + up to 16 bytes to store the meta-data for the entry. It works fine for most EAV's and has the added advantage of preserving the original datatype. It's also pretty fast especially on inserts because you don't have to convert to VARCHAR.
The real problem isn't whether or not to use SQL_Variant in an EAV... the real problem may be the reason why you're using an EAV to begin with. ;-)
