SQLServer: random data continued

There are many posts which refer to Visual Studio’s ability to generate data for SQLServer randomly. That sounded great. Redgate-like capability for zero (extra) cost. Turns out there are no references post VS2010, and I’m not going to keep a copy hanging around just for that. (And at £300 per seat for the privilege, my company will not be buying Redgate, much as I like it.)

Also turns out that people use a combination of rand, abs, checksum, newid and modulus, to generate random data. I should add I looked into ways other than gen_crypto_random because that does not play nicely in a function, and I don’t propose writing lots of inline code repeating the same pattern time and time again.

All the necessary information for a way forward is here. The article is beautifully simple – absolutely nothing needs adding.

However… :-).. so now I want as above to use this in a function, something like this (just playing…)

random01

random02

Really? I am visibly not changing the state of the database in getting a guid, surely. So it will all have to be inline  – what a pain.

Maybe if I call out to CLR…

Another option is to create a temporary table stuffed full of the checksums for a million GUIDs, and then use that as our tally table that the function calls into. But that does mean we have to pass an index into the function, so that we’re not getting back the same hash every time. Hm, maybe the CLR route might give us a way.

 

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s