change identity of a column - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

change identity of a column

Author

Topic

nitin05.sharma
Starting Member

20 Posts

Posted - 2011-05-26 : 04:41:26

hi all,

i have a table in this one column is recordid and we assined primary key on this and set the identity (1,1) but now i want to set the such type of identity that the output should be

1,2,3,4,5,10,20,40,60,100,200,300 like that

so please help me

thanks in advance

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-26 : 04:43:55

You can set the step on an identity column but it would mean recreating the column and changing the step multiple times.
Usually an identity isn't used for a meaningful value. Maybe you could use a computed column or view to calculate this value based on the identity.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-05-26 : 04:50:25

quote:
Originally posted by nitin05.sharma

hi all,

i have a table in this one column is recordid and we assined primary key on this and set the identity (1,1) but now i want to set the such type of identity that the output should be

1,2,3,4,5,10,20,40,60,100,200,300 like that

so please help me

thanks in advance

identity property is assigned on a column to store the data in a sequence...but i think you requirement is to display the data in the order you have posted...your output depends on your query logic..not by reseeding the identity on column...

jfarrugia
Yak Posting Veteran

55 Posts

Posted - 2011-05-26 : 05:40:18

if i am understanding you correct, your current increment is 1, i.e. your keys will look something like 1,2,3,4,5 but now you want them to look like 50,60,70,80.. if thats the case, trying using Identity Increment under Identity Specification in column properties.. and set it to 10.

Hope it helps

Where software development knowledge meets the reader

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-26 : 05:53:59

quote:
Originally posted by jfarrugia

if i am understanding you correct, your current increment is 1, i.e. your keys will look something like 1,2,3,4,5 but now you want them to look like 50,60,70,80.. if thats the case, trying using Identity Increment under Identity Specification in column properties.. and set it to 10.

Hope it helps

Where software development knowledge meets the reader

Don't think so (and that's already been covered) -
>> 1,2,3,4,5,10,20,40,60,100,200,300

Looks like
1-5, step 1
10-50,(60 a mistake) step 10
100-500 step 100

Another issue is that identities can have gaps and this loks like you want the gaps filled in?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nitin05.sharma
Starting Member

20 Posts

Posted - 2011-05-26 : 06:09:32

hi all
thanks for reply

My question is when first time i create a table at that time i set the identity (1,1) but inserting five row i want to change the identity dynamically how can i do that

means
when five rows inserted into table then rowid is 1,2,3,4,5
but i want to change the identity with diffrence of five like
1,2,3,4,5,10,15,20 like that

jfarrugia
Yak Posting Veteran

55 Posts

Posted - 2011-05-26 : 06:11:28

exactly.. you need to add an identity increment of 5 then

Where software development knowledge meets the reader

nitin05.sharma
Starting Member

20 Posts

Posted - 2011-05-26 : 06:14:15

hi,
thanks for your reply
but how
can u give querry or any other procedure

jfarrugia
Yak Posting Veteran

55 Posts

Posted - 2011-05-26 : 06:17:31

right click on the table in object explorer... select design.. select the column in question and look for identity increment in column properties

Where software development knowledge meets the reader

jfarrugia
Yak Posting Veteran

55 Posts

Posted - 2011-05-26 : 06:18:38

alternatively:

ALTER TABLE MyCustomers
ALTER COLUMN CustId IDENTITY (5, 5)

Where software development knowledge meets the reader

nitin05.sharma
Starting Member

20 Posts

Posted - 2011-05-26 : 06:21:42

hi
thanks

but i don't know that last is five that can be 6,7,8 etc

jfarrugia
Yak Posting Veteran

55 Posts

Posted - 2011-05-26 : 06:23:11

DECLARE @Last AS INT

SELECT @LAST = MAX(ID) FROM MyTable -- this will give you last id

ALTER TABLE MyTable
ALTER COLUMN IDIDENTITY (@Last, 5)

Where software development knowledge meets the reader

nitin05.sharma
Starting Member

20 Posts

Posted - 2011-05-26 : 06:24:11

hi
is that possible
first five row incremented by 1 and next five incremented by 5
and next incrmented by 10

jfarrugia
Yak Posting Veteran

55 Posts

Posted - 2011-05-26 : 06:26:21

you'll have to change the identity properties after you reach ID 5.. it will only allow you to apply 1 "rule" at a time.

Where software development knowledge meets the reader

nitin05.sharma
Starting Member

20 Posts

Posted - 2011-05-26 : 06:28:02

hi,

thanks

can i change programmatically

jfarrugia
Yak Posting Veteran

55 Posts

Posted - 2011-05-26 : 06:45:36

not sure i understood your question, but the script i originally posted is the programmatic way of modifying the identity increment.. but as i said, you can not apply a rule stating.. use seed of 1 until 5 then change to increment of 5.. to do that you'll need to manually run the script or else, which is not something i will suggest .. its theoretical and i have not tested it.

create a trigger on the table, on insert check for primary key value.. if its 5, then run the script i gave you with the modification of the identity increment.. cant understand why you need to apply this rule as its illogical and doesnt seem to have any use at all.

Where software development knowledge meets the reader

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-26 : 06:50:12

quote:
Originally posted by nitin05.sharma

hi,

thanks

can i change programmatically

See my first post.
You can change the step or set the seed on insert but I would not advise it.

You should look at why you want to do this - you probably do not want an identity. You could use a view or computed column but this does not sound like an artificial value.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources