How does a non-clustered index with SQL Variant as one of the columns work?-CodePudding

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?

CodePudding user response：

I have never used this data type myself.

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 )