Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


IDENTITY


IDENTITY

Author
Message
dost.kumar2
dost.kumar2
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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..
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5689 Visits: 25280
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
dost.kumar2
dost.kumar2
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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:-
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5689 Visits: 25280
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
dost.kumar2
dost.kumar2
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 34
WHEN I M DOING.. THAN..
MY ALL COLUMN TestID values Changes..
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5689 Visits: 25280
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
dost.kumar2
dost.kumar2
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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)
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5689 Visits: 25280
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search