none
Adding Identity column to an existing table! RRS feed

  • Question

  • Hallo,

    I am using SQL Server 2005. How can i add a column to an existing table with data which should be an identifier with data type numeric(of course not null)?
    How will the existing rows value for the new column will be filled? Will that cause any problems, if i update a table with large number of rows?

    Thanks n Regards
    swingme
    Thursday, January 14, 2010 8:52 AM

Answers

  • -- Default naming
    CREATE TABLE #Sample (Data INT)
    
    INSERT	#Sample(Data)
    SELECT	Number 
    FROM	master..spt_values
    WHERE	Type = 'P'
    
    SELECT * FROM #Sample order by data
    
    ALTER TABLE	#Sample
    ADD RowID INT IDENTITY(-7, 19) PRIMARY KEY CLUSTERED
    
    SELECT * FROM #Sample order by data
    GO
    DROP TABLE #Sample
    GO
    
    -- User naming
    CREATE TABLE #Sample (Data INT)
    
    INSERT	#Sample(Data)
    SELECT	Number 
    FROM	master..spt_values
    WHERE	Type = 'P'
    
    SELECT * FROM #Sample order by data
    
    ALTER TABLE #Sample
    ADD RowID INT IDENTITY(15, -2) 
    
    ALTER TABLE	#Sample
    ADD CONSTRAINT [PK_Sample] PRIMARY KEY CLUSTERED 
    (
    	RowID ASC
    )
    
    
    SELECT * FROM #Sample order by data
    
    Go
    DROP TABLE #Sample
    • Marked as answer by swingme Thursday, January 14, 2010 10:37 AM
    Thursday, January 14, 2010 9:01 AM