Share via

We can not modify identity column type when table contains a persisted computed column. (Msg 1934, Level 16, State 1, Line 1)

  • Article

We were trying to modify IDENTITY column type to BIGINT in SQL server 2012 and we got the below error.

Msg 1934, Level 16, State 1, Line 1

ALTER TABLE failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

We made sure that 'ANSI_WARNINGS' is set to ON from session and database perspective , We made sure that Triggers are disabled from table, database, server side.(for our troubleshooting perspective we deleted all indexes on that table as well). but still we were getting above error.

and that column we are trying to alter is not used in any another computed columns in the table.

Repro steps:-

SQL server 2012

CREATE TABLE [dbo].table1([column1] [int] IDENTITY(30000000,1) NOT NULL,column2 int null,column3 as(column2) persisted)
alter table table1 alter column [column1] bigint not null

Above repro we have done in SQL server 2012, later we thought lets test it in higher version, we tested in SQL server 2014

SQL server 2014

When executed the same steps in SQL server 2014 we got different error message , like below.

Msg 11424, Level 16, State 1, Line 1

Cannot alter the identity column 'column1' in the table 'table1' because this operation requires data modification and the table contains a persisted computed column. Remove the persisted computed column before modifying the identity column.

Actually SQL server 2014 gives the right error message, 2012 error is misleading us. So in this case ideally we need to drop all PERSISTED COMPUTED columns in the table and change the IDENTITY column type and then add all computed columns.

remember you will not have this problem if either table has no persisted columns or you are not changing the IDENTITY column type.