Views on comparing the organizational structure of data tables through clustered indexes and heaps-SQL Server Best Practices

This article mainly tests the query efficiency of clustered index tables and heap tables in the case of insertion, deletion, update, query, and concurrency.
When a single user inserts, deletes, updates, and queries, the clustered index table is more efficient than the heap table.
This is because only one read/write operation is performed on the clustered index table during insert, delete, and update operations, and two read/write operations on the heap table are performed respectively, you need to maintain the index data and table data.
When the page splits/sec indicator is inserted, the clustered index table is much higher than the heap table. This is because the data is organized according to the clustered index column during data insertion, therefore, the splitting of leaf/non-leaf nodes in the clustered index table is much higher than that in the heap table.
In the case of clustered index tables, page splits/sec = pages allocated/sec, that is, the split speed, that is, the re-allocation speed
In the case of heap tables, pages allocated/sec must be greater than the clustered index table because the heap Table Page is disordered and must be allocated from the iam page each time, the clustered index table can be searched through a two-way linked list.
Pages allocated/sec is the number of pages allocated per second in all databases of the SQL server instance. These pages include the pages allocated from the hybrid zone and unified zone.

For queries, clustered indexes are of course the fastest choice, and the heap table needs to be searched twice. The update and delete operations are similar.

In the case of concurrency, the data insertion efficiency is better than the clustered index table, mainly reflected in page splits/sec and page latch waits per
Page latch waits per
Second can be understood as the number of waiting for the use of the page, because of the sorting of the clustered index data organization, for example, to compete for the hotspot page, and the heap table does not have this problem.

In summary, the performance of clustered index tables is generally better than that of heap tables.

However, this test also has some problems. The ordering of test data cannot be demonstrated. the ordering of index column data has a great relationship with insertion and space utilization, it also affects subsequent update and deletion tests.
The second is that the column width of the table is too small and the initial index fill factor is 0, which is not significant for the test of update and delete operations, because the updated column width has not changed, this does not affect page splitting and space utilization.