Usage of built-in functions on columns ignores/excludes Indexes on that column

Question

Usage of built-in functions on columns ignores/excludes Indexes on that column

devsql123

SSC-Addicted

Points: 478
More actions
May 25, 2015 at 1:37 am

#318454

Hello Folks,
Somewhere i read..that in SQL Server...usage of Built-in Functions on Columns, makes query optimizer to igonore indexes on that column...!!!
So lets say we have table EMP with Emp_id and Emp_Name....
Also we have Non-Clustered index on Emp_Name.
So following query would NOT use Non-Clustered index on Emp_Name column.
SELECTLEFT(emp_name, 3) as emp3
FROMdbo.EMP
Is this true...i tried to google it...but couldn't get anything...proper..so thought to ask here.
Just FYI, i am using SQL Server 2008.
Thanks
devsql

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

In your example, the index will be uses. Since you're using the column only in the select list and no search predicate is present in the query, the nonclustered index will be read fully.

Filter predicates that use functions to transform columns are said to be non SARG-able and cannot use indexes. Often the predicate can be expressed in different ways to make it SARG-able. In your case:

SELECT LEFT(emp_name, 3) as emp3

FROM dbo.EMP

--WHERE LEFT(emp_name, 3) = 'ABC' -- Non SARG-able: no index seek

WHERE emp_name LIKE 'ABC%' -- SARG-able: index seek (range scan under the hood, but execution plan shows a seek)

Is this the information you were looking for?

-- Gianluca Sartori

devsql123 SSC-Addicted Points: 478 More actions · Answer 2

Yup, you almost gave the answer.

But when i tried to test via below query, it doesn't follow what you said:

CREATE Table dbo.Test_1

(

Emp_Id int

,Emp_Name varchar(100)

)

Create Nonclustered Index NCIX_1

On dbo.Test_1(Emp_Name)

Insert Into dbo.Test_1

Values(1,'10:02:30')

Insert Into dbo.Test_1

Values(2,'11:22:34')

Insert Into dbo.Test_1

Values(3,'09:12:04')

Go 1000

SELECTEmp_Name, LEFT ( Isnull(Emp_Name,'00:00:00'),(Charindex(':',Isnull(Emp_Name,'00:00:00')) - 1))

FROMTest_1

WhereLEFT ( Isnull(Emp_Name,'00:00:00'),(Charindex(':',Isnull(Emp_Name,'00:00:00')) - 1)) > 10

When i ran above query, It does Non-Clustered Index Scan...means SQL Server is using Index even though Built-in function is used in Filter predicate...Which looks contradictory to your suggestion....

Please clearify me.

Thanks

devsql

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Functions on a column don't prevent index usage. They prevent index seeks. The index can always be scanned, but scans are usually less efficient than seeks

Also, you cannot draw conclusions about behaviors on 3 rows. Try a couple thousand as a minimum.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

I never said the index won't be used with non SARG-able predicates: I said it cannot be searched.

SQL Server has no way to navigate the index b-tree structure to find the rows that match the predicate, so it has to SCAN the whole index to evaluate every single row. With SARG-able predicates, the index can be searched.

-- Gianluca Sartori