T-SQL Tuesday #143 – Random Fun

This month’s T-SQL Tuesday is hosted by John McCormack, who asked participants to blog about Short Code Examples.

A few years ago, I wrote a similar blog about T-SQL Tools that I could not live without. Those were my snippets files, which I maintain in Google Drive. Not much has changed since I’d spent the last 4 years working for SentryOne, but I still maintain the entire toolset and did use it periodically.

For today’s, I’m going to dive into my 1_Scripts.sql file and share some of my favorites all involving “random” values!

Random Fun

First, there are times where you need multiple random numbers. Simply using multiple iterations of RAND() in a single statement won’t cut it, as you need to vary the seed. So I keep this snippet handy for when I need a bunch of random values in a single statement:

/* Random Number between 0-99 */
SELECT
   CAST((RAND(CAST(NEWID() AS VARBINARY)) * 100) AS INT),
   CAST((RAND(CAST(NEWID() AS VARBINARY)) * 100) AS INT);
SELECT
   RAND(CHECKSUM(NEWID())),
   RAND(CAST(NEWID() AS VARBINARY));

Wait For Me Randomly

During my time at SentryOne, I found that I had to create mock workload on a regular basis. I wanted to introduce some unpredictability to make demos more interesting, so wrote this chunk of code to introduce random WAITFOR DELAY intervals.

/* Shell Agent Job with randomized runtime */

DECLARE
    @DelayMin INT = 5,
    @DelayLength CHAR(8) = '00:00:00'; -- hh:mm:ss
SELECT
    -- Create randomized 5-15 MINUTE WAITFOR DELAY
   @DelayLength = '00:'
   + RIGHT('0' + CAST((@DelayMin + CAST((RAND(CAST(NEWID() AS VARBINARY)) * 100) AS INT) % 15) AS VARCHAR(2)), 2)
   + ':00';
   -- Create randomized 5-15 SECOND WAITFOR DELAY
   --@DelayLength = '00:00:'
   --+ RIGHT('0' + CAST((@DelayMin + CAST((RAND(CAST(NEWID() AS VARBINARY)) * 100) AS INT) % 15) AS VARCHAR(2)), 2)

SELECT @DelayLength AS '@DelayLength';

PRINT 'START - Do Something';

-- DO SOMETHING HERE;
WAITFOR DELAY @DelayLength;

PRINT 'END - All Done With Something';

For My Final Trick, Random Strings

In much of my AutoDealershipDemo database, I have random strings for various things, so needed code to do that. I’m using a Tally Table approach, that then combines CHAR() with modulus operators to only general alpha-numeric values. But this can easily be adapted for other values available in CHAR().

; WITH Ten_CTE (N) AS (
   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), Thousand_CTE (N) AS (
   SELECT 1 FROM Ten_CTE a CROSS JOIN Ten_CTE b CROSS JOIN Ten_CTE c
), Tally_CTE (N) AS (
   -- This provides the "base" CTE and limits the number of rows right up front
   -- for both a performance gain and prevention of accidental "overruns"
   SELECT
      ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
   FROM Thousand_CTE
   -- CROSS APPLY Thousand_CTE t2 -- UNCOMMENT IF YOU WANT 1,000,000 results instead of 1,000
)
SELECT
   ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RecID,
   -- Increase concat below to increase length of string
   CHAR(ABS(CHECKSUM(NEWID())) % 26 + 97)
   + CHAR(ABS(CHECKSUM(NEWID())) % 26 + 97)
   + CHAR(ABS(CHECKSUM(NEWID())) % 26 + 97)
   + CHAR(ABS(CHECKSUM(NEWID())) % 26 + 97)
   + CHAR(ABS(CHECKSUM(NEWID())) % 26 + 97)
   + CHAR(ABS(CHECKSUM(NEWID())) % 26 + 97)
   AS RandomString
FROM Tally_CTE;


Hope you’ve enjoyed reading. These will most likely not be useful to the vast majority of you. But if you do ever have to write some mock workload, demo scripts, etc., then hopefully you’ll remember this blog post and make use of the above!

2 thoughts on “T-SQL Tuesday #143 – Random Fun

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.