sqlsql-servert-sqlssismsbi

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


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?


Solution

  • 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 )