Ok... Tom is correct. The "hack" is no good (I should have checked more deeply before I suggested it :blush:). It looks mighty fine on the Execution Plan because it does return as a Clustered Index Seek but it only seeks to the logical "first" row in the table and then continues as a scan.

It's better to put indexes on the two columns and make sure that you have a good FILL FACTOR on the index for the GUID. The "Key Lookups" for queries against the GUID column are much more efficient than the false seek that the hack produces.

If you want to see for yourself, here's the code that I should have used before saying anything about the possible hack. Apologies again for suggesting the hack without further testing. :blush:

--===== Conditionally drop the test table to make reruns easier in SSMS

IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL

DROP TABLE #TestTable

;

--===== Create and populate the test table on-the-fly

SELECT TOP 1000000

SomeID = IDENTITY(INT,1,1),

SomeGUID = ISNULL(NEWID(),NEWID()), --ISNULL just to make the resulting column NOT NULL

SomeDate = ABS(CHECKSUM(NEWID()))%DATEDIFF(dd,'2000','2010')+CAST('2000' AS DATETIME),

SomeInt = ABS(CHECKSUM(NEWID()))%100000

INTO #TestTable

FROM master.sys.all_columns ac1

CROSS JOIN master.sys.all_columns ac2

;

--===== Create the clustered index featured in the "hack".

-- Used alone, this creates a very misleading Clustered Index Seek

-- which is really a full table scan in disguise.

CREATE UNIQUE CLUSTERED INDEX IX_XRef ON #TestTable (SomeID, SomeGUID)

;

--===== If you add this index, you get seeks with Key Lookups. If you

-- look at the IO statistics it produces, it's much more effecient

-- than the hack. Apologies for suggesting the hack without testing it.

--CREATE NONCLUSTERED INDEX [IX_SomeGuid]

--ON [dbo].[#TestTable] ([SomeGUID])

--;

--===== Select a GUID from somewhere in the table.

-- We're just getting a GUID that's know to exist in the table.

DECLARE @SomeGUID UNIQUEIDENTIFIER;

SELECT @SomeGUID = SomeGUID

FROM #TestTable

WHERE SomeID = 1

;

SET STATISTICS IO ON;

--===== Now, demonstrate that we can get a CI seek when looking

-- for a GUID using the ">" hack... BUT IT'S A FALSE SEEK

-- BECAUSE IT FINDS THE FIRST ROW WHICH HAS A SomeID > 0

-- AND THEN GOES INTO A SCAN MODE AS ATTESTED TO BY THE

-- IO STATISTICS.

SELECT SomeDate, SomeINT

FROM #TestTable

WHERE SomeID > 0

AND SomeGUID = @SomeGUID

;

--===== Now, demonstrate that we get a CI scan if we don't use

-- the ">" hack.

SELECT SomeDate, SomeINT

FROM #TestTable

WHERE SomeGUID = @SomeGUID

;

SET STATISTICS IO OFF;