[dba-SQLServer] Random autonumber ID

Jim Lawrence accessd at shaw.ca
Thu Jun 14 12:11:46 CDT 2012

Just finished a small task asked for by a client. They wanted a running
digital clock display to appear at the top right hand corner of each of
their forms. Once the code was build it was really easy.

But; it took me threes solid days to add it to all their modules and forms.


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Gustav
Sent: Thursday, June 14, 2012 7:04 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Random autonumber ID

Hi John

You are right, but by trivial I meant "doesn't require much brain work, just
sit down and do it" - which, I know, very well could take a long time, but
the count of tables with random IDs is low as most have a normal sequential


>>> jwcolby at colbyconsulting.com 14-06-12 14:32 >>>

Trivial?  if there are two hundred tables each with a random auto PK and
there are 100 child tables, 
then there are 200 tables to change the PK type of and 100 tables to change
the FK in.

The process involves modifying each parent table to add a new sequential PK
field keeping the OLD 
PK, each child table to add a new "NewFK" keeping the OldFK, populating the
new sequential PK in the 
parent, then running a query to update the NewFK field in the child, *then*
finding and fixing every 
reference to the OldFK to now point to the NewFK in all queries and dynamic
SQL statements in code. 
  hen you rename the OkdPK and OldFK fields during testing.  Then dropping
those "Old" columns.

The "trivialness" of it depends entirely on the situation on the ground so
to speak, but it is 
decidedly not trivial.

While I would never use random in a new table, without very good reason, I
would never make a change 
back to sequential in a large database with very many random PKs.

John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it

On 6/12/2012 11:06 AM, Gustav Brock wrote:
> Hi John
> This, trivial and cumbersome.
> However, the client has accepted to move to sequential numbers. The reason
for the random numbers were, that the column this way looked "different"
when perusing the table. Not a very heavy argument, he admitted.
> /gustav
>>>> jwcolby at colbyconsulting.com 12-06-12 16:20>>>
> The problem is that in order to revert you have to manually rebuild the
autonumber PKs by moving the
> data into a new table with a new autonumber, plus the old "random"
autonumber.  You then have to
> update all child tables with then new PK values.  This is darned ugly and
error prone.
> John W. Colby
> Colby Consulting
> Reality is what refuses to go away
> when you do not believe in it
> On 6/12/2012 5:27 AM, Gustav Brock wrote:
>> Hi Mark
>> Thanks, I didn't know that.
>> By the way, I located this page:
>> which has a very good explanation of the pitfalls in the paragraph:
>> "Replicated Databases and Random Autonumbers")
>> This tells me that I once again should try to persuade the client to bite
the bullet and revert to sequential IDs.
>> /gustav
>>>>> marklbreen at gmail.com 12-06-12 10:23>>>
>> Hello Gustav,
>> I think that SQL Server will automatically pick up at the largest number
>> currently in the table and work away happily.  If not, you can easily set
>> the starting number for the new range using DBCC CHECKIDENT
>> In fact, if the last random number was 547845714  you could make the next
>> Identity Insert start at 6000000000
>> Mark

dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com

More information about the dba-SQLServer mailing list