Generate random records for table in SQL

Question

I'm doing a project in ms sql and I have to fill table with random records.

Here is a definition of table:

create table invoice(
 id_invoice INT PRIMARY KEY IDENTITY(1,1),
 id_employee INT FOREIGN KEY REFERENCES Employee(id_employee),
 id_client INT FOREIGN KEY REFERENCES Client(id_client),
 id_service INT FOREIGN KEY REFERENCES Service(id_service),
 amount MONEY,
 payment_way VARCHAR(20) CHECK (payment_way in ('cash', 'credit_card'))
);

And here is the code that generates random records:

ALTER PROCEDURE generate_invoices
AS
BEGIN
 DECLARE @id_employee INT
 DECLARE @id_client INT
 DECLARE @id_service INT
 DECLARE @amount INT
 DECLARE @payment_way VARCHAR(20)
 SET @id_employee = (SELECT TOP 1 id_employee FROM Employee ORDER BY NEWID())
 SET @id_client = (SELECT TOP 1 id_client FROM Client ORDER BY NEWID())
 SET @id_service = (SELECT TOP 1 id_service FROM Service ORDER BY NEWID())
 EXEC random 1000, 5000, @amount OUTPUT
 IF (@amount % 2 = 1) SET @payment_way = 'cash'
 ELSE SET @payment_way = 'credit card'
 INSERT INTO Invoice VALUES (id_employee, @id_clienta, @id_service, @amount, @payment_way)
END
GO
EXEC generate_invoices
GO 10

The code works obviously, but I would like to ask if there is better way (shorter, more efficient) to do same thing, because I must write over a dozen of similar procedure for other tables.

• RubberDuck: Interesting and clever. I didn't realize that ORDER BY could be exploited that way. I'm looking forward to seeing what others have to say.
• 200_success: @RubberDuck See What is the best way to get a random ordering?.

Answer 1

Randomness

Using NEWID() seems like a pretty smart way of getting random records, and it is. There is however a better function for this purpose. Say hello to the CRYPT_GEN_RANDOM() function.

The CRYPT_GEN_RANDOM() has a parameter that we need to provide, the length of the random string it is going to generate. For what we need, 4 or 5 characters is plenty.

I will update my answer with the proof when I find the time, but my own testing found CRYPT_GEN_RANDOM() generates less predictable records than NEWID().

The Random Stored Procedure

I have no idea what your Random stored procedure entails, but might I suggest an alternate method of generating a random number...

WITH Numbers(I) AS
(
 SELECT 1000
 
 UNION ALL 
 
 SELECT I+1
 FROM Numbers
 WHERE I<5000
)
SELECT TOP 1 @amount = I
FROM Numbers
ORDER BY CRYPT_GEN_RANDOM(4)
OPTION (MAXRECURSION 0)

First, this generates a table of numbers 1000 to 5000 using a recursive CTE, then selects one randomly using the CRYPT_GEN_RANDOM() that we discussed earlier.

To If or Not to If

Might I also suggest an alternate method of generating the @payment_way. Instead of using an If, might I suggest using a Simple Case Statement.

Personally I find this

SET @payment_way = 
CASE @amount % 2
 WHEN 1
 THEN 'cash'
 ELSE 'credit card'
END

To be a more SQL friendly way of writing it.

Putting it all together

This is what I ended up with. It's not any smaller, but it doesn't rely on any external stored procedures.

ALTER PROCEDURE generate_invoices
AS
BEGIN
 DECLARE 
 @id_employee INT = (SELECT TOP 1 id_employee FROM Employee ORDER BY CRYPT_GEN_RANDOM(4)),
 @id_client INT = (SELECT TOP 1 id_client FROM Client ORDER BY CRYPT_GEN_RANDOM(4)),
 @id_service INT = (SELECT TOP 1 id_service FROM Service ORDER BY CRYPT_GEN_RANDOM(4)),
 @amount INT,
 @payment_way VARCHAR(20)
 SET @id_employee 
 SET @id_client 
 SET @id_service 
 WITH Numbers(I) AS
 (
 SELECT 1000
 
 UNION ALL 
 
 SELECT I+1
 FROM Numbers
 WHERE I<5000
 )
 SELECT TOP 1 @amount = I
 FROM Numbers
 ORDER BY CRYPT_GEN_RANDOM(4)
 OPTION (MAXRECURSION 0)
 SET @payment_way = 
 CASE @amount % 2
 WHEN 1
 THEN 'cash'
 ELSE 'credit card'
 END
 INSERT INTO Invoice VALUES (@id_employee, @id_clienta, @id_service, @amount, @payment_way)
END
GO