How to random increment unique identity

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

How to random increment unique identity

Author

Topic

shlomiw
Starting Member

5 Posts

Posted - 2007-08-28 : 12:17:27

Hi, for some reason I want to have a unique ID with a seed and random Identity increment (I want an ascending ID's but without able to know how many objects there are).

any ideas?
thanks in advance

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2007-08-28 : 12:30:53

Ok I gotta ask.

WHY?

Jim
Users <> Logic

shlomiw
Starting Member

5 Posts

Posted - 2007-08-28 : 12:41:14

:-)

well, let say I'm selling a product and each product bargain have an ID.
now I give for each buyer the ID of this product bargain. the ID should increase from each bargain. but if I'll increase it by 1 each time, people can have a good idea how many products I'm selling. and I have this case which I don't want them to guess it (I own this privilege :-)). that why I want the increase to be by a random number.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 13:03:15

[code]CREATE TABLE Bargains
(
RowID INT IDENTITY PRIMARY KEY,
Code AS CHECKSUM(NEWID()),
CustomerID INT
)
GO
CREATE PROCEDURE dbo.uspGetBargainCode
(
@CustomerID INT,
@Code INT OUT
)
AS

SET NOCOUNT ON

INSERT Bargains
(
CustomerID
)
VALUES (
@CustomerID
)

SELECT @Code = Code
FROM Bargains
WHERE RowID = SCOPE_IDENTITY()
GO

DECLARE @Code INT

EXEC dbo.uspGetBargainCode 11, @Code OUT

SELECT @Code[/code]

E 12°55'05.25"
N 56°04'39.16"

franzzi
Starting Member

2 Posts

Posted - 2007-08-28 : 13:03:23

You limit the number of records the table can hold by wasting numbers.
I had such a problem too and solved it by preparing a table filled with random unique numbers and consuming from this table whenever i need a new key value (or public reference number). I have to make sure that enough numbers are in stock, from time to time generating new random numbers in there. The consumption has to be synchronized properly to not get the same random number for two concurrent sessions.
Maybe you could use such an approach.
You could for example keep an internal ascending key and use such a random number for public reference or do similar things.

shlomiw
Starting Member

5 Posts

Posted - 2007-08-28 : 13:35:09

Thanks for your fast replies guys!

Peso - I'm going to try what you suggested.
Franzzi - you made a good point of limiting the table number of rows.
so let me ask you a different question -
if I leave the ID's with 1 increment, but I still want to give a reasonable ID's (reasonable for human to use - in contrast to GUID). I'll take out the need of ID's going up, but I still need that my customers wont know the traffic volume of the bargains.
I been thinking about taking the original ID's from my db table and then do a kind of unique 2-ways function to convert the regular ID to 'product ID' and the other way of converting the 'product ID' to the db ID. well it is not much of a db question - but any idea here?
thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-28 : 14:08:36

[code]CREATE TABLE Bargains
(
RowID INT IDENTITY PRIMARY KEY,
Code AS ABS(CHECKSUM(NEWID())),
CustomerID INT
)[/code]

E 12°55'05.25"
N 56°04'39.16"

franzzi
Starting Member

2 Posts

Posted - 2007-08-28 : 14:16:41

If you have use a symmetric function you could achieve some effect.
For example you have a function which exchanges bits or group of bits of a 32 bit number this function would be symmetric because if you execute it again on the generated number you get the original number back. Such a function would give every identity number exactly one converted number without having duplicates in them. This is about bijective functions and isomorphismm if I remember back in mathematics :-)
This is not bulletproof and a guy who is familiar with cryptology and such things would get the original numbers within one hour or less :-))
But it could maybe serve the purpose for not too obviously show the ascending numbers. Such bit shuffling code can be done in TSQL and implemented within a user defined function.

Kristen
Test

22859 Posts

Posted - 2007-08-29 : 06:42:30

"if I'll increase it by 1 each time, people can have a good idea how many products I'm selling"

We have this issue for Session Numbers. We want a short value for PK and internal use, but an obfuscated view for external use.

We use NEWID() to generate a "random" value, take a few digits from that, put that in a SEPARATE field, and incorporate it in the "pretty value" shown to the user (and required when the user retrieves the data, but not required internally).

So we have

MyID INT IDENTITY NOT NULL PRIMARY KEY ...,
TheOtherBit VARCHAR(4) NOT NULL DEFAULT(SUBSTRING(CONVERT(varchar(50), NEWID()) ...

The ID part of our session numbers are clearly ascending, but no use to a hacker without the "TheOtherBit", which is different to your "I don't want the user to know the ID part", but it might lead you to some solution. Our primary need was speed, so we wanted an Identity

All of which may come back to Peso's CHECKSUM(NEWID()) ... approach

Kristen

shlomiw
Starting Member

5 Posts

Posted - 2007-08-29 : 08:27:09

thanks to you all!
I got a better idea how to approach this issue!
(not sure exactly what yet but now I have some good tools in hand)

rcurrey
Starting Member

30 Posts

Posted - 2007-08-30 : 11:46:22

This might sound silly, and certainly is a simple answer, but why don't you just seed the identity field with a number other than 1 and increase it by a number other than 1 ....

id int identity(10435,3) not null

I know my limited mind couldn't discern how many products you had from this.

Thanks,
Rich

Kristen
Test

22859 Posts

Posted - 2007-08-30 : 12:27:00

We did think about bumping up our client Order Numbers (IDENTITY column) by some random number each night to "fool their competitors" ...

shlomiw
Starting Member

5 Posts

Posted - 2007-08-30 : 12:58:57

quote:
Originally posted by rcurrey

This might sound silly, and certainly is a simple answer, but why don't you just seed the identity field with a number other than 1 and increase it by a number other than 1 ....

id int identity(10435,3) not null

I know my limited mind couldn't discern how many products you had from this.

Thanks,
Rich

of-course we thought about this - it just to easy to figure out...
I think I'll start with something like 2^x and shuffle the LSB x bits in a symetric shuffle function.
not too hard to figure out but less tempting.

or just use, like suggested here, another code - with random number, the problem with this one that if you want to use a "human number" (rather than GUID) - it is a bit more likely to get the same 2 numbers, and this is something I'd rather not to have (I believe in a large volume of traffic).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources