Supplement to page Splits: Change in SQL Server 2012

On one sentence, Page splits does not always make a 50/50 split in SQL Server 2012!

Recently I was preparing for a training and checking on page splits. Suddenly I thought that it is better to tell people about the numberof page splits can occur in a single statement and the amount of activities it can take. I found something new … well its new to me,  as my subsequent search over the internet didn't give any answers.

My Investigation On Heap Table: Why It Shows Data Not In Order?

Recently, while I was looking at DatabaseLog table in AdventureWorks database, I found something wired. Before going further, let us look at some important information about this table. I publish only the information of my concern.

First of all, this table does not have a clustered index. It has DatabaseLogID which is an identity column which is also the primary key, non-clustered. The next point is that it has variable length columns.  The wired thing came, when I executed SELCT * from DatabaseLog statement.  

SET QUOTED_IDENTIFIER, ANSI_NULLS ON

GO

CREATE TABLE dbo.DatabaseLog(

     DatabaseLogID int IDENTITY(1,1) NOT NULL,

     PostTime datetime NOT NULL,

     DatabaseUser sysname NOT NULL,

     Event sysname NOT NULL,

     Schema sysname NULL,

     Object sysname NULL,

     TSQL nvarchar(max) NOT NULL,

     XmlEvent xml NOT NULL,

 CONSTRAINT PK_DatabaseLog_DatabaseLogID PRIMARY KEY NONCLUSTERED

(

     DatabaseLogID ASC

)

To my surprise, the data didn’t come in ascending order of databaseLogID. In fact it has no order. This wake my curiosity and I started digging further.

When the table does not have a clustered index, the table is considered a heap.  It is not just the name. Consider a heap of stones stored along the roadside for some repair. There is no order, and any stone can be considered as the first. In database world, heap table also considered as the same.

But my question was different.  Okay, there is no order, but I am getting the results in some order. Not the primary key order, but some other order.  What is that order?