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;

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;