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 execution plan 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 when we do add a Primary Key.

CREATE SCHEMA Accounting Authorization dbo
 (AcctID int IDENTITY,
  AcctName char(15),
  Balance money,
  ModifiedDate date)

INSERT INTO Accounting.BankAccounts
VALUES('Jack',500, GETDATE()),
	  ('Diane', 750, GETDATE()),
	  ('John', 650, GETDATE()),
	  ('Dora', 725, GETDATE())

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 AcctID, AcctName, Balance, ModifiedDate
FROM Accounting.BankAccounts
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.

---Adding a Primary Key will also create a clustered index.
ALTER TABLE Accounting.BankAccounts
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 execution plan table operators.

SELECT AcctID, AcctName, Balance, ModifiedDate
FROM Accounting.BankAccounts
WHERE AcctID = 1
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.