SQL Variant作为列之一的非聚集索引如何工作?

Question

我有一个包含以下列的表

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

列(Name, orderValue)有一个唯一的键约束和一个唯一的非聚集索引。

我们有一个新的需求，其中OrderValue列将开始接收字符串，浮点数，指针。我们现在有两个选项，将orderValue的数据类型更改为Varchar(100)或SQL_Variant。我的队友支持sql_variant。他们的理由是，由于orderValue是唯一非聚集索引的一部分，将其更改为sql_variant将使索引表内索引键的排序变得容易，因为相同类型的所有数据类型都存储在一起。我不是很精通Sql_variant以及如何为Sql_variant存储索引，但我读过Sql_variant的性能通常很差。在我们的情况下，什么是一个好的选择?当其中一列为sql_variant时，非聚集索引如何工作?

Answer 1

我自己从来没有使用过这种数据类型。

更新:

找到一篇演示如何使用SQL_VARIANT数据类型的文章: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

命令

通过阅读文档，特别是值比较部分，我可以看出有些情况下排序看起来不"自然"。例如

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

结果(按a升序排序):

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

<<h2>索引性能/h2>我认为索引查找不会有任何性能问题。插入和更新可能会受到惩罚

陷阱

在使用这种数据类型时会有很多小问题。下面是一些例子:

1|WHERE条件将不匹配，除非数据类型匹配:

-- 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|这将不会插入具有正确数据类型的数据，因为数据类型将首先隐式转换为VARCHAR，然后再转换为SQL_VARIANT:

INSERT INTO #Test VALUES( 2 ), ( 2.1 ), ( '3' ), CAST( 1.8 AS FLOAT )