- Avoid using calculations on the table columns in conditions (where clause or Join predicates). A calculation on the left side of where clause or join predicates cause Index scans even when appropriate index is present to perform the seek operation.
CREATE NONCLUSTERED INDEX [IX_Person_LastName_FirstName] ON [Person].[Person] ( [FirstName] ASC , [LastName] ASC ) INCLUDE ([PersonType]) --Now execute below two queries. DECLARE @Name VARCHAR(40) = 'Joe Sanz' SELECT PersonTypeFROM [Person].[Person] WHERE [FirstName] + ' ' + [LastName] = @Name SELECT PersonTypeFROM [Person].[Person] WHERE [FirstName] = SUBSTRING(@Name, 1, CHARINDEX(' ', @Name) - 1) AND [LastName] = SUBSTRING(@Name, CHARINDEX(' ', @Name) + 1, LEN(@Name))
The output of both the queries is same but there is a huge difference in the execution plan.
The first query is using index scan while second query is using is using index seek and batch cost of the first query is 97% while it is 3 % for second query.
Reason of Index Scan: Index has the information about first name and last name individually, but it has no information about the expression Firstname +’ ‘+LastName so optimizer was forced to use Index scan.
The other way to get the rid of Index scan is to use Computed columns
Let’s check the table size before adding computed columns
Now create the computed column
ALTER TABLE [Person].[Person] ADD FullName AS ([FirstName] + ' ' + [LastName]);
Check the size of the table again
The size of the table is still same because computed column does not store any data physically in computed column.
Let’s create an index on FullName column
USE [AdventureWorks2016] GO CREATE NONCLUSTERED INDEX [IX_FullName] ON [Person].[Person] ([FullName]) INCLUDE ([PersonType])
Now select the same value using FullName column
DECLARE @Name VARCHAR(40) = 'Joe Sanz' SELECT PersonType FROM [Person].[Person] WHERE [FullName] = @Name
And the execution plan is again using Index seek.