Performance issues using sql_variant to store data

I have a Key/Value table I'm using to store data that can be either text or numeric, but nothing else.

In preliminary testing, I am seeing horrendous performance when applying citeria to the sql_variant column, for example:

SELECT * FROM MY_DATA WHERE  
MY_ENTITY_TYPE = 555
AND CAST(MY_SQL_VARIANT_COLUMN AS NUMERIC) = 2254

So obviously there is going to be SOME hit with queries like this, but I am seeing queries well over 10 seconds, with only a few thousand rows in the table.

Considering I am only going to be storing numeric or text data, would it be more reasonable to use a varchar(255) column? This way, select performance should be fast, and I would just ha开发者_C百科ve to do a post select CAST to get the data into the proper data type.

---

Forgive me, but key/value table sounds like the uber-general DB many people try at some point or another (I did!) and it doesn't work well at all.

Are you sure you can't predict the keys and define a table where the keys are the columns and the rows are the values related together?

---

Here are two methods to solve this performance issue:

1. CAST the criteria value rather than the column: SELECT * FROM MY_DATA WHERE
   MY_ENTITY_TYPE = 555 AND MY_SQL_VARIANT_COLUMN = CAST(2254 AS NUMERIC)

2. Add an index to a different column in the table and see if it magically fixes the problem. For example, add an index to MY_ENTITY_TYPE, and despite not criteria being applied to this column, it cleared up the performance problem in my example.

Obviously, #1 is the better of the two.