NOLOCK vs Clustered Index Order Part IV

Having a heap doesn't influence the order.
Both queries return the data in the same random order.

I've asked the question to a lot of people already including MVP's, Microsoft Employees... an answer will come :-)

Unfortunately nobody has an immediate answer :-(

I'll keep you posted!

Ken Henderson's Guru's Guide to SQL Server Architecture

Please check Ken's post.
Let him know how you feel about including OS information in his new book!

NOLOCK vs Clustered Index Order Part III

Ok... even worse now :-p
Issuing a DBCC DBREINDEX solves the NOLOCK order problem but then again... not if you have multiple datafiles in your database.

Oh boy :-)

NOLOCK vs Clustered Index Order Part II

Still haven't figured out why exactly it's happening but here's a script for the people that want to try.

CREATE TABLE tblClustered
(ID int,
MyDate smalldatetime,
TestField varchar(50))
GO
CREATE CLUSTERED INDEX ixID ON tblClustered (ID, MyDate)
GO
DECLARE @i int
SET @i = 0
WHILE @i < 1000
BEGIN
INSERT INTO tblClustered (ID, MyDate, TestField) VALUES (RAND() * 1000, CONVERT(varchar, getdate(), 112), REPLICATE('T', 50))
SET @i = @i + 1
END
SELECT * FROM tblClustered (NOLOCK)
SELECT * FROM tblClustered

DROP TABLE tblClustered

NOLOCK vs Clustered Index Order

I'm trying to understand something.

I have a simple table with a Clustered Index on an integer field.

When I use 'SELECT * FROM tblClustered' the records are nicely ordered by the clustered index key. When I use 'SELECT * FROM tblClustered (NOLOCK)' however, the records are returned in a semi-random order. With semi-random I mean that the order is always the same but they are not sorted by the clustered index key. The queryplan is the same, I've tried DBCC DROPCLEANBUFFERS, CHECKPOINT, Restart of the SQL Server Service, you name it...

Obviously you should never rely on the clustered key for the sort order but I'm just trying to figure out what's happening 'inside' that makes this difference.

More to follow!

Performance Fix for AMD Dual Core users

Something for freaks like me :-)
There seems to be a performance fix for AMD Dual Core users with Windows XP SP2. It's seems like you have to contact Microsoft for the fix and it can't be just downloaded... weird :-s

Source

Business Intelligence

Hmm... now that SQL Server 2005 is getting closer I'd like to acquire some knowledge about Business Intelligence.

Let's start with this article