[dba-SQLServer] Random autonumber ID

Hi John

No, it is not originating from replication. This Access database contains only test data where only the meaningful content have lived outside this before as it was a partial import from production data from a SAP system.

/gustav


>>> jwcolby at colbyconsulting.com 08-06-12 17:27 >>>
The random autonumber may have come from an attempt to do replication, where the be was placed out 
many different offices and then merged into one place.  Access changes any existing sequential 
autonumbers to random if you use the replication wizard on an Access container.

The problem now is that you can't go back to sequential because your PK will eventually hit a random 
number higher than the current number and it will fail. Sequential isn't very smart about "going 
around" existing numbers.

John W. Colby
Colby Consulting

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

On 6/8/2012 10:44 AM, Gustav Brock wrote:
> 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