TSQL Execution Plan Operator - Index Scan

In execution plan when you see an Index scan operator, it indicates that SQL Server has to scan the data or index pages to find the appropriate records and The second operator is a clustered index scan and it similar to the table scan, and it indicates a retrieval of all rows from a table with a clustered index. In this video, we’ll be going to learn about these two operators.

In this session, we’ll be going to learn about one of the T-SQL execution plan operators called Index Scan, which you might have seen in the execution plan. As a developer many times we are in a situation where some of our queries are not performing well. As a part of the troubleshooting process, you will want to look at the execution plan and see if there are any indications as to why the problem is occurring. Basically, an execution plan is a tree of operators. SQL Server 2014 has more than 100 different operators. In the execution plan, when you see the Index Scan Operator, it indicates that a SQL Server has to scan the data or index pages to find the appropriate records. The second operator in this game is a Cluster Index Scan which is similar to the table scan. It indicates a retrieval of all rows from a table with a Cluster Index. Now let’s perform the demo for this operator. I have created a new table called Patient Master. Here you can see that I have not defined the primary key for this (Patient ID) column.

It means a table without a primary key is called a heap. Before executing this query, enable the execution plan.Then execute this (SELECT PatientId LastName, FirstName, HomePhone From[dbo] [PatientMaster]) query. Here I have got nine rows and if I look at the execution plan, we can see that we have a table scan here on patient master. By looking at the table scan tooltip, we can see that we don’t have a Cluster Index on the table. That could be a noteworthy item to pay attention to.

Programmers Essentials TSQL

Now create the unique Clustered Index on the patient ID column by executing the script. When I execute the same select statement again here, we can see a Clustered Index Scan operator.

Now, you may have a question: what is the Cluster Index? A Cluster Index is the one in which the leaf node of the index is the actual data page itself. There can be only one Clustered Index per table because it specifies how records are arranged within the data page. Cluster Index Scan means the SQL engine is traversing your Cluster Index in search of a particular value or a set of values. It is one of the most efficient methods for locating a record bit by a Cluster Index Seek.

For more videos and articles visit:

Useful Links:

TSQL Execution Plan Operator - Index Scan

For more videos and articles visit:

Useful Links:

👉 GET YOUR FREE API KEY for PDF.co

✅ ON-PREMISE SDK FREE TRIAL:

🔎 SDK DOCUMENTATION:

🔑 WEB API DOCUMENTATION :