0

I have a table with the following columns

Id - Int Primary key Identity column
Name - Varchar(100)
OrderValue - int
OrderDate - date 
OrderState - varchar(100)

The columns (Name, orderValue) have a unique key constraint and a unique non-clustered index on them.

We have a new requirement where the OrderValue column will start receiving strings, floats, guids. The two options we have right now are to change the datatype of orderValue to either Varchar(100) or to SQL_Variant. My teammates are in favor of sql_variant. Their reasoning is that since orderValue is part of the Unique Non-clustered index, changing it sql_variant will make sorting easy on the index keys within the index table since all the datatypes of the same type are stored together. I'm not well versed with Sql_variant and how the indexes are stored for sql_variants but I've read that sql_variant's performance is usually bad. In our case, what would be a good option? How do non-clustered indexes work when they have one of the columns as sql_variant?

joemac12
  • 113
  • 1
  • 7
  • I would probably leave the OrderValue as is and add/use an new/different field to store the string variant as reference. – alexherm Oct 21 '21 at 23:06
  • 5
    SQL is strongly typed and just because you *can*, doesn't mean you *should*. A variant can be indexed but the size is limited (900b), exceed that and insert would error. I'd add columns for each specific data type. – Stu Oct 21 '21 at 23:08
  • Thanks for the reply guys. We are not expecting more than 900b at any point and we want all the values to be in the Ordervalue. We can't create new columns for each data type since we are expecting more data types in the future. From a performance standpoint would it make sense to convert this column to sql_variant or varchar? – joemac12 Oct 21 '21 at 23:21
  • 4
    How many more data types could be possible? EAV is far less evil than sql_variant IMHO. https://sqlblog.org/2009/11/19/what-is-so-bad-about-eav-anyway – Aaron Bertrand Oct 21 '21 at 23:44
  • ^^^ agreed..... – Dale K Oct 21 '21 at 23:57
  • 5
    Also to continue bashing on sql_variant see [this](https://stackoverflow.com/q/25243028/61305) and [this](https://stackoverflow.com/q/9039455/61305) and [this](//sqlblog.org/blogs/aaron_bertrand/archive/2009/10/12/bad-habits-to-kick-using-the-wrong-data-type.aspx) and [this](https://stackoverflow.com/q/13823130/61305) and [this](https://stackoverflow.com/q/17542566/61305) and [this](https://www.brentozar.com/archive/2017/03/no-seriously-dont-use-sql_variant/). With so many people warning against this data type, what could possibly go wrong? It's kind of always been the "gas station sushi" – Aaron Bertrand Oct 21 '21 at 23:59
  • ...of SQL Server data types. – Aaron Bertrand Oct 22 '21 at 00:02
  • Thank you so much for all the replies! Much appreciated – joemac12 Oct 22 '21 at 13:11

1 Answers1

-1

I have never used this data type myself.

Update:

Found an article that demonstrates a way to use SQL_VARIANT data type: https://aboutsqlserver.com/2012/02/22/store-custom-fieldsattributes-in-microsoft-sql-server-database-part-2-namevalue-pairs/?unapproved=201416&moderation-hash=771c41a02ff9a7c909e93140a8795e3a#comment-201416

Ordering

From reading documentation, specifically value comparison part I can tell that there will be cases where ordering will not look "natural" e.g.

CREATE TABLE #Test( a SQL_VARIANT )
INSERT INTO #Test VALUES( 2 ) -- INT
INSERT INTO #Test VALUES( 2.1 ) -- DECIMAL
INSERT INTO #Test VALUES( '3' ) -- VARCHAR
INSERT INTO #Test VALUES( CAST( 1.8 AS FLOAT ) )
INSERT INTO #Test VALUES( DATEFROMPARTS( 2020, 1, 1 ) )
SELECT *, SQL_VARIANT_PROPERTY ( a , 'BaseType' ) FROM #Test ORDER BY a
DROP TABLE #Test

Results (sorted by col a in ascending order):

a                          
-------------------------- -----------
3                          varchar
2                          int
2.1                        numeric
1.8                        float
2020-01-01 00:00:00.000    date

Index Performance

I don't think there will be any performance issues with index seeks. Inserts and Updates will likely take a penalty

Gotchas

There will be a lot of little gotchas when working with this data type. Some examples are below:

1| WHERE conditions will not match unless data types match:

-- Returns nothing
SELECT *, SQL_VARIANT_PROPERTY ( a , 'BaseType' ), SQL_VARIANT_PROPERTY ( a , 'TotalBytes' )
FROM #Test
WHERE a = '2'
ORDER BY a

-- Returns nothing
SELECT *, SQL_VARIANT_PROPERTY ( a , 'BaseType' ), SQL_VARIANT_PROPERTY ( a , 'TotalBytes' )
FROM VariantTest
WHERE a = 3
ORDER BY a

2| This will not insert data with correct data types as data types will first be implicitly cast to VARCHAR before being cast to SQL_VARIANT:

INSERT INTO #Test VALUES( 2 ), ( 2.1 ), ( '3' ), CAST( 1.8 AS FLOAT )
Alex
  • 4,885
  • 3
  • 19
  • 39