how can i remove identity properity

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how can i remove identity properity

Author

Topic

js.reddy
Yak Posting Veteran

80 Posts

Posted - 2008-09-25 : 05:40:56

Hi guys,

I want to remove identiy properity of the column by query.

can any one help me!!!!!!!!

Thanks
Js.reddy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 05:43:51

why should you remove identity property?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 05:47:33

i dont think you can drop identity property directly. you could however do a work around

1.Create a new column without identity property
2.copy all existing data from identity column to this new column
3.Recreate all constraints on identity column on this column
4.drop the constraints on identity column
5.drop the identity column itself.

js.reddy
Yak Posting Veteran

80 Posts

Posted - 2008-09-25 : 05:49:56

Thank You.

quote:
Originally posted by visakh16

i dont think you can drop identity property directly. you could however do a work around

1.Create a new column without identity property
2.copy all existing data from identity column to this new column
3.Recreate all constraints on identity column on this column
4.drop the constraints on identity column
5.drop the identity column itself.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 05:51:05

other way is to go to SQL Management studio.RIGHT-CLICK on the table in SQL Server Management Studio, select Design and highlight the column you wanted to change. Then, go to the Column Properties and scrolled down to Identity Specification and changed (Is Identity) to 'No

js.reddy
Yak Posting Veteran

80 Posts

Posted - 2008-09-25 : 05:56:46

This is ok boss.
but i want to do this through query only.

Thanks once again.

quote:
Originally posted by visakh16

other way is to go to SQL Management studio.RIGHT-CLICK on the table in SQL Server Management Studio, select Design and highlight the column you wanted to change. Then, go to the Column Properties and scrolled down to Identity Specification and changed (Is Identity) to 'No

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 06:02:04

quote:
Originally posted by js.reddy

This is ok boss.
but i want to do this through query only.

Thanks once again.

quote:
Originally posted by visakh16

other way is to go to SQL Management studio.RIGHT-CLICK on the table in SQL Server Management Studio, select Design and highlight the column you wanted to change. Then, go to the Column Properties and scrolled down to Identity Specification and changed (Is Identity) to 'No

still you didnt answer my first question. why you're dropping id property??

js.reddy
Yak Posting Veteran

80 Posts

Posted - 2008-09-25 : 06:13:47

right now i don't want identity properity.
becuase i want to insert some duplicate values into this column.

Thanks

quote:
Originally posted by visakh16

quote:
Originally posted by js.reddy

This is ok boss.
but i want to do this through query only.

Thanks once again.

quote:
Originally posted by visakh16

other way is to go to SQL Management studio.RIGHT-CLICK on the table in SQL Server Management Studio, select Design and highlight the column you wanted to change. Then, go to the Column Properties and scrolled down to Identity Specification and changed (Is Identity) to 'No

still you didnt answer my first question. why you're dropping id property??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 06:19:56

But you dont have to drop identity property for that. you can just use SET IDENTITY_INSERT tablename On and insert duplicated values which you wanted, provided you dont have a UNIQUE constraint or PRIMARY KEY constraint on your column.

js.reddy
Yak Posting Veteran

80 Posts

Posted - 2008-09-25 : 06:27:42

Thanks. This is what i need.

quote:
Originally posted by visakh16

But you dont have to drop identity property for that. you can just use SET IDENTITY_INSERT tablename On and insert duplicated values which you wanted, provided you dont have a UNIQUE constraint or PRIMARY KEY constraint on your column.

js.reddy
Yak Posting Veteran

80 Posts

Posted - 2008-09-25 : 06:31:19

Is there any way to drop identity property permanently!!!!

quote:
Originally posted by visakh16

But you dont have to drop identity property for that. you can just use SET IDENTITY_INSERT tablename On and insert duplicated values which you wanted, provided you dont have a UNIQUE constraint or PRIMARY KEY constraint on your column.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 06:39:59

quote:
Originally posted by js.reddy

Is there any way to drop identity property permanently!!!!

quote:
Originally posted by visakh16

But you dont have to drop identity property for that. you can just use SET IDENTITY_INSERT tablename On and insert duplicated values which you wanted, provided you dont have a UNIQUE constraint or PRIMARY KEY constraint on your column.

thats what i suggested on Posted - 09/25/2008 : 05:51:05 and Posted - 09/25/2008 : 05:47:33

js.reddy
Yak Posting Veteran

80 Posts

Posted - 2008-09-25 : 07:04:17

Thank you so much for your replaying.

quote:
Originally posted by visakh16

quote:
Originally posted by js.reddy

Is there any way to drop identity property permanently!!!!

quote:
Originally posted by visakh16

But you dont have to drop identity property for that. you can just use SET IDENTITY_INSERT tablename On and insert duplicated values which you wanted, provided you dont have a UNIQUE constraint or PRIMARY KEY constraint on your column.

thats what i suggested on Posted - 09/25/2008 : 05:51:05 and Posted - 09/25/2008 : 05:47:33

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources