DELETE Operation in SQL Server HEAPs

You should stick to using tables in SQL Server, rather than heaps that have no clustered index, unless you have well-considered reasons to choose heaps. However, there are uses for heaps in special circumstances, and it is useful to know what these uses are, and when you should avoid heaps . Uwe Ricken explains, and demonstrates why you'd be unwise to use heaps rather than tables when the data is liable to change.… Read more

When AUTO_UPDATE_STATISTICS Doesn’t Happen

When your SQL Server database is set to have its statistics automatically updated, you will probably conclude that, whenever the distribution statistics are out-of-date, they will be updated before the next query is executed against that index or table. Curiously, this isn't always the case. What actually happens is that the statistics only gets updated if needed by the query optimiser to determine an effective query plan.… Read more

How Forwarded Records are Read and Processed in a SQL Server Heap

Before you deliberately use a heap in SQL Server rather than a table, it is worth understanding why a heap has such different characteristics, and therefore relative benefits and disadvantages. Forward pointers and the Page Free Space page are designed with performance in mind, but only in certain circumstances. Uwe explains why, and how heaps can be used to advantage.… Read more