December 2011 - Posts

Q. When is a HEAP not a HEAP ?

A. When it’s on SQL Server.

  A very important part of Business Analysis is understanding the perceptions of users even if they haven’t clearly stated a requirement, so a SQL Server table without a clustered index is known as a heap ( in fairness I could say that a table with a clustered index is an ordered heap ), programmers understand heaps as a last in first out “store” ( LIFO ) so if I posed this question

  " If I put 200 rows of data into a SQL Server table which has no indexes and then select a top 100 * would you expect the first 100 rows or last 100 rows to be returned?"

  If you think HEAP then your answer probably will be " the last 100 rows " ( LIFO ) ( this was the answer I received from those I asked )

  However in my tests with SQL Server 2008 and SQL Server 2012 the results returned are always the first 100 rows. This must prove that even a HEAP follows the default ASC order which is by default applied to every index created, it also shows that you cannot actually implement a heap process without using an order by – which is annoying to say the least. There’s also some similar ( ish ) behaviour with partitioned tables where a top on a desc clustered key actually returns asc data, or in some cases a semi random set of data, however I haven’t finished my tests on SQL 2012 yet so I’ll post about this later.

  It’s purely of incidental interest of course but the fact remains you don’t always want to have sorts being carried out on data which is already ordered and if your table is a heap then I could say it should behave like one.

  Here’s a dictionary definition for HEAP "a group of things placed, thrown, or lying one on another"

 If you consult Wikipedia concerning LIFO it says this belongs to a stack.

 Here’s a dictionary definition for stack "a more or less orderly pile or heap"

( tried to sort out the formatting - sorry - what you see isn't always what you get! )

Posted by GrumpyOldDBA with no comments
Filed under: