|
While working on one of our on-site consulting projects I was
approached by one of the in-house developers with seemingly unexplainable SQL
Server 2008 behavior that they were battling for days to solve.
The team was developing an online two levels only forum. All
messages were stored in SQL Server table with each row having its parentId
pointing to the parent message. The level 0 messages having parentId 0.
In order to prepare the forum for rendering in their web site
forum page they used adjacency list model for the forum data organisation – in
other words they expected to have forum data organized as following:
-
Parent 1 Message
-
Child 1 of Parent 1 Message
- Child 2 of Parent 1
Message
- Child 3 of Parent 1 Message
-
Parent 2 Message
-
Child 1 of Parent 2 Message
-
Child 2 of Parent 2 Message
Etc..
In order to generate this result they used two stored
procedures - one which created
CURSOR that ran over all the level 0 messages – called spGetParentList .
In the beginning of spGetParentList temporary table
was created as following
create
table #ForumMessages
(
MessageId
int,
Subject varchar(100),
Message varchar(max),
)
For each row fetched in cursor it first added it to #ForumMessages table and then
called other stored procedure ,named
spGetChildrenForParent, and passed it current message id.
spGetChildrenForParent was responsible to list all children for that Id and
it also added them into the same temporary table (#ForumMessages)
In the end of procedure spGetParentList there was
select MessageId,Subject,Message from
#ForumMessages
and of course there was a drop of the temporary table just
before end of spGetParentList stored procedure.
So far , everything
looked fine but each time you ran spGetParentList you got a little
different result. In many cases children rows coming before parent rows.
Example:
-
Parent 1 Message
-
Child 1 of Parent 1 Message
- Child 2 of Parent 1
Message
- Child 3 of Parent 1
Message
-
Child 2 of Parent 2 Message
-
Parent 2 Message
-
Child 1 of Parent 2 Message
Etc..
In order to start diagnosing this problem we added PRINT of
the each Message Id before it was inserted to the #ForumMessages temporary table in both stored procedures. After we ran the
stored procedures with PRINTs we saw that the messages were inserted in the
correct order.
Then I paid attention to the fact that #ForumMessages
had no clustered key
so who promised us that the rows would be returned
in the select statement in the same order they were inserted into the table – it
was incorrect to assume that SQL server will keep the order – if no clustered
index defined on table and there is no ORDER BY specified SQL Server will store
the information in its own order based on its diskpsace and other
considerations.
So now that the problem was clear all we had to do is to fine
was to sort result for output based on insert order into the temporary table.
MessageId could not be used as sort
field as it was identity number that was generated on creation of message in the
forum. In order to keep insert order in the temporary table we added identity
field that increased by one with each insert into temporary table.
Therefore
#ForumMessages was changed to following
create
table #ForumMessages
(
SequenceId
int identity(1,1), -- new identity
field added MessageId
int ,
Subject varchar(100),
Message varchar(max),
)
Now that we had identity field that increased with each
insert all we had to do is return result sorted by SequenceId.
select MessageId,Subject,Message
from #ForumMessages
order by SequenceId
|