IDENTITY

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Absinthe » IDENTITY

IDENTITY

Rate Topic

Display Mode

Topic Options

Author

Message

dost.kumar2

Posted Monday, June 30, 2008 4:23 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 3:50 AM
Points: 10, Visits: 34

Ex:--
create table testing1
(
cID int identity(1,1) not null,
cname varchar(50)

)

Add 10 record........ after that.. 4 or 8 record delete..
My table like that.....
CId cName
1 Sunil
2 Sunil
3 Sunil
5 Sunil
6 Sunil
7 Sunil
9 Sunil
10 Sunil

than IDENTITY is off....
i want change the CId IDENTITY (1,3)..
how can i alter the table..

Post #525789

bitbucket-25253

Posted Tuesday, July 01, 2008 8:06 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 5,102, Visits: 20,204

You could using SSMS
a). In design mode drop the column cID
b). Save the table
c). Go back into design mode insert a new column named cID
d). Set the column data type to int, do not allow NULLS
e). In the lower frame (column properties) expand "Identity specification" and then set (Is identity) to Yes and input values for "Identity increment" and "identity seed"
f). Close and save the table design window.

This should do it.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read
Before posting a performance problem please read

Post #526656

dost.kumar2

Posted Wednesday, July 02, 2008 1:00 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 3:50 AM
Points: 10, Visits: 34

My dear frnd thanks for reply.....

i m not using enterprise Manager..

how can i do in query analyzer.

send me some exp:-

Post #527053

bitbucket-25253

Posted Wednesday, July 02, 2008 6:28 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 5,102, Visits: 20,204

Using Query Analyzer

ALTER TABLE testing1 DROP COLUMN Cid
GO

ALTER TABLE testing1 ADD Cid INT IDENTITY (1,3) NOT NULL
GO

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read
Before posting a performance problem please read

Post #527236

dost.kumar2

Posted Thursday, July 03, 2008 2:28 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 3:50 AM
Points: 10, Visits: 34

WHEN I M DOING.. THAN..
MY ALL COLUMN TestID values Changes..

Post #527841

bitbucket-25253

Posted Thursday, July 03, 2008 6:20 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 5,102, Visits: 20,204

dost.kumar your original question was:

i want change the CId IDENTITY (1,3)..
how can i alter the table..

which is what resulted following my suggestion.
Do you want your existing entries to maintain the values they had before changing the identity increment, with only new values having the new identity values or?
Can you provide an example of what you want as your result?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read
Before posting a performance problem please read

Post #528441

dost.kumar2

Posted Friday, July 04, 2008 2:09 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 3:50 AM
Points: 10, Visits: 34

create table testing1
(
cID int identity(1,1) not null,
cname varchar(50)

)

select *From testing1
insert into testing1 values ('sunil2')
insert into testing1 values ('sunil6')
insert into testing1 values ('sunil7')
insert into testing1 values ('sunil8')
insert into testing1 values ('sunil9')
insert into testing1 values ('sunil10')
insert into testing1 values ('sunil11')

Result
cID cname
----------- --------------------------------------------------
1 sunil
2 sunil6
3 sunil7
4 sunil8
5 sunil9
6 sunil10
7 sunil11

than delete record 2,4,6

Than Result
cID cname
----------- --------------------------------------------------
1 sunil
3 sunil7
5 sunil9
7 sunil11

than Drop column Cid and create again

ALTER TABLE testing1 DROP COLUMN Cid
GO

ALTER TABLE testing1 ADD Cid INT IDENTITY (1,3) NOT NULL
GO
select *from testing1

result
cname Cid
-------------------------------------------------- -----------
sunil 1
sunil7 4
sunil9 7
sunil11 10

(4 row(s) affected)

Post #528537

bitbucket-25253

Posted Friday, July 04, 2008 6:52 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 5,102, Visits: 20,204

If the result:
cname Cid
---------- -----------
sunil2 1 |
sunil7 3 |--- Values after deleting Cid values 2,4,6
sunil9 5 |
sunil11 7 |
sunil20 10 |
sunil60 13 | --- Cid values after altering the
sunil70 16 | identity increment using
sunil80 19 | Management Console
sunil90 22 |
sunil100 25 |
sunil110 28 |

If this is what you want the only way to alter the identity increment is to use the management console. Open the table in design view and alter the identity increment, close and save the revised table.
To the best of my knowledge there is no way to use T-SQL commands to accomplish the results as shown.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read
Before posting a performance problem please read

Post #528657

« Prev Topic | Next Topic »

Permissions