This article contribution from one of my favorite SQL Expert Imran Mohammed. He is one man who has lots of ideas and helps people from all over the world with passion using this community as platform. His constant zeal to learn more about SQL Server keeps him engaging him to do new SQL Server related activity every time.
1. Adding Identity Property to an existing column in a table.
How difficult is it to add an Identity property to an existing column in a table? Is there any T-SQL that can perform this action?
For most, the answer to the above two questions is an absolute NO! There is no straightforward T-SQL like ALTER TABLE… or MODIFY COLUMN to add an 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.
Are you finding my answer difficult to believe? Let me explain.
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.
This table is vacant, with no records, as you can see in the following screenshot.
Take a look at the design of this table in SSMS.
Now let us make eid, 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.
Unfortunately, I cannot expand this dialogue box further to show you the complete script, so I have copied this script below.
/* 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
CREATE TABLE dbo.Tmp_example1
(
eid INT NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_example1 ON
GO
IF EXISTS(SELECT * FROM dbo.example1)
EXEC('INSERT INTO dbo.Tmp_example1 (eid)
SELECT eid FROM dbo.example1 WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_example1 OFF
GO
DROP TABLE dbo.example1
GO
EXECUTE sp_rename N'dbo.Tmp_example1', N'example1', 'OBJECT'
GO
COMMIT
Let us understand above code in simple words:
Begin Transaction
Step 1: Create similar table structure as example1, say Tmp_Example1 with Identity Column.
Step 2: Set IDENTITY_INSERT ON on new table just created.Step 3: Insert all values from example1 to Tmp_Example1
Step 4: Set IDENTITY_INSERT OFF on Tmp_Example..
Step 5: Drop example1 (What is going on… dropping our original table???)
Step 6: Rename Tmp_Example1 to Example1.
Commit Transaction
What if you have millions and millions of records in your table?
Imagine how much time it would take to copy an entire table?
Just to make a column Identity do you really need to go through all these and take so much pain?
Certainly not!
Same is the case when you want to remove Identity column property from a column.
2. Removing Identity Property from an existing column in a table.
/* 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
CREATE TABLE dbo.Tmp_example1
(
eid INT NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.example1)
EXEC('INSERT INTO dbo.Tmp_example1 (eid)
SELECT eid FROM dbo.example1 WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.example1
GO
EXECUTE sp_rename N'dbo.Tmp_example1', N'example1', 'OBJECT'
GO
COMMIT
Once again, I would like to congratulate Imran Mohammed for this wonderful article.
Reference : Pinal Dave (https://blog.sqlauthority.com)
107 Comments. Leave new
ALTER TABLE dbo.[MyTable] ADD NewID INT IDENTITY(1,1)
SET IDENTITY_INSERT dbo.[MyTable] ON
UPDATE dbo.[MyTable]
SET NewID
= ID
sp_RENAME ‘[MyTable].[NewID]’ , ‘[ID]’, ‘COLUMN’
SET IDENTITY_INSERT dbo.[MyTable] OFF
When it tries the update I get:
Msg 8102, Level 16, State 1, Line 1
Cannot update identity column ‘NewID’.
What am I missing? I too, want the “certainly not” option. ;)
You cannot update a identity column
I found the solution for this update problem
…………………………………
sp_configure ‘allow update’, 1
go
reconfigure with override
go
update syscolumns set colstat = colstat – 1
where id = object_id(‘TB_EmployeeMaster’)
and name = ‘EmpId’
go
exec sp_configure ‘allow update’, 0
go
reconfigure with override
go
update TB_EmployeeMaster set EmpId=newEmpId
go
sp_configure ‘allow update’, 1
go
reconfigure with override
go
update syscolumns set colstat = colstat + 1
where id = object_id(‘TB_EmployeeMaster’)
and name = ‘EmpId’
go
exec sp_configure ‘allow update’, 0
go
reconfigure with override
go
……………………………
EmpId is the identity field
newEmpId is the new field
sp_configure ‘allow update’, 1 means system table is allowed for editing
After updation use sp_configure ‘allow update’, 0
it means system table editing locked
Also identity field colstat is 1. We can change it to 0 for updation.
After updation set colstat as 1 for original state
before any changes in system table, we can set sp_configure ‘allow update’, 1
and after change we can set
sp_configure ‘allow update’, 0
‘reconfigure with override’ use change to take immediate effect without sql server restarted
note that sp_configure change server level settings
improper use of ‘sp_configure’ may lead to DB crash.
Hi ,
Am getting below error when am trying to change the colstat in syscolumn table
Ad hoc updates to system catalogs are not allowed.
Pl suggest what can be done to update identity column value.
Thanks in anticipation.
i m set identity_insert on but still show error
(:mssql2005)
MESSSAGE
create table temp_students(id int primary key identity,f_name varchar(30))
SET IDENTITY_INSERT dbo.temp_students ON
insert into temp_students(id) SELECT id FROM students
WITH (HOLDLOCK TABLOCKX)
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table ‘temp_students’ when IDENTITY_INSERT is set to OFF.
SET IDENTITY_INSERT dbo.temp_students OFF
DROP TABLE students
sp_rename temp_students,students
Caution: Changing any part of an object name could break scripts and stored procedures.
Create similar table structure as example, say Tmp_Example with Identity Column.
while fire
SELECT *
INTO temp_Example
FROM Example
WHERE 1=2
but constarint not copy onlu table structure copy
Hi im using Visual Studio 2008 & MS SQL SERVER Management Studio Express
i don’t know where is my error
Please help me out?
THIS IS MY REGISTER.
Imports System.Data.Sql
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
‘Dim connection As String = ConfigurationManager.ConnectionStrings(“Data Source=JED-PC;Initial Catalog=comicon;Trusted_Connection=True”).ConnectionString
Using cn As New SqlConnection
cn.ConnectionString = “Data Source=JED-PC;Initial Catalog=comicon;Trusted_Connection=True”
Dim c As String
c = “INSERT INTO Logins(First_Name,Last_Name,Password,Address,Contact_No,Email,Account) VALUES (‘” & TextBox1.Text.Trim & “‘,’” & TextBox2.Text.Trim & “‘,’” & TextBox3.Text.Trim & “‘,’” & TextBox5.Text.Trim & “‘” & “, ‘” & TextBox6.Text.Trim & “‘,’” & TextBox7.Text.Trim & “‘,’1′ )”
Dim sqlcmd1 As New Data.SqlClient.SqlCommand
sqlcmd1.Connection = cn
Try
cn.Open()
sqlcmd1.CommandText = c
sqlcmd1.ExecuteNonQuery()
cn.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
If TextBox6.Text.Length < 7 Then
MsgBox("contact # is too short")
Else
Button1.Visible = True
MsgBox("Your Successfully Registered", 6, "Confirmation Message")
Response.Redirect("Login.aspx")
End If
End Using
End Sub
End Class
(When I Submit it won’t redirect to other..)
Imports System.Data.Sql
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connection As String = ConfigurationManager.ConnectionStrings(“Data Source=JED-PC;Initial Catalog=comicon;Trusted_Connection=True”).ConnectionString
Using cn As New SqlConnection
Dim Account As String
Dim login As String
login = “Select count(*) from Logins where Email = ‘” & TextBox1.Text & “‘ and Password = ‘” & TextBox2.Text & “‘”
Dim sqlcmd1 As New SqlCommand(login, cn)
Try
cn.Open()
Account = sqlcmd1.ExecuteScalar
If Account = 1 Then
sqlcmd1.CommandText = login
Account = sqlcmd1.ExecuteScalar()
cn.Close()
Select Case Account
Case 0
MsgBox(“welcome admin”)
Session(“Logins”) = TextBox1.Text
Response.Redirect(“/planetcomic/user/admin/Home.aspx”)
Case 1
MsgBox(“You are Succesfully Login”)
Session(“Logins”) = TextBox2.Text()
Response.Redirect(“/planetcomic/user/customer/Home.aspx”)
Case Else
Label6.Text = “Invalid Login or Password”
TextBox1.Text = “”
TextBox2.Text = “”
Label6.Visible = True
cn.Close()
‘If sqlcmd1.ExecuteScalar = “1″ Then
‘Session(“Login”) = TextBox1.Text
‘Response.Redirect(“/planetcomic/user/customer/Home.aspx”)
End Select
End If
Catch ex As Exception
MsgBox(“Welcome to Planetx”)
End Try
End Using
End Sub
End Class
I am running this script on SQL Server 2000.
I am not sure about on SQL Server 2005
Pinal,
Let me know how to do the same with 2008 R2. I’m facing issues when I set the Identity Specification and save the table.
Thank you so much! You saved my day!
Keep on the great work!
Pinal / Imran,
Thanks for this wonderful article. Just one thing needs to be mentioned.
I ran into following error :
” Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.”
and then when i googled, following link helped me with solution.
HI Pinal,
I want to add a column in select statement which is rank. And Rank should contain function of identity. Is this possible . If yes plz let me know.
Thanks,
Regards,
Avinash
Yes. This article explains what you want
Hi Really U r Great Ur Suggestion is working fine thnks Oops
you have created a new table with non-identity coln and renaming it as the table is small and have one column but do you think this is the best practice for a table with lot of indexes and constraints?
why a new/temp table?
why not a new/temp column?
Hi,
how to concatenate string in identity column.
for eg:-
emp1
emp2
emp3
i want to increase the identity column like this…………….
I want those row from a table who has value only 1 in In_Store. If value is 0 in In_store for a Item_no. Then those item_no should not displayed.
Result i want :— Item_no In_store
2 1
Ex:- Item_no In_store
1 0
1 1
2 1
please give me ur suggestion.
Regards,
Avinash Kumar
Item_no In_Store
2 1
I want this result from the Displayed Table below,
Item_no In_store
1 0
1 1
2 1
Means to say if value is 0 for any item then those item should not display.
Please give me your valuable suggestion.
Regards,
Avinash Kumar
Hİ. I want delete column ıdentity property.
I’m not delete
SET IDENTITY_INSERT xxx OFF
please. help me!
I want delete :)
how do you drop & re-create if the column is the foreign key of another table.? and still need to remove the identity function
You can drop the constraint first and then re-enable later
Hello all,
Please help me…
My table is :-
Product Qty
P1 3
P2 2
and I want the result like:-
P1 1
P1 1
P1 1
P2 1
P2 1
Please suggest me the query.
Regards,
Avinash kumar
Please give the answer if any one know about this question.
order_day prod_id Quantity price
18-10-11 P1 2 25
18-10-11 P2 3 10
18-10-11 P3 5 5
18-10-11 P4 3 25
18-10-11 P5 4 20
18-10-11 P6 4 30
18-10-11 P1 4 25
19-10-11 P1 2 25
19-10-11 P3 3 10
19-10-11 P4 4 15
19-10-11 P2 1 15
Question:- Select the product which is sold on 18th & 19th and how many time on each day.
Output Should be like this:-
Product Count
P1 2
P1 1