- Heap is nothing but, when you create any table without index.
- A table which has no Index is called “Heap”.
- For a heap INDEX_ID will be zero (0). (queried from sys.indexes)
- When search is performed on a heap, it is always going to be a Full Table Scan.
- Table Scans are dangerous for SQL Server Performance (large tables).
- How to find heap tables
select * from sys.indexes where index_id=0 and object_id(‘Table_1’)=object_id