How to Add an Identity Column to an Existing Database Table Which Has Large Number of Rows

Adding an identity to an existing column

You can't alter the existing columns for identity.

You have 2 options,

Create a new table with identity & drop the existing table
Create a new column with identity & drop the existing column

Approach 1. (New table) Here you can retain the existing data values on the newly created identity column. Note that you will lose all data if 'if not exists' is not satisfied, so make sure you put the condition on the drop as well!

CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL
             IDENTITY(1, 1),
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT  *
            FROM    dbo.Names ) 
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,
                    Name
            FROM    dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

Approach 2 (New column) You can’t retain the existing data values on the newly created identity column, The identity column will hold the sequence of number.

Alter Table Names
Add Id_new Int Identity(1, 1)
Go

Alter Table Names Drop Column ID
Go

Exec sp_rename 'Names.Id_new', 'ID', 'Column'

See the following Microsoft SQL Server Forum post for more details:

How to alter column to identity(1,1)

Adding auto increment identity to existing table in oracle which is not empty

You can not do it in one step. Instead,

Alter the table and add the column (without primary key constraint)
```
ALTER TABLE DEGREE ADD (Ident NUMBER(10));
```
Fill the new column with data which will fulfill the primary key constraint (unique/not null), e.g. like
```
UPDATE DEGREE SET Ident=ROWNUM;
```
Alter the table and add the constraint to the column
```
ALTER TABLE DEGREE MODIFY (Ident PRIMARY KEY);
```

After that is done, you can set up a SEQUENCE and a BEFORE INSERT trigger to automatically set the id value for new records.

Add a column to existing table and uniquely number them on MS SQL Server

This will depend on the database but for SQL Server, this could be achieved as follows:

alter table Example
add NewColumn int identity(1,1)

How to update Identity Column in SQL Server?

You can not update identity column.
SQL Server does not allow to update the identity column unlike what you can do with other columns with an update statement.

Although there are some alternatives to achieve a similar kind of requirement.

When Identity column value needs to be updated for new records

Use DBCC CHECKIDENT which checks the current identity value for the table and if it's needed, changes the identity value.

DBCC CHECKIDENT('tableName', RESEED, NEW_RESEED_VALUE)

When Identity column value needs to be updated for existing records

Use IDENTITY_INSERT which allows explicit values to be inserted into the identity column of a table.

SET IDENTITY_INSERT YourTable {ON|OFF}

Example:

-- Set Identity insert on so that value can be inserted into this column
SET IDENTITY_INSERT YourTable ON
GO
-- Insert the record which you want to update with new value in the identity column
INSERT INTO YourTable(IdentityCol, otherCol) VALUES(13,'myValue')
GO
-- Delete the old row of which you have inserted a copy (above) (make sure about FK's)
DELETE FROM YourTable WHERE ID=3
GO
--Now set the idenetity_insert OFF to back to the previous track
SET IDENTITY_INSERT YourTable OFF

SQL Server inserting huge number of rows to a table with default values and identity column in it

Grab a hold of 6400000 rows from somewhere and insert them all at once.

insert into BigList(Is_It_Occupied)
select top(6400000) 0
from sys.all_objects as o1
  cross join sys.all_objects as o2
  cross join sys.all_objects as o3

Did some testing on how long time the different solutions took on my computer.

Solution                                           Seconds
-------------------------------------------------- -----------
Mikael Eriksson                                    13
Naresh                                             832
Dd2                                                25
TToni                                              92
Milica Medic                                       90
marc_s                                             2239

SQL Server: how to add new identity column and populate column with ids?

Just do it like this:

ALTER TABLE dbo.YourTable
ADD ID INT IDENTITY(1,1)

and the column will be created and automatically populated with the integer values (as Aaron Bertrand points out in his comment - you don't have any control over which row gets what value - SQL Server handles that on its own and you cannot influence it. But all rows will get a valid int value - there won't be any NULL or duplicate values).

Next, set it as primary key:

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

Convert an existing Column to Identity

As you are using SQL Server 2012, another possible alternative could be to create a sequence object that has a starting value of the highest ID +1 already in your table, then create a default constraint for your column using GET NEXT VALUE FOR and reference your sequence object you just created.

SQL Server add auto increment primary key to existing table

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

How do I add the identity property to an existing column in SQL Server

I don't beleive you can do that. Your best bet is to create a new identity column and copy the data over using an identity insert command (if you indeed want to keep the old values).

Here is a decent article describing the process in detail:
http://www.mssqltips.com/tip.asp?tip=1397