CODEWITHSUNDEEP
sql-server
beginIT
beginIT

Reputation: 227

How to randomly auto generate 8 digit number value for a primary key column in SQL Server?

Let's say I have created a Product Table in which Product_No is the PRIMARY KEY. Now when a new product is inserted in every row, the primary key column should generate any random 8 digit number value which is something like this:

48917232
19718291
12672554
12480288
21835819

Probably it is not possible to achieve it with IDENTITY or UNIQUEIDENTIFIER.

Does SQL Server have any datatype that can randomly auto generate 8 digit number with every new product insert ?

Upvotes: 0

Views: 5154

Answers (3)

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

First: Don't.

Second: CAST(FLOOR(RAND()*100000000) as int)

Third: Why?

There's no reason to do so.

  • If you fear problems with multiple concurrent users on the database: Don't. SQL Server will handle that for you.
  • If you are planning for some self-written replication: ainh, ainh, let SQL Server do this.

Listen to the voices and do not do this. If you need a randomly generated primary key, use a uniqueidentifier column with a default of NEWID(). The chance for duplicates is very small, because on the one hand, these GUIDs have 128 bits, on the other hand the algorithm not only uses a timestamp with nanoseconds precision but also the MAC address of the NIC on the machine that generates the GUID. With your 8 digit numbers, the chance for duplicates is about 1030.53 times higher.

Upvotes: 0

Vaccano
Vaccano

Reputation: 82361

First this is kind of an odd request. You may want to re-look at your design.

This answer may help, even if it does not fully meet with your specifications.

First: NewId().

NewId generates a "seemingly" random value of type `UniqueIdentifier'. Here are some example outputs:

5371C9B2-CC80-4B42-98F2-FF8D87F84358
46A08052-9A06-43C3-AE1C-8E9165328A38

This is more than 8 chars long, and is alpha numeric, so I does not quite fit with your request.

Note that it is usually a bad idea to make this type of value a clustered index.

Second: Sequence

A sequence is an object in Sql Server designed to make numbers. They can be limited to 8 digits(with stop and start values), and can skip values (for example it could count by 8's or 3's or whatever).

This is not random. (Is any number in computing really random?) But it does allow generating numbers that fit in the number range you want.

Summary

Anyway, that is my input on it. I recommend Sequences. But really I recommend looking harder at your design that is wanting random numbers for an identifier. Need for randomness is usually a design smell (randomness is often used for fake security, and is not good for storing indexes in Sql Server).

Upvotes: 1

sniperd
sniperd

Reputation: 5274

You can make your own function like this:

CREATE FUNCTION MYRAND(@RANDOMNUMBER NUMERIC(18,10)) 

RETURNS INT
AS
BEGIN
    DECLARE @MYRAND INT
    SET @MYRAND = (SELECT FLOOR(@RANDOMNUMBER*(9999999-1000000+1)+1000000))
    RETURN @MYRAND
END

And then as your default value for the column do this:

([dbo].[MYRAND](RAND()))

enter image description here

You can't have RAND as part of the function, which is why need to pass a RAND value into the function, and then it gets used to make a random number, that has exactly 8 digits using the FLOOR function.

Now, if you need a unique random number you'd have to do something else as there is no guarantee that these will be unique. Perhaps use an instead of trigger for inserts to generate the number and check to make sure it doesn't already exist.

You could also use logic to pre-populate a table with all the numbers you could possibly want and delete them from your pre-populate them table and use them.

Or you could have your insert trigger find the MAX value you have already inserted and just add 1.

You could also have your id column be identity and seed it at 10000000. It wouldn't be random but might do what you want.

Upvotes: 0

Related Questions