Starting from SQL Server version 2008, a new method was introduced to overcome the forwarding pointers performance issue, by using the ALTER TABLE REBUILD command, that will rebuild the heap table.ДЄ SQL Server index is considered as one of the most important factors in the performance tuning process. These forwarding pointers will cause performance issues over time due to visiting the old/original location vs the new location specified by the forwarding pointers to get a specific value. When a data modification operation is performed on heap table data pages, Forwarding PointersВ will be inserted into the heap to point to the new location of the moved data. Q3: What is the вЂњForwarding Pointers issueвЂќ and how can we fix it? The IAM is considered as the only logical connection between the data pages, that the SQL Server Engine will use to move through the heap. SQL Server Engine uses an Index Allocation MapВ (IAM) to keep an entry for each page to track the allocation of these available pages. Q2: Explain how the SQL Server Engine uses an Index Allocation MapВ (IAM)? You can also query the sys.indexes system object also to show the heap table index details, that shows, the id of that index is 0 and the type of it is HEAP.ДЇor more information, see the article: SQL Server table structure overview. The heap table can be identified by querying the sys.partitionsВ system object that has one row per each partition with index_id value equal to 0. This is due to the fact that the heap table contains no clustered index.ДЄ clustered table is a table that has a predefined clustered index on one column or multiple columns of the table that defines the storing order of the rows within the data pages and the order of the pages within the table, based on the clustered index key. In addition, there is no particular order to control the data page sequence, that is not linked in a linked list. Q1: What is the difference between a Heap table and a Clustered table? How can we identify if the table is a heap table?ДЄ HeapВ table is a table in which, the data rows are not stored in any particular order within each data page.

Sql server indexing