DeMyu
asked on
How can I disable and re-enable the IDENTITY property on a table column using T-SQL.
I would like to be able to disable and re-enable the identity property on a column using T-SQL. Here is my sample table:
CREATE TABLE [dbo].[tblTestIDENT] (
[Col1] [int] IDENTITY (1, 1) NOT NULL ,
[Col2] [varchar] (50) NULL
) ON [PRIMARY]
GO
What effect would this have if I have say 100 million records in the table?
CREATE TABLE [dbo].[tblTestIDENT] (
[Col1] [int] IDENTITY (1, 1) NOT NULL ,
[Col2] [varchar] (50) NULL
) ON [PRIMARY]
GO
What effect would this have if I have say 100 million records in the table?
ASKER
Thank you for your reply. This does not actually remove the identity property of the column in Enterprise Manager. Is there a way to do this in T-SQL such that when I go into EM I wouldn't see the property on the column. I have to do this on a table with about 70 million rows.
Thanks
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
insert into tblTestIDENT (col1, col2) values (10, 'test1')
insert into tblTestIDENT (col1, col2) values (1001, 'test2')
SET IDENTITY_INSERT [tblTestIDENT] OFF
insert into tblTestIDENT (col2) values ( 'test3')
select * from [tblTestIDENT]