ALTER COLUMN fails when changing datatype of PRIMARY KEY

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » ALTER COLUMN fails when changing datatype of...

ALTER COLUMN fails when changing datatype of PRIMARY KEY

Rate Topic

Display Mode

Topic Options

Author

Message

mambion

Posted Monday, September 10, 2012 9:32 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:47 PM
Points: 7, Visits: 219

I am currently working on a transaction table that has an IDENTITY column of type INT, which also has a PRIMARY KEY constraint. Table also has one filtered index. The volume in this table is starting to pick-up and we're afraid this field might run out. I was hoping to change the data type to BIGINT without creating a temp table. I applied the following script:

SET ANSI_WARNINGS ON

ALTER TABLE [dbo].[TxRecords]
DROP CONSTRAINT [PK_TxRecords]

--change data type
ALTER TABLE [dbo].[TxRecords]
ALTER COLUMN [tx_id] BIGINT NOT NULL

--add primary key
ALTER TABLE [dbo].[TxRecords]
ADD CONSTRAINT [PK_TxRecords] PRIMARY KEY ([tx_id])

After executing above script, I got the following error message:

Msg 1934, Level 16, State 1, Line 5
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.

I checked the session properties and database properties and ANSI_WARNINGS is ON. Only way I could successfully run this is by dropping the filtered index and then recreating this again after the datatype of the IDENTITY column had been updated.

Is there anyone who may have experienced this and was able to come up with a resolution without dropping the filtered index?

Post #1357120

HowardW

Posted Wednesday, September 12, 2012 3:40 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 5:55 AM
Points: 1,034, Visits: 7,660

I suspect that altering the column to another data type internally requires that ANSI_WARNINGS be set to OFF, so it's overriding the setting.

In your case, you should be dropping all non-clustered indexes prior to dropping the Clustered index (PK) anyway, otherwise internally it's having to rebuild these indexes twice (once to move the pointers to the heap and then again to move them back to the new clustered index), so you'd actually be making the process slower by not dropping your filtered index and all other Non-Clustered indexes first.

Post #1357863

CELKO

Posted Wednesday, September 12, 2012 8:36 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

>> I am currently working on a transaction table that has an IDENTITY column of type INTEGER, which also has a PRIMARY KEY constraint. <<

Where is the DDL? Why do you think that the count of physical insertion attempts can ever be a logical key in RDBMS? fields and columns are totally different concepts.

>> I was hoping to change the data type to BIGINT without creating a temp table. I applied the following script: <<

Do you realistically expect to write to this table more times that the number of atoms in the Universe? Why don't you drop the proprietary, non-relational IDENTITY property (it is not even a column) and start using a valid, relational key? Since you gave no DDL and no specs, we cannot guess what that would be.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Post #1358303

Eugene Elutin

Posted Thursday, September 13, 2012 3:53 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:59 AM
Points: 2,525, Visits: 4,324

...
Do you realistically expect to write to this table more times that the number of atoms in the Universe?
...

Sorry, I completely forgot this one. Could you please remind me how many atoms are in the Universe?

...Why don't you drop the proprietary...

I guess, it is because there is no one here have a need for such "portability", but everyone wants to use features of the product they have chosen to use and paid money for. Is it good enough reason?

...
non-relational IDENTITY property (it is not even a column) and start using a valid, relational key?
...

Please help me! I have two tables and I struggle to choose a valid, relational key for them:

1. Table "Person" - holds personal details from customers around the world. I know you are expert in ISO, so may be you know if all different Gods from all religions have finally come up with agreement on the International Standard for marking people?

2. Table "Companies" - I could use some Company Identification Number/Code from US, but again, I have some companies registered elsewhere and they not "public" enough to be listed on any exchange. Is any international body who issues globally recognised and unique Company Key?

Any idea anyone?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1358441

Luis Cazares

Posted Thursday, September 13, 2012 7:33 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 3:26 PM
Points: 958, Visits: 1,917

Eugene Elutin (9/13/2012)

...
Do you realistically expect to write to this table more times that the number of atoms in the Universe?
...

Sorry, I completely forgot this one. Could you please remind me how many atoms are in the Universe?

Two approximate calculations give the number of atoms in the observable universe to be close to 10^80 (much more than bigint capabilities).

The observable universe contains between 10^22 and 10^24 stars (not even to store all the stars.

Luis C.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help

Post #1358549

ScottPletcher

Posted Thursday, September 13, 2012 5:08 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:38 PM
Points: 1,318, Visits: 1,763

Is the PK also a clustered index?

Is the identity column used in any another index?

Please post the DDL for the filtered index.

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson

Post #1358981

« Prev Topic | Next Topic »

Permissions