In case you guys think I'm cheating by changing the nullability of the id column, this version is a true replacement, and is just as fast:
SET NOCOUNT ON;
-- Conditional drops
IF OBJECT_ID(N'tempdb..#my_tab', N'U') IS NOT NULL DROP TABLE #my_tab;
IF OBJECT_ID(N'tempdb..#my_tab', N'U') IS NOT NULL DROP TABLE #my_tab2;
IF OBJECT_ID(N'tempdb..#my_tab', N'U') IS NOT NULL DROP TABLE #my_tab3;
-- Sample data
SELECT TOP (100000)
CAST(NULL AS INT) AS ID,
DATEADD(mi,-ABS(CHECKSUM(NEWID())%100000),GETDATE()) AS Any_Date
INTO #My_Tab
FROM master.sys.all_columns T1, master.sys.all_columns T2, master.sys.all_columns T3;
-- Start timing
DECLARE @now DATETIME;
SET @now = GETDATE();
-- Bulk logged identity add (id column is NOT NULL)
SELECT id = IDENTITY(INT, 1000, 1),
any_date
INTO #my_tab2
FROM #my_tab WITH (TABLOCK);
-- Intermediate table for nullability change
CREATE TABLE #my_tab3 (id INT NOT NULL, any_date DATETIME NULL);
-- Lose the IDENTITY attribute
ALTER TABLE #my_tab2 SWITCH TO #my_tab3;
-- Metadata change to id column nullability
ALTER TABLE #my_tab3 ALTER COLUMN id INT NULL;
-- Fast clear original table
TRUNCATE TABLE #my_tab;
-- Metadata switch data in
ALTER TABLE #my_tab3 SWITCH TO #my_tab;
-- Show duration
PRINT 'Duration for 100000 values: ' + CONVERT(VARCHAR(20), DATEDIFF(MILLISECOND, @now, GETDATE()));
-- Tidy up
DROP TABLE
#my_tab, #my_tab2, #my_tab3;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi