Adding an identity to an existing column

You cannot alter a column to be an IDENTITY column. What you'll need to do is create a new column which is defined as an IDENTITY from the get-go, then drop the old column, and rename the new one to the old name.

ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

EXEC sp_rename 'yourTable.NewColumn', 'OldColumnName', 'COLUMN'

Marc

As per my current condition, I follow this approach. I want to give identity to a primary table after data inserted via script.

As I want to append identity, so it always start from 1 to End of record count that I want.

--first drop column and add with identity
ALTER TABLE dbo.tblProductPriceList drop column ID
ALTER TABLE dbo.tblProductPriceList add ID INT IDENTITY(1,1)

--then add primary key to that column (exist option you can ignore)
IF NOT EXISTS (SELECT * FROM sys.key_constraints WHERE object_id = OBJECT_ID(N'[dbo].[PK_tblProductPriceList]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProductPriceList]'))
ALTER TABLE [tblProductPriceList] ADD PRIMARY KEY (id)
GO

This will create the same primary key column with identity

I used this links :

[To see links please register here]

[To see links please register here]

Right click on table name in Object Explorer. You will get some options. Click on 'Design'. A new tab will be opened for this table. You can add Identity constraint here in 'Column Properties'.

To modify the identity properties for a column:

- In Server Explorer, right-click the table with identity properties you want to modify and click Open Table Definition.
The table opens in Table Designer.
- Clear the Allow nulls check box for the column you want to change.
- In the Column Properties tab, expand the Identity Specification property.
- Click the grid cell for the Is Identity child property and choose Yes from the drop-down list.
- Type a value in the Identity Seed cell. This value will be assigned to the first row in the table. The value 1 will be assigned by default.


That's it, and it worked for me

I'm a java developer that happened to get on a team without a DBA and one where as a developer, I can't get DBA rights. I was tasked with moving an entire schema between two databases, so without having a DBA, I had to do it and do it by running scripts, not being able to use the GUI in SQL Server 2008 because I didn't have admin privileges.

Everything was moved without issue, however, when running a stored procedure on the new schema.table, I found I lost the identity field in a table. I double checked the script that created the table and it was there, however, SQL Server didn't get it when I ran the script. I was told later by a DBA that he had seen this same problem before.

In any event, for SQL Server 2008, these are the steps I took to get this resolved and they worked, so I'm posting this here in the hopes it will be a help to someone. This is what I did as I had FK dependencies on another table that made this more difficult:

I used this query to verify the identity was indeed missing and to view dependencies on the table.

1.) Find statistics on a table:

exec sp_help 'dbo.table_name_old';

2.) Create a duplicate, identical new table, except add an identity field on the PK field where it had been before.

3.) Disable the identity to move data.

SET IDENTITY_INSERT dbo.table_name ON

4.) Transfer the data.

INSERT INTO dbo.table_name_new
(
field1, field2, etc...
)
SELECT
field1, field2, etc...
FROM
dbo.table_name_old;

5.) Verify the data is there.

SELECT * FROM dbo.table_name_new

6.) Re-enable the identity.

SET IDENTITY_INSERT ToyRecP.ToyAwards.lkpFile_New OFF

7.) **This is the best script I found to get all the FK relationships to verify which table(s) the original table references as dependencies
and I came across many, so it is a keeper!**

SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
ORDER BY ReferenceTableName;


8.) Make sure you have all the PK and FK scripts for all the tables involved, before this next step.

9.) You can right-click on each key and script this using SQL Server 2008

10.) Drop the FK(s) from the dependency table(s) using this syntax:

ALTER TABLE [dbo].[table_name] DROP CONSTRAINT [Name_of_FK]

11.) Drop the original table:

DROP TABLE dbo.table_name_old;

13.) These next steps rely on the scripts you created in SQL Server 2008 in step 9.

--Add the PK to the new table.

--Add the FK to the new table.

--Add the FK's back to the dependency table.

14.) Verify everything is correct and complete. I used the GUI to look at the tables.

15.) Rename the new table to the original tables name.

exec sp_RENAME '[Schema_Name.OldTableName]' , '[NewTableName]';

Finally, everything worked!

As I understood in normal cases we are creating a table with **Primary key** which is having **Identity property**<br/>
So **Rename** or **Delete** a column which is associated with **Primary Key** **constraint** will not be possible because constraint Rules are validating column structure.<br/>
Tto achieve this we have to process some steps in the following way:
Let us assume **TableName = 'Employee'** and **ColumnName = 'EmployeeId'**
<br/>
<br/>
1. Add new column 'EmployeeId_new' in the 'Employee' table
<br/>
**ALTER TABLE Employee ADD EmployeeId_new INT IDENTITY(1,1)**

2. Now remove column 'EmployeeId' from 'Employee' table
<br/>
**ALTER TABLE Employee DROP COLUMN EmployeeId**

3. This will throw error because of Primary Key Constraint rules are applicable and validating column structure.
<br/>
*###
'***Msg 5074, Level 16, State 1, Line 1 The object [PK_dbo.Employee] is dependent on colmn [EmployeeId].'***###

4. So we have to remove the Primary Key constraint first from the table 'Employee' then we can remove the column
<br/>
**ALTER TABLE Employee DROP constraint [PK_dbo.Employee]**

