• 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.

Example:

Create Index

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.