Identity is a property of table that automatically increment integer value of a column. For example a table has column name ‘id’ and this column generated id automatically id value, this done by identity property.
Adding Identity Property to an existing column in a table
However, there is an easy way to accomplish this action. It can be done through SSMS (SQL Server Management Studio).
Let’s first see what SSMS does in backend when you add Identity property on an existing column in any table.
Now, let’s create an example table for better understanding.
/*Create table*/
create table info
(
id int primary key,
[name] varchar(50) not null,
address varchar(50) not null
)
Take a look at the design of this table in SSMS.
Now let us make id, an Identity column.
This is very easy. All you have to do is just select Yes from the drop down list and you are done!
But before moving further let’s see what T-SQL SQL Server is using to make this change.
You will notice that T-SQL is used by SQL Server to make this change.
After you make the change for Identity property from No to Yes, on top in tools box, you will see Generate Change Script. This is the T-SQL Script that SQL Server will use to make this change.
After saving changes, insert records into table. Here we provide value for name and address column. Id column auto generate number for self.
insert into info values('xyz','india')
insert into info values('abc','pakistan')
Output
Besides id, name and address column one column is un-title, this is clustered index.
Adding Identity Property in a table
We can add identity property during creating table via query.
create table information
(
id int identity (1,1),
[name] varchar(50) not null,
address varchar(50) not null
)
Removing Identity Property from an existing column in a table
There is no easy way to do this. By design there is no simple way to turn on or turn off the identity feature for an existing column. The only clean way to do this is to create a new table and migrate your data.
To get this script use Management Studio to make the change and then right click in the designer and select "Generate Change Script".
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
COMMIT
BEGIN TRANSACTION
GO
use avi
CREATE TABLE dbo.tmp_info
(
id INT NOT NULL,
name varchar(50) NULL,
address varchar(50) null
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.info)
EXEC('INSERT INTO dbo.tmp_info (id, name,address)
SELECT id, name, address FROM dbo.info WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Test1
GO
EXECUTE sp_rename N'dbo.Tmp_Test1', N'Test1', 'OBJECT'
GO
COMMIT
Now drop the original table and rename the temporary table with original table name.
DROP TABLE dbo.info
GO
EXECUTE sp_rename N'dbo.tmp_info', N'info', 'OBJECT'
GO
Leave Comment