[dba-SQLServer] Random autonumber ID

Most of my systems are set up with transaction keys on every record and
nothing is ever deleted, just no longer displayed. Slowly, via a moving
window all the records tagged as deleted as moved to posted tables.

I know this process is very old school but it has saved many companies when
a bit of forensic analysis of their data is required. The client is always
right, even when they are wrong but as the IT guy, it is dependant on you to
give them what they and want and what they need.

Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Gustav
Brock
Sent: Monday, June 11, 2012 3:55 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [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

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com