Generate random records for table in SQL

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.

• 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. – RubberDuck Feb 3 '15 at 1:30
• @RubberDuck See What is the best way to get a random ordering?. – 200_success Feb 3 '15 at 16:43

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