Sql

Table structure and total records

Let’s look at our table structure as shown in the left panel of the picture

Note: There is a non-clustered index on the Title column of the Book table, which has a total of 1 million records.

select
  Id,
  Title,
  Author,
  Price
from Book where title='The Divine Comedy'

As you have noticed in the execution plan, there is a key lookup. Since index is only available for the Title column, Title is retrieved from the non-clustered index. However, other columns like Id, Author and Price are not present in the non-clustered index, so they will be retrieved from the clustered index (the Book table). This results in an additional key lookup step and may decrease the performance. It’s like, you are looking for records in two places (non clustered index and clustered index).

A query is SARGable (Search ARGument able) if a database engine can take advantage of index.

What makes a query non SARGable?

These are the few factors that makes a query non sargable.

1. Put a column inside a function in the where clause.

select
 s.SalesOrderID,
 s.SalesPersonID,
 s.OrderDate,
 s.SalesOrderNumber
from Sales.SalesOrderHeader s
where YEAR(s.OrderDate) = 2011; -- Row Retrieved: 1607 and Row read : 31465

Note: I have created covering index on the OrderDate:

Initially, it was not intended to be an article. I was tinkering around sql and documenting few things, then I thought, it could turnout to a nice article. I have wrote lots of article and created lots of videos on c#, .net core and angular. But it is my first article on the SQL.

In this blog post, we are going to explore few terms of execution plan, like table scan, clustered index scan, index seek, RID lookup and key lookup.