<div>
SET IDENTITY_INSERT [tblTestIDENT] ON<br />
insert into tblTestIDENT (col1, col2) values (10, 'test1')<br />
insert into tblTestIDENT (col1, col2) values (1001, 'test2')<br />
SET IDENTITY_INSERT [tblTestIDENT] OFF<br />
insert into tblTestIDENT (col2) values ( 'test3')<br />
select * from [tblTestIDENT]<br />

</div>


SET IDENTITY_INSERT [tblTestIDENT] ON
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]


<div>
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.<br />
<br />
Thanks
</div>


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

<div>
<div class="content wysiwyg-content">
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:<br />
<br />
CREATE TABLE [dbo].[tblTestIDENT] (<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Col1] [int] IDENTITY (1, 1) NOT NULL ,<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Col2] [varchar] (50) NULL <br />
) ON [PRIMARY]<br />
GO<br />
<br />
What effect would this have if I have say 100 million records in the table?<br />

</div>
</div>


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?


How can I disable and re-enable the IDENTITY property on a table column using T-SQL.

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.