5. Now we can remove the column 'EmployeeId' from 'Employee' table as did in the previous step where we got error
<br/>
**ALTER TABLE Employee DROP COLUMN EmployeeId**

6. Now Column 'EmployeeId' removed from table
So we will Rename the newly added new column 'EmployeeId_new' with 'EmployeeId'
<br/>
**sp_rename 'Employee.EmployeeId', 'EmployeeId_new', 'COLUMN'**

7. To rearrange the table in the same form as It was, we have to add Primary Key Constraint for the column 'EmployeeId'
<br/>
**ALTER TABLE Employee add constraint [PK_dbo.Employee] primary key (EmployeeId)**

*8. **Now the table 'Employee' with 'EmployeeId' is modified for Identity rules along with existing primary key constraint***

If you happen to be using Visual Studio 2017+

1. In Server Object Explorer right-click on your table and select "view code"
2. Add the modifier "IDENTITY" to your column
3. Update

This will do it all for you.

You can't alter the existing columns for identity.


You have 2 options,

1. Create a new table with identity & drop the existing table

2. 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!

```sql
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.

```sql
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)][1]


[1]:

[To see links please register here]

In SQL 2005 and above, there's a trick to solve this problem without changing the table's data pages. This is important for large tables where touching every data page can take minutes or hours. The trick also works even if the identity column is a primary key, is part of a clustered or non-clustered index, or other gotchas which can trip up the the simpler "add/remove/rename column" solution.

Here's the trick: you can use SQL Server's [ALTER TABLE...SWITCH][1] statement to change the schema of a table without changing the data, meaning you can replace a table with an IDENTITY with an identical table schema, but without an IDENTITY column. The same trick works to add IDENTITY to an existing column.

Normally, [ALTER TABLE...SWITCH][1] is used to efficiently replace a full partition in a partitioned table with a new, empty partition. But it can also be used in non-partitioned tables too.

I've used this trick to convert, in under 5 seconds, a column of a 2.5 billion row table from IDENTITY to a non-IDENTITY (in order to run a multi-hour query whose query plan worked better for non-IDENTITY columns), and then restored the IDENTITY setting, again in less than 5 seconds.

Here's a code sample of how it works.

CREATE TABLE Test
(
id int identity(1,1),
somecolumn varchar(10)
);

INSERT INTO Test VALUES ('Hello');
INSERT INTO Test VALUES ('World');

-- copy the table. use same schema, but no identity
CREATE TABLE Test2
(
id int NOT NULL,
somecolumn varchar(10)
);

ALTER TABLE Test SWITCH TO Test2;

-- drop the original (now empty) table
DROP TABLE Test;

-- rename new table to old table's name
EXEC sp_rename 'Test2','Test';

-- update the identity seed
DBCC CHECKIDENT('Test');

-- see same records
SELECT * FROM Test;

This is obviously more involved than the solutions in other answers, but if your table is large this can be a real life-saver. There are some caveats:

- As far as I know, identity is the only thing you can change about your table's columns with this method. Adding/removing columns, changing nullability, etc. isn't allowed.
- You'll need to drop foriegn keys before you do the switch and restore them after.
- Same for WITH SCHEMABINDING functions, views, etc.
- new table's indexes need to match exactly (same columns, same order, etc.)
- Old and new tables need to be on the same filegroup.
- Only works on SQL Server 2005 or later
- I previously believed that this trick only works on the Enterprise or Developer editions of SQL Server (because partitions are only supported in Enterprise and Developer versions), but Mason G. Zhwiti in his comment below says that it also works in SQL Standard Edition too. I assume this means that the restriction to Enterprise or Developer doesn't apply to ALTER TABLE...SWITCH.

There's a good [article on TechNet][2] detailing the requirements above.

UPDATE - [Eric Wu][3] had a comment below that adds important info about this solution. Copying it here to make sure it gets more attention:

> There's another caveat here that is worth mentioning. Although the
> new table will happily receive data from the old table, and all the
> new rows will be inserted following a identity pattern, they will
> start at 1 and potentially break if the said column is a primary key.
> Consider running `DBCC CHECKIDENT('<newTableName>')` immediately after
> switching. See [msdn.microsoft.com/en-us/library/ms176057.aspx][4] for more
> info.

If the table is actively being extended with new rows (meaning you don't have much if any downtime between adding IDENTITY and adding new rows, then instead of `DBCC CHECKIDENT` you'll want to manually set the identity seed value in the new table schema to be larger than the largest existing ID in the table, e.g. `IDENTITY (2435457, 1)`. You might be able to include both the `ALTER TABLE...SWITCH` and the `DBCC CHECKIDENT` in a transaction (or not-- haven't tested this) but seems like setting the seed value manually will be easier and safer.

Obviously, if no new rows are being added to the table (or they're only added occasionally, like a daily ETL process) then this race condition won't happen so `DBCC CHECKIDENT` is fine.


[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

[4]:

[To see links please register here]

Consider to use [SEQUENCE instead of IDENTITY][1].

In SQL Server 2014 (I don't know about lower versions) you can do this simply, using sequence.

CREATE SEQUENCE sequence_name START WITH here_higher_number_than_max_existed_value_in_column INCREMENT BY 1;

ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT NEXT VALUE FOR sequence_name FOR column_name

From here:

[To see links please register here]

[1]:

[To see links please register here]