[dba-SQLServer] Random autonumber ID

Hi Jim

Sequential numbering is my preference too. 

If that isn't enough I prefer the GUID because of its simplicity.

/gustav


>>> jlawrenc1 at shaw.ca 08-06-12 18:07 >>>
If data collision is never going to be an issue then random numbers are fine
but then it asks the question, "Why". 

I prefer a controlled sequential numbering system with an appropriate
location prefix but another system that can be used is to assemble a serial
date+time value. The number is always proceeding, never any data collisions
and it have addition uses. I use a similar method in account transactions
where easy forensic account be done in the future.

Jim  

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Gustav
Brock
Sent: Friday, June 08, 2012 7:44 AM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Random autonumber ID

Hi all

I'm working on upsizing an Access backend to SQL Server and used Access'
wizard for this.
That worked with no errors.

However, the client used Random Autonumber for IDs in Access and these are
converted to integer with an associated trigger to create the random ID for
new records:

USE [PPT]
GO
/****** Object:  Trigger [dbo].[T_UserLastDataTable_ITrig]    Script Date:
06/08/2012 16:19:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[T_UserLastDataTable_ITrig] ON [dbo].[UserLastDataTable]

FOR INSERT 
AS
SET NOCOUNT ON
DECLARE @randc int, @newc int    
/* EMULATION CODE FOR AUTONUMBERING */
/* * EMULATION CODE FOR RANDOM AUTONUMBERING FOR FIELD "ID" */
SELECT @randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @newc = (SELECT ID FROM inserted) 

UPDATE UserLastDataTable SET ID = @randc WHERE ID = @newc


Is this really the best method?
Any disadvantages?
Would we be better off changing ID in Access to either sequential IDs or
even GUID before running the update wizard?

/gustav