September 10, 2012 at 9:32 pm
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?
September 12, 2012 at 3:40 am
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.
September 13, 2012 at 3:53 am
...
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?
September 13, 2012 at 7:33 am
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.
September 13, 2012 at 5:08 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply