Sql server: bottom function on indexed column

Question

I found one big problem.

I added the Lower function to the indexed column of one of the tables to retrieve data. The table contains more than 100 thousand records.

When retrieving records, CPU utilization is 100% changed.

I could not understand how this drastic change can only happen due to the Lower () function.

Please, help!

Answer 1

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

ALTER TABLE dbo.YourTableName ADD LowerFieldName AS LOWER(YourFieldName) PERSISTED CREATE NONCLUSTERED INDEX IX_YourTableName_LowerFieldName ON dbo.YourTableName(YourFieldName) 

This will maintain the representation of your field in your lower case, it is always relevant, and since it is stored, it is part of your table and is not responsible for the LOWER () function. Put an index on it and your search will be as fast as before.

References:

• MSDN docs for computed columns in SQL Server 2008
• Integrated Computing Columns
• Using computed columns in SQL Server with saved values
• Top 10 hidden gems in SQL Server 2005 - Constant calculated columns are item # 3
• Computed SQL Server Columns
• Work with calculated columns

Answer 2

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

By default, SQL Server is not case sensitive, so you can remove it.