Sql Server : Lower function on Indexed Column.

Question

I found one big issue.

I have added the Lower function to indexed column of one of the table to fetch the data. The table contains more then 1 lac records.

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

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

Please Help!

Answer 1

When you add LOWER() (or any function) around a column it is no longer possible to use an index (it is no longer SARG-able).

By default, SQL Server is not case sensitive, so you should be able to remove it.

Answer 2

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)

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.

Links:

• 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