First published on MSDN on Mar 08, 2009
In general, you should avoid applying a scalar function on a column when comparing against another column, variable or constant.
Let's use an example. Frequently, we got cases from customers who are not aware of performance implications and do just that. If you have query like below. It poses challanges to SQL Server optimizer. It can't use index on c1 to do any seeks.
select * from t where lower(c1) = 'az'.
One natural solution is to avoid applying function lower. But what if the column is case senstive? Then the result won't be correct if you drop the 'lower' function.
there is another solution. The solution is to create another computed column on t (c2 as lower(c1)). Then create an index on c2.
now the above query will use index defined on c2 to do seeks. Below is a complete example to demonstrate the technique. There are serveral requirements on creating indexes on computed columns. See books online "Creating Indexes on Computed Columns" section for details
use tempdb
go
drop table t
go
create table t (c1 varchar(50) collate Latin1_General_CS_AS)
go
set nocount on
declare @i int
set @i = 0
while @i < 10000
begin
declare @ch varchar(10)
set @ch = cast (@i as varchar(10))
insert into t (c1) values (@ch)
set @i = @i + 1
end
go
create index indx_c1 on t(c1)
go
update statistics t with fullscan
go
set statistics profile on
go
--note that this query does table scan because of the lower function applied
select * from t where lower(c1) = '00'
go
set statistics profile off
go
--let's add a computed column and create index on this computed column
alter table t add c2 as lower(c1)
go
create index indx_c2 on t(c2)
go
set statistics profile on
go
--note that this query does index seek
select * from t where lower(c1) = '00'
go
set statistics profile off
go
Jack Li | Senior Escalation Engineer | Microsoft SQL Server Support
