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

SQL Server 2005 Mission Critical High Availability

High availability with SQL Server 2005
Lots of interesting information here

SQLPass.NL

Check out our very own PASS for Dutch speaking people :-)
http://www.sqlpass.nl

Atlas - .NET feat. AJAX

AJAX rocks!
Check out this preview site.

Windows Vista Release Schedule

Check it out here

Funny news

How about this woman :)
Here

Filter table on NULL value in Reporting Services

I was trying to find the correct filter statement for a NULL value in Reporting Services but I always seemed to fail.

So what's the trick?

Filter: =IsNothing(Fields!YourField.Value)
Operator: =
Value: =True

Notice the = before True as this really makes a difference!

Query Optimizer vs Statistics

A nice Technet article on how the query optimizer uses statistics
Here

More on LINQ

Nice article on MSDN about LINQ

Visual Studio 2005 CTP Uninstaller

Aha... official tool by Microsoft

SQL Dependency Viewer

The people from RedGate keep amazing me with great tools.
Check out this tool... bye bye select * from sysdepends :-)

Been a while

It's been a while but there is a very good reason for that :-(

There were some complications with the pregnancy of my girlfriend. The little one wanted to get out already but my girlfriend is only 30 weeks pregnant so it has to stay in a couple of weeks longer.

Anyway, a week and lots of medication later it seems to be under control... for the time being.

So as of tomorrow I should be back on track and posting interesting SQL stuff.

LINQ

A very weird innovation for a DBA :-)

LINQ

MSDN Webcasts

We all love them but can we find the time to listen to them?
Well Jeffrey Palermo found an interesting way... here