Points to Consider While Writing Queries - Microsoft SQL Server 2012 Performance Tuning - page 266

4. Now, dropping the non-clustered index created for this example and creates one "Non

Clustered "on "First Name" column:
DROP INDEX IDX_Person_ModifiedDate ON Person.Person
GO
CREATE INDEX IDX_Person_FirstName ON Person.Person(FirstName)
GO

5. Now, selecting all people from the Person table whose first name starts with R . In

this kind of task, logical steps to use are string functions, such as LEFT , RIGHT , or
SUBSTRING , which is again a bad idea:
SELECT
BusinessEntityID
,FirstName
FROM
Person.Person
WHERE
Left(FirstName,1)='R'
GO

6. As already said, a shorter query is not always a smarter query, we can go for an

alternative like this:
SELECT
BusinessEntityID
,FirstName
FROM
Person.Person
WHERE
FirstName LIKE 'R%'
GO

7. By looking at the execution plan of both queries, the picture becomes clear. The

second query seek on the non-clustered index IDX_Person_FirstName whereas
first query makes scan on the non-clustered index IDX_Person_FirstName . The
second query ran faster and used only 9 percent of the total query execution cost,
which is faster.