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.

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.

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.

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.