| Author |
Topic |
|
monaya
Yak Posting Veteran
58 Posts |
 Posted - 2009-01-27 : 17:26:36
|
| I have an id (which will be a primary key for a new table) I need to create for my users but I don't want the typical auto increment 1, 2, 3 , 4 and so on.Is it possible to alter the ID column to a format of my liking such as one that stays within a certain amount of digits such as 323243, 344322, etc.. |
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2009-01-27 : 17:39:25
|
| I think what you're referring to is an identity column. You can seed the column from whatever number you like and increment by another number (most often the incrementation is by 1). Are you trying to limit your IDs to a specific number or length, or what is the format you're trying to achieve? |
 |
|
|
Nageswar9
Aged Yak Warrior
600 Posts |
Posted - 2009-01-27 : 23:14:55
|
| u define identity column like this,Stdid int identity( 1->startingpoint, 1->incrementation by 1 ) |
|
|
|
monaya
Yak Posting Veteran
58 Posts |
Posted - 2009-01-28 : 00:34:41
|
| Yeah I would like to keep the idea around 6 digits consistently. Like 345654 or perhaps 6 digits mixed with characters. Can you explain what i would do exactly in sql server to achieve this. I would also like to understand the concept in case I want to change the format. |
|
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2009-01-28 : 11:09:06
|
| What you could do is start the seed of the identity column at 100000 and increment by 1. So the first ID would be 100000, followed by 100001, 1000002 and so on. To do this you would define the column in the following way:ALTER TABLE YourTableADD YourIdentityColumn INT IDENTITY (100000,1) |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-28 : 11:16:44
|
| Also pretty soon INT has to be changed to BIGINT consuming spaces. |
|
|
|
monaya
Yak Posting Veteran
58 Posts |
Posted - 2009-01-28 : 16:09:11
|
| [quote]Originally posted by SkorchALTER TABLE YourTableADD YourIdentityColumn INT IDENTITY (100000,1)[/quoteCould I use this to alter the primary key in a table that's already up and running and have it go into effect for future records?Also how can I alter from INT to BIGINT if I have to do that? |
|
|
|
tonymorell10
Yak Posting Veteran
90 Posts |
Posted - 2009-01-28 : 17:04:50
|
| IF the existing table already has a primary key, you need to drop the primary key first, add the identity column to the table, then re-create the primary key on the identity column. Every existing row will get a value and new rows will get the next value in sequence.You would have to do the same thing if you wanted to alter the identity from INT to BIGINT, so why not just create it as BIGINT to begin with? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-28 : 18:49:24
|
| Why you want to start with Bigint datatype consuming space for Primary key? |
|
|
|
tonymorell10
Yak Posting Veteran
90 Posts |
Posted - 2009-01-28 : 19:52:25
|
| Planning ahead - something which DBA's and developers rarely think about. Consume the extra 4 bytes per row if you think you will run out of values with an INT data type during the lifetime of the table. Granted, it may a while to eat through 2 billion+ values, but how fast you will run out depends on how the identity is seeded and incremented and how many rows you expect to create. |
|
|
|
|
Alter Primary KEY ID value to new format?