Sql Server:索引列上的较低功能 [英] Sql Server : Lower function on Indexed Column

问题描述

我发现了一个大问题.

我已将下层函数添加到表之一的索引列以获取数据.该表包含超过 10 万条记录.

I have added the Lower function to indexed column of one of the table to fetch the data. The table contains more than 100K records.

在获取记录时，cpu 使用率达到 100%.

While fetching the records, the cpu usage goes to 100%.

我无法理解，怎么会因为Lower()函数而发生如此剧烈的变化.

I could not understand, how this much drastic change can happen just because of Lower() function.

请帮忙！

推荐答案

如果您真的非常需要此查询，您可以做的是创建一个使用 LOWER() 函数的持久计算列.索引该列，您应该又好了:

What you could do, if you really need this query a lot, is create a persisted computed column that uses the LOWER() function. Index that column and you should be fine again:

ALTER TABLE dbo.YourTableName
  ADD LowerFieldName AS LOWER(YourFieldName) PERSISTED

CREATE NONCLUSTERED INDEX IX_YourTableName_LowerFieldName
  ON dbo.YourTableName(YourFieldName)

这将在您的表中保留您的字段的小写表示，它始终是最新的，并且由于它被持久化，它是您的表的一部分并且不会招致 LOWER() 函数的惩罚.为其添加索引，您的搜索应该和以前一样快.

That would keep a lower-case representation of your field in your table, it's always up to date, and since it's persisted, it's part of your table and doesn't incur the penalty of the LOWER() function. Put an index on it and your search should be as fast as it was before.

链接:

• 有关 SQL Server 2008 计算列的 MSDN 文档
• 复杂计算列
• 在具有持久值的 SQL Server 中使用计算列
• SQL Server 2005 中的 10 大隐藏宝物 - 持久化计算列是第 3 项
• SQL Server 计算列
• 使用计算列

• MSDN docs on SQL Server 2008 computed columns
• Complex Computed Columns
• Using Computed Columns in SQL Server with Persisted Values
• Top 10 Hidden Gems in SQL Server 2005 - persisted computed columns are item #3
• SQL Server Computed Columns
• Working with computed columns

这篇关于Sql Server:索引列上的较低功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！