SQL Server add auto increment primary key to existing table

by

No – you have to do it the other way around: add it right from the get go as INT IDENTITY – it will be filled with identity values when you do this:

ALTER TABLE dbo.YourTable
   ADD ID INT IDENTITY

and then you can make it the primary key:

ALTER TABLE dbo.YourTable
   ADD CONSTRAINT PK_YourTable
   PRIMARY KEY(ID)

or if you prefer to do all in one step:

ALTER TABLE dbo.YourTable
   ADD ID INT IDENTITY
       CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED

More Related Contents:

  1. How to choose the clustered index in SQL Server?
  2. If condition in SQL Server 2008
  3. Comma separated results in SQL
  4. What are the best practices for using a GUID as a primary key, specifically regarding performance? [closed]
  5. Understanding PIVOT function in T-SQL
  6. Which are more performant, CTE or temporary tables?
  7. Using RegEx in SQL Server
  8. What is the maximum characters for the NVARCHAR(MAX)? [duplicate]
  9. How do I view the full content of a text or varchar(MAX) column in SQL Server 2008 Management Studio?
  10. INSERT INTO vs SELECT INTO
  11. How do I use the CONCAT function in SQL Server 2008 R2?
  12. How can I automate the “generate scripts” task in SQL Server Management Studio 2008?
  13. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  14. Is it possible to force row level locking in SQL Server?
  15. Select / Insert version of an Upsert: is there a design pattern for high concurrency?
  16. How do I get date/time information from a TIMESTAMP column?
  17. Why does select SCOPE_IDENTITY() return a decimal instead of an integer?
  18. How to add xml encoding to xml Output in SQL Server
  19. How to: Create trigger for auto update modified date with SQL Server 2008
  20. error when inserting into table having instead of trigger from entity data framework
  21. What happens to the primary key Id when it goes over the limit?
  22. SQL Recursive CTE: Finding objects linked by property
  23. Convert decimal time to hours and minutes
  24. Sort Bullets in Database
  25. How Can I Detect and Bound Changes Between Row Values in a SQL Table?
  26. How to find the boundaries of groups of contiguous sequential numbers?
  27. “Prevent saving changes that require the table to be re-created” negative effects
  28. How to change column datatype in SQL Server database without losing data?
  29. Table Valued Function Killing My Query Performance
  30. How to convert text column to datetime in SQL

Leave a Comment