SQL Server 2005，宽索引，计算列和可查询的查询
在我的数据库中，假设我们有一个定义如下的表格：
CREATE TABLE [Chemical](
[ChemicalId] int NOT NULL IDENTITY(1,1) PRIMARY KEY,
[Name] nvarchar(max) NOT NULL,
[Description] nvarchar(max) NULL
)
Name的值可能非常大，所以我们必须使用nvarchar（max）。 不幸的是，我们想在这个列上创建一个索引，但是在索引内不支持nvarchar（max）。
所以我们根据它创建以下计算列和关联索引：
ALTER TABLE [Chemical]
ADD [Name_Indexable] AS LEFT([Name], 20)
CREATE INDEX [IX_Name]
ON [Chemical]([Name_Indexable])
INCLUDE([Name])
该索引不会是唯一的，但我们可以通过触发器强制执行唯一性。
如果我们执行下面的查询，执行计划会产生索引扫描，这不是我们想要的：
SELECT [ChemicalId], [Name], [Description]
FROM [Chemical]
WHERE [Name]='[1,1''-Bicyclohexyl]-2-carboxylic acid, 4'',5-dihydroxy-2'',3-dimethyl-5'',6-bis[(1-oxo-2-propen-1-yl)oxy]-, methyl ester'
但是，如果我们修改查询以使其“可变sargable”，那么执行计划将导致索引查找，这是我们想要的：
SELECT [ChemicalId], [Name], [Description]
FROM [Chemical]
WHERE [Indexable_Name]='[1,1''-Bicyclohexyl]-' AND [Name]='[1,1''-Bicyclohexyl]-2-carboxylic acid, 4'',5-dihydroxy-2'',3-dimethyl-5'',6-bis[(1-oxo-2-propen-1-yl)oxy]-, methyl ester'
如果我们控制通过中间层对数据库执行的所有查询的格式，这是一个很好的解决方案吗？ 有没有更好的办法？ 这是一个主要的混乱？ 我们应该使用全文索引吗？
恕我直言，是的，我认为这是一个不好的方法。 如果您知道前20个字符是唯一的，那么它应该是具有唯一约束的头等列。 如果您希望在名称列上进行更好的搜索，那么使用全文搜索是正确的选择。 如果您想确保varchar（max）列是唯一的，那么创建一个计算列，该列会生成一个散列值并将一个唯一约束关闭。
Alter Table Add NameHash Hashbytes('SHA1', [Name])
加成
根据我们的讨论，如果您的搜索总是完全匹配，那么您可以散列您的搜索参数并将其与上面的NameHash进行比较。 然而，问题在于比赛必须完全匹配（即区分大小写）。
我仍然认为FTS将是你最好的选择。 即使在将文本分解为文字方面存在开销，FTS也是最适合对大量文本进行搜索的工具。 搜索条件越长，搜索条件越精确，搜索速度越快。
您的索引位于name_indexable ，而不是name 。 由于name_indexable是从涉及name的函数生成的，而不是直接在列name ，所以当where子句包含对name的引用时，优化器不会自动使用索引。 您必须搜索name_indexable才能使用索引。 既然你有一个中间层，最好的办法就是提供一个函数，在name_indexable上搜索，如果给定的名字<= 200个字符，否则搜索。
使Name_Index列成为持久计算列和主键，并通过追加ChemicalId而不是依赖触发器来强制执行唯一性。
CREATE TABLE dbo.[Chemical]
([ChemicalId] int NOT NULL IDENTITY(1,1),
[Name] Nvarchar(max) NOT NULL,
[Description] Nvarchar(max) NOT NULL,
[Name_Index] AS (CONVERT(VARCHAR(20), LEFT([Name], 20)) + CONVERT(VARCHAR(20), [ChemicalId])) PERSISTED PRIMARY KEY);
上一篇: SQL Server 2005, wide indexes, computed columns, and sargable queries