Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Identity Issue Expand / Collapse
Posted Wednesday, October 3, 2012 12:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
I have a Table where in i have to set Identity in it.... it has 1 Lakh records in it..
when i go to design mode of the table & try to set the Identity to YES... it gives the below error

'Record_1' table
- Saving Definition Changes to tables with large amounts of data could take a considerable amount of time. While changes are being saved, table data will not be accessible.
- Warning: One or more existing columns have ANSI_PADDING 'off' and will be re-created with ANSI_PADDING 'on'.

Please help

Every Dog has a Tail !!!!!
Post #1367413
Posted Wednesday, October 3, 2012 3:43 AM


Group: General Forum Members
Last Login: Yesterday @ 9:21 AM
Points: 2,751, Visits: 5,187
You can't add an Identity property to an existing field.
So the way the GUI will do that is:
Create a new table that does have the column with Identity property
Copying the data across from old table to new.
Delete old table
Rename new table & associated constraints (FK's etc.) to the original names.

While it's doing this, the tables will be locked so you can't make any data modifications.

If you hit the Generate Change Script button rather than save, you can see the script that does all of the above.

The ANSI Padding warning is because the new table will be created with SET ANSI_PADDING ON, whereas some or all of the old table columns were created with it off. Probably a good idea to move to ON (as OFF is being removed from SQL), but check on the difference in behaviour of the two and see if it affects you.

You can script out & do this manually, which will give you more control on how long the table is unavailable.
Post #1367498
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse