Hey Jeff & Flo,

Just a couple of things... 🙂

1. This isn't a true 'quirky' update unless there's a clustered index to determine the order of the ID assignment. It is true that the current SQL Server behaviour is always to return rows from heaps in insertion order (at least with a new heap that has never had any update or delete activity on it), but still.

2. This method runs in less than half the time (2005 only):

SET NOCOUNT ON

--===== Conditionally drop the test table for test reruns

IF OBJECT_ID(N'tempdb..#my_tab', N'U') IS NOT NULL DROP TABLE #my_tab;

IF OBJECT_ID(N'tempdb..#my_tab2', N'U') IS NOT NULL DROP TABLE #my_tab2;

--===== Build the test table and populate it with data on the fly

SELECT TOP 100000

ISNULL(CAST(0 AS INT), 0) AS ID,

DATEADD(mi,-ABS(CHECKSUM(NEWID())%100000),GETDATE()) AS Any_Date

INTO #My_Tab

FROM Master.dbo.SysColumns t1 CROSS JOIN Master.dbo.SysColumns t2

DECLARE @now DATETIME;

SET @now = GETDATE();

-- Bulk logged *temporary* IDENTITY addition

SELECT id = IDENTITY(INT, 1000, 1),

any_date

INTO #my_tab2

FROM #my_tab WITH (TABLOCK);

-- Fast clear of the original table

TRUNCATE TABLE #my_tab;

-- Metadata only change

ALTER TABLE #my_tab2 SWITCH TO #my_tab;

PRINT 'Duration for 100000 values: ' + CONVERT(VARCHAR(20), DATEDIFF(MILLISECOND, @now, GETDATE()));

-- Tidy up

DROP TABLE

#my_tab, #my_tab2;

Paul