CODEWITHSUNDEEP
sqlsql-serverindexingsql-server-2016
SE1986
SE1986

Reputation: 2740

SQL Server Function Based Index Without Computed Column

I have a SQL Server query which is running slow due to the WHERE clause

WHERE LEFT(CustomerId,3) = 'ABC'

I need the LEFT function there as there are other customer IDs which begin with 'BCD', 'CDE' or 'DEF' and I only want those with ABC in my results. However, I need the query to be quick but the function is killing it, causing an index scan.

I know in Oracle there is a way to create a function based index so I would be able to do

CREATE INDEX IX_Customer_ID ON Customer (LEFT(CustomerId,3))

I would like to create similar functionality in SQL Server. I know I can do this using a computed column and creating an index on that column. However, I was wondering if there was a way to do this without a computed column?

Upvotes: 1

Views: 2032

Answers (1)

Pred
Pred

Reputation: 9042

You cannot create an index based on a function in SQL Server. You can however create an index on a computed column (with restrictions, such as the computation has to use only deterministic functions).

In your specific case, you don't have to use any functions, just to modify your query slightly.

Since left(column, 3) = 'abc' equals to column like 'abc%' you can safely use it. SQL Server is able to use indexes with the LIKE operator when the pattern contains a constant at the beginning (abc in this case).

Upvotes: 4

Related Questions