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
CREATE TABLE BankAccounts
 (AcctID int IDENTITY,
  AcctName char(15),
  Balance money,
  ModifiedDate date)
GO

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

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

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
ADD CONSTRAINT pk_acctID PRIMARY KEY (AcctID)

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