What is Heap in SQL Server ?

  • 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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s