Difference in JOIN Sort with GUID 2000 / 2005

I found this issue while reviewing a suspected code error in a .NET application I am working on. There is a difference in join sort order between SQL 2000 and SQL 2005. Before I get into the details of this, I want to first point out that joining tables in the manner that occurs here is not, generally speaking, a best practice. I generally don't join tables on non-PrimaryKey/ForeignKey columns, but in this case that is what was done in SQL code.

A few tables are required to demonstrate this issue. I keep a general use Sandbox database on all my servers where I can do things like stage data, or create test tables to test issues as I find them, or they get reported on the SQL Forums. You will need a SQL Server 2000 database server and a SQL Server 2005 database server to test this.

Table Scripts:

CREATE TABLE dbo.xr_Table1_Table2
(
id int NOT NULL IDENTITY (1, 1),
table1key int NOT NULL,
table2key int NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.xr_Table1_Table2 ADD CONSTRAINT
PK_xr_Table1_table2 PRIMARY KEY CLUSTERED
(
table1key,
table2key
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table1] (
[table1key] [int] IDENTITY (1, 1) NOT NULL ,
[datacol] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[guid] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[table1key]
) ON [PRIMARY]
GO
CREATE TABLE dbo.Table2
(
table2key int NOT NULL IDENTITY (1, 1),
datacol varchar(50) NOT NULL,
datacol2 varchar(50) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table2 ADD CONSTRAINT
PK_Table2 PRIMARY KEY CLUSTERED
(
table2key
) ON [PRIMARY]
GO

Script to generate some test data:

DECLARE @Loopvar int
SET @Loopvar = 1
WHILE @Loopvar < 100
BEGIN
INSERT INTO Table2 (datacol, datacol2)
VALUES ('This is a test #' + convert(varchar, @loopvar), convert(varchar, @loopvar * 3.1459))
SET @Loopvar = @Loopvar +1
END

And finally the script to show the difference:

DECLARE @temp TABLE
(
id int identity primary key,
datacol varchar(50),
guid uniqueidentIFier,
table2key int,
table1key int
)
DECLARE @loop2 int
DECLARE @guid uniqueidentifier

SELECT @loop2 = min(table2key)
FROM table2

WHILE @loop2 IS NOT NULL
BEGIN
IF @loop2 not in (25, 50, 75)
BEGIN
SET @guid = newid()
END
INSERT INTO @temp (datacol, guid, table2key)
SELECT datacol, @guid, @loop2
FROM table2
WHERE table2key = @loop2

SELECT @loop2 = min(table2key)
FROM table2
WHERE table2key > @loop2
END

BEGIN TRANSACTION

INSERT INTO table1 (datacol, guid)
SELECT datacol, guid
FROM @temp

UPDATE t
SET table1key = t1.table1key
FROM @temp t
JOIN table1 t1 ON t.guid = t1.guid

INSERT INTO xr_table1_table2 (table1key, table2key)
SELECT table1key, table2key
FROM @temp

SELECT * FROM @temp
SELECT * FROM table1
SELECT * FROM table2
SELECT * FROM xr_table1_table2

ROLLBACK TRANSACTION

Results in SQL 2000:

@TEMP DATA

id datacol guid table2key table1key
----- -------------------------- ---------------------- ------------------------------------ ----------- -----------
24 This is a test #24 88914477-CC69-4AB8-ACCA-9CD21C51CB2B 24 619
25 This is a test #25 88914477-CC69-4AB8-ACCA-9CD21C51CB2B 25 619


TABLE 1 DATA
table1key datacol guid
----------- ---------------------------- ------------------------------------
618 This is a test #24 88914477-CC69-4AB8-ACCA-9CD21C51CB2B
619 This is a test #25 88914477-CC69-4AB8-ACCA-9CD21C51CB2B

Results in SQL 2005:

@TEMP DATA
id datacol guid table2key table1key
----- -------------------------- ---------------------- ------------------------------------ ----------- -----------
24 This is a test #24 29B0B6F1-E36A-4CC5-BA0A-52A0DD3F6633 24 321
25 This is a test #25 29B0B6F1-E36A-4CC5-BA0A-52A0DD3F6633 25 321


TABLE 1 DATA
table1key datacol guid
----------- ---------------------------- ------------------------------------
321 This is a test #24 29B0B6F1-E36A-4CC5-BA0A-52A0DD3F6633
322 This is a test #25 29B0B6F1-E36A-4CC5-BA0A-52A0DD3F6633

For whatever reason, on this type of join, SQL 2000 sorts descending, and SQL 2005 sorts ascending. I didn't report this issue on Connect because I am not sure that it really is an issue. It was surprising to find however.