Execution Plans – Table Operators

Table Operators in an Execution Plan

In a previous blog, we discussed the different Table Structures in SQL Server. Another way of determining if a table is structured as a Heap or a Clustered Index is to look at the table operators from the Execution Plan. As always, there are outliers to how things work. But for now, we are just looking at the basic table operators.

First, we are going to build a new table named Accounting.BankAccounts and insert four records. We are not adding a Primary Key or any indexes. Please note that the AcctID field will not allow NULL values. This will be helpful later on when we do add a Primary Key.

Create the Accounting.BankAccounts Table

Now when we run a SELECT statement and look at the Execution Plan, you will notice that the query optimizer needed to perform a table scan. This is because the data is being stored in a heap. Keep in mind a scan is not always bad and in this case, it is perfectly fine.

SELECT Bank Acounts Table
Table Scan Operator

Now let’s add a Primary Key to the table and run the SELECT statement again. The Execution Plan will perform a Clustered Index Scan instead of the Table Scan once the Clustered Index has been added.

Add Primary Key to Bank Accounts table
Clustered Index Scan Table Operator

Finally, if I wanted to have the query optimizer perform a SEEK operation instead of a SCAN, I would need to include a WHERE clause in the SELECT statement. Again, not to keep pointing this out, but there are situations that a SCAN operation will still happen even with a WHERE clause. But for now, we are just focusing on the basic table operators.

Clustered Index Seek Operator

Be the first to comment on "Execution Plans – Table Operators"

Leave a comment

Your email address will not be published.


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.