SQLServerCentral / Discuss content posted by Chris Morton / Article Discussions / Article Discussions by Author / random integer number scalar function / Latest Posts

RE: random integer number scalar function

RBarryYoung — Wed, 17 Dec 2008 10:35:59 GMT

Sorry Jon, I misread earlier.

RE: random integer number scalar function

jcrawf02 — Wed, 17 Dec 2008 10:08:19 GMT

[quote][b]RBarryYoung (12/17/2008)[/b][hr]Neat! Thanks for sharing, Jeff.[/quote]Jeff?Glad to share, Harold

RE: random integer number scalar function

RBarryYoung — Wed, 17 Dec 2008 09:47:38 GMT

Neat! Thanks for sharing, Jeff.

RE: random integer number scalar function

jcrawf02 — Wed, 17 Dec 2008 08:03:07 GMT

Thought you might like to see what I did, any suggestions welcome on making it better. Tried to KISS for the end user of this script. Enjoy!****Edit - I should mention that I'm NOT generating test data for the actual SELECT that's in here because, well,1) not sending PHI is the whole point of this, and 2) the SELECT is meant to be overwritten by the user anyway (you, in this case) *****[code]/*Title: 'Generate De-identified Test Data.sql'Description: the purpose of this script is to demonstrate how to generate test data so that help can be sought enterprise-wide, from users who may not have access to the actual data source. Values entered into the variables will change dates, although zeroes can be used to retain date information if necessary There are five steps to use this (marked as such below): STEP ONE - initialize variables as needed to name tables, limit results and move dates around STEP TWO - enter your query, adding 'INTO ##myTemp' as noted and fully naming tables STEP THREE - copy your table.field names into the STEP THREE area (automatically grabs datatypes and lengths) STEP FOUR - Execute this script and copy/paste first the Message tab, then the Results tab into your post/email STEP FIVE - Save a copy of the results in case you want to tie the new values back to the original in your dataKnown flaws: n/aAuthor: Jon Crawford Healthcare Data AnalystRevision History: Date Changes ------ ------- 12/12/2008 'initial implementation'*/SET NOCOUNT ONUSE TempDB -- DO NOT CHANGE THIS-- =================================-- DECLARE variables-- =================================DECLARE @shiftYearsLower int,-- lower bound of random years added to dates @shiftYearsUpper int,-- upper bound of random years added to dates @shiftMonthsLower int,-- lower bound of random months added to dates @shiftMonthsUpper int,-- upper bound of random months added to dates @shiftDaysLower int,-- lower bound of random days added to dates @shiftDaysUpper int,-- upper bound of random days added to dates @sq char(1), -- holds the value of a single quote to simplify our dynamic SQL @limit varchar(25), -- holds the number of rows you want to generate @columns varchar(8000), -- variable to hold the columns that we want @createTableSQL varchar(8000), -- variable to hold the dynamic SQL that will generate our table @mySelectList varchar(8000), -- variable to hold the dynamic SQL that represents your SELECT in the real world @myInsertList varchar(8000), -- @myInsertList holds the dynamic SQL for the INSERT statements you need -- if your inserts are too long for this,-- then my advice is to rethink your inserts, you're passing test data. Make it fit. @myTableData varchar(8000), -- holds the data that you will be copy/pasting @myTempTableName varchar(255) -- holds the name of your current output table (set to #temp)-- ===============================================-- Initialize variables - DON'T CHANGE THESE ONES-- ===============================================-- we have to set all to Empty String ('') because we're adding their value to themselves, -- and if we don't, the first value is undefined, which wrecks the whole thingSET @columns = ''SET @createTableSQL = ''SET @mySelectList = ''SET @myInsertList = ''SET @myTableData = ''SET @sq = ''''-- ======================================================================================-- STEP ONE: Initialize variables - CHANGE THESE AS NEEDED-- ======================================================================================-- change @myTempTableName to whatever you want your table name to be that you will be posting-- *** NOTE *** you'll have to run this script once for each temp table that you want to generateSET @myTempTableName = '#temp' -- set @limit to 'TOP x' where x is the number of rows you want to generate-- or set to '' if you don't want to limit the results-- (but remember your statements need to fit within an 8000 character variable)SET @limit = 'TOP 10' -- the following shift days/months/years in all date fields either a set number or a random number in a boundarySET @shiftYearsLower = 10 -- lower bound of random years added to datesSET @shiftYearsUpper = 10 -- upper bound of random years added to datesSET @shiftMonthsLower = 1 -- lower bound of random months added to datesSET @shiftMonthsUpper = 1 -- upper bound of random months added to datesSET @shiftDaysLower = 0 -- lower bound of random days added to datesSET @shiftDaysUpper = 0 -- upper bound of random days added to dates-- =========================-- END OF STEP ONE-- =========================IF OBJECT_ID('Tempdb..##myTemp') IS NOT NULL BEGIN DROP TABLE ##myTemp END-- ======================================================================================-- STEP TWO:-- Add your SELECT statement below, this is a sample based on member and claim startdate -- that shows both the original and modified data -- SELECT INTO a global temp table (allows for complex joins, calculated fields, etc),-- so you'll want to alias all columns that are modified in any way (e.g. column AS alias)-- ======================================================================================SELECT TOP 10 rtrim(member.fullname) AS member, 'member'+convert(varchar(25),abs(checksum(member.fullname))) AS testMember, rtrim(provider.fullname) AS provider, 'provider'+convert(varchar(25),abs(checksum(provider.fullname))) AS testProvider, rtrim(claim.claimid) AS claimid, 10000000000+abs(checksum(claim.claimid)) AS testClaim, claim.startdate, dateadd(yyyy,abs(checksum(claim.startdate))%(@shiftYearsUpper - @shiftYearsLower+1)+@shiftYearsLower, dateadd(mm,abs(checksum(claim.startdate))%(@shiftMonthsUpper - @shiftMonthsLower+1)+@shiftMonthsLower, dateadd(dd,abs(checksum(claim.startdate))%(@shiftDaysUpper-@shiftDaysLower+1)+@shiftDaysLower,claim.startdate) ) ) AS testClaimStartdate-- ======================-- Add this to your query after your SELECT clauseINTO ##myTemp -- FULLY NAME YOUR TABLES below along with aliases -- e.g. JOIN someDB.dbo.tablename tablename ON blah blah blah-- ^db ^author ^table ^alias ^JOIN criteria-- ======================FROM someDB.dbo.member member JOIN someDB.dbo.claim claim on member.memid = claim.memid JOIN someDB.dbo.provider provider ON claim.provid = provider.providORDER BY member.memid-- =========================-- END OF STEP TWO-- ========================= -- GENERATE INSERT STATEMENTS TO HELP OTHERS POPULATE #temp WITH TEST DATASELECT @columns = @columns + char(9) + cols.name + ' ' + systypes.name + CASE WHEN systypes.name IN ('char','varchar') THEN '('+convert(varchar(4),cols.length)+')' ELSE '' END + ',' + char(13), @mySelectList = @mySelectList + cols.name + ', ', @myInsertList = @myInsertList + @sq + ',' + @sq + char(13) + CASE WHEN systypes.name IN ('char','varchar','datetime','smalldatetime') THEN '+' +@sq+@sq+@sq+@sq+ ' + '-- +CASE WHEN cols.name IN ('memid','carriermemid','fullname','enrollid','member') THEN @sq+'member'+@sq+'+convert(varchar(25),abs(checksum(ISNULL('+objs.name+'.'+cols.name+','+@sq+@sq+')))) + ' WHEN cols.name IN ('provid','affiliateid','affiliationid','fedid','provider') THEN @sq+'provider'+@sq+'+convert(varchar(25),abs(checksum(ISNULL('+objs.name+'.'+cols.name+','+@sq+@sq+')))) + ' WHEN cols.name IN ('claimid','claim','encounter') THEN 'convert(varchar(11),10000000000+abs(checksum('+objs.name+'.'+cols.name+','+@sq+@sq+'))) + ' WHEN systypes.name IN ('datetime','smalldatetime') THEN 'convert(varchar,'+objs.name+'.'+cols.name+',20) + ' ELSE 'convert(varchar(25),ISNULL('+objs.name+'.'+cols.name+','+@sq+@sq+')) + ' END + @sq+@sq+@sq+@sq ELSE '+ CONVERT(varchar(255),ISNULL(' + objs.name+'.'+cols.name+',0))' END + ' + ' FROM dbo.sysobjects AS objs INNER JOIN dbo.syscolumns AS cols ON cols.id = objs.id INNER JOIN dbo.systypes systypes ON cols.xtype = systypes.xusertypeWHERE objs.name+'.'+cols.name IN (-- ===========================================================================================================-- STEP THREE:-- change these to your table.field names -- (field names should match your fields/aliases in the SELECT above)-- =========================================================================================================== '##myTemp.member', --'##myTemp.testMember', '##myTemp.provider', --'##myTemp.testProvider', '##myTemp.claimid', --'##myTemp.testClaim', '##myTemp.startdate'--, --'##myTemp.testClaimStartDate'-- ====================================================================-- STEP FOUR: Now Execute the query for each temp table and -- copy/paste from the Messages tab first and then -- the Results tab into your post/email-- ==================================================================== )-- belongs to the above query-- Create temp table generation codeSET @createTableSQL = 'IF object_id(''Tempdb..'+@myTempTableName+''') IS NOT NULL BEGIN DROP TABLE '+@myTempTableName+' ENDCREATE TABLE '+@myTempTableName+' (iRow int identity(1,1), -- identity column for primary key' + substring(@columns,1,len(@columns)-2) + ')--===== Add a Primary Key to maximize performanceIF OBJECT_ID(''Tempdb..'+@myTempTableName+''') IS NULLBEGIN ALTER TABLE '+@myTempTableName+' ADD CONSTRAINT PK_'+@myTempTableName+'_iRow PRIMARY KEY CLUSTERED (iRow) WITH FILLFACTOR = 100END' PRINT @createTableSQL -- Created code will show up in Messages tab of QA, copy/paste into your email/posted questionPRINT '-- Insert test data into table' + char(13) + '-------------------------------------------------------' + char(13)-- Created code will show up in Results tab of QA, copy/paste into your email/posted questionSET @myTableData = 'SELECT ' + @limit + ' ' + @sq + 'INSERT INTO '+@myTempTableName+' (' + substring(@mySelectList,1,len(@mySelectList)-1) + ') ' + 'VALUES (' + @sq + substring(@myInsertList,4,len(@myInsertList)) + @sq +')' + @sq + ' AS [Copy and Paste these AFTER the text from the Messages tab]' + char(13)+ ' FROM ##myTemp'--PRINT @myTableData -- uncommenting this will show you what the dynamic SQL generated from the above looks likeEXEC(@myTableData) -- execute the dynamic SQL we just generated, to create the INSERT statements-- =======================================================================================================-- STEP FIVE - Save the data from this SELECT as a reference in case you want to tie back to the original-- =======================================================================================================SELECT 'See below, member and provider data ' AS [Example of how the data is changed], 'is converted to memberX, providerX;' AS [.], ' claims are changed to other numbers;' AS [..], ' and dates are moved randomly' AS [...]SELECT * FROM ##myTemp[/code]

RE: random integer number scalar function

RBarryYoung — Fri, 12 Dec 2008 11:04:58 GMT

Glad I could help.

RE: random integer number scalar function

jcrawf02 — Fri, 12 Dec 2008 10:29:20 GMT

Thanks, and understood. I think this will be fine for our purposes, and we're mostly on 2000 at the moment, so hashbytes() won't help me until we upgrade. Thanks again,Jon

RE: random integer number scalar function

RBarryYoung — Fri, 12 Dec 2008 09:56:19 GMT

The CHECKSUM() is not cryptographically safe, however, if you use it as a one-way hash, it shoudl be unreversible, unless the crackers already have a complete list of the source values.IE, CHECKSUM(fullname) should not be reversible, unless they already know what all of your "fullname" values could be, and in that context, there is no safe way to hash short text with limited possible values without a variable & secret key.If you want more protection than CHECKSUM, then you might want to try HASHBYTES().

RE: random integer number scalar function

jcrawf02 — Fri, 12 Dec 2008 06:38:05 GMT

Great timing Chris, I was just looking for something that would allow me to generate random numbers.Jeff, based on your code for random test data, I had been playing with newid() and now I took another look at the checksum().Question:What I'm trying to do is show folks how to generate test data that we (corporate we) can share across state units, without revealing HIPAA protected information (member names/ids/dates of service, etc). If I do this to generate fake names and dates: [code]SELECT TOP 10 member.fullname, 'member'+convert(varchar(25),abs(checksum(member.fullname))) AS testMember, member.dob, dateadd(dd,abs(checksum(member.dob))%(5-1+1) +1,member.dob) AS testDateFROM member[/code]can someone reverse the checksum somehow and read the correct data?

RE: random integer number scalar function

Jeff Moden — Sat, 29 Nov 2008 10:38:59 GMT

The only thing that I can figure out is that the optimizer in 2k5 resolves the sub-query before using it... the one in 2k seems to be trying to resolve it on the fly and makes mistakes because Number is indeterminate. I've never run into this problem before because I've either always put such things into a Temp table first or simply not done a Group By on the random number.

RE: random integer number scalar function

Jeff Moden — Sat, 29 Nov 2008 09:20:26 GMT

Isn't that strange? It's the GROUP BY loosing it's mind and it doesn't happen in 2k5. Not sure what to do about it yet.

RE: random integer number scalar function

Jeff Moden — Sat, 29 Nov 2008 09:09:10 GMT

Heh... well never mind... all of the sudden, I'm getting the same bug and I only have a single CPU...

RE: random integer number scalar function

Jeff Moden — Sat, 29 Nov 2008 09:06:58 GMT

[quote][b]rbarryyoung (11/29/2008)[/b][hr]Huhn, the bug happens on my last remaining SQL 2000 server also. MAXDOP has no effect on it.[/quote]Is it a multi CPU box you're working on? Also, which SP are you guys using? I'm using sp3A... never had the hair to go to 4 because I heard about some performance issues.

RE: random integer number scalar function

RBarryYoung — Sat, 29 Nov 2008 09:04:22 GMT

Huhn, the bug happens on my last remaining SQL 2000 server also. MAXDOP has no effect on it.

RE: random integer number scalar function

Jeff Moden — Sat, 29 Nov 2008 06:59:08 GMT

I've not seen such a thing... perhaps try using MaxDop 1 just to see if parallelism is getting to it? I'd try it on a multi-processor box if I had access to one, but I don't so I can't hammer it out on this end. Sorry.

RE: random integer number scalar function

ChrisM@Work — Sat, 29 Nov 2008 03:24:25 GMT

[quote][b]Jeff Moden (11/28/2008)[/b][hr]Chris, I've created a Numbers table with the same column name as you (Number), and I cannot get the code to fail the same way as you have with the dupes and all.What version of SQL Server do you have and are you using either QA or SMS for the interface? I've seen the Oracle version of TOAD produce such things, but never QA or SMS. Also, single or multi CPU on your box?[/quote]It doesn't happen on the 2k5 box, only on the 2k box queried from either QA or SMS. Version is 8.00.760 (SP3) and it's dual-processor.

RE: random integer number scalar function

Jeff Moden — Fri, 28 Nov 2008 18:22:09 GMT

Chris, I've created a Numbers table with the same column name as you (Number), and I cannot get the code to fail the same way as you have with the dupes and all.What version of SQL Server do you have and are you using either QA or SMS for the interface? I've seen the Oracle version of TOAD produce such things, but never QA or SMS. Also, single or multi CPU on your box?

RE: random integer number scalar function

Jeff Moden — Fri, 28 Nov 2008 17:53:52 GMT

Dang... I missed that... was looking at the wrong column for "dupes".

RE: random integer number scalar function

RBarryYoung — Fri, 28 Nov 2008 17:18:12 GMT

Yeah, but he's getting dupes, even from the Group By categories, Jeff.Chris: FWIW, I am not seeing this on my Laptop.

RE: random integer number scalar function

Jeff Moden — Fri, 28 Nov 2008 15:43:59 GMT

[quote][b]Chris Morris (11/28/2008)[/b][hr][quote][b]Jeff Moden (11/28/2008)[/b][hr]That's about what I would expect from an almost real set of random numbers.[/quote]You mean [i]almost reel[/i], right? :P-- did you catch the dupes?[/quote]Yep... and the dupes are appropriate for such a small set of random numbers. Consider the simplest of all random number systems... Black and Red spots (with the occasional Green spot or spots) on a Roulette wheel... what would you guess would be the maximum number of times that, say, Black would show up consecutively?

RE: random integer number scalar function

ChrisM@Work — Fri, 28 Nov 2008 08:03:04 GMT

[quote][b]Jeff Moden (11/28/2008)[/b][hr]That's about what I would expect from an almost real set of random numbers.[/quote]Sorry Jeff, should have explained more:[code]SELECT Number, COUNT(*) as NumberCountFROM (SELECT ABS(CHECKSUM(NEWID()))%(500-300+1)+300 AS Number FROM dbo.Numbers WHERE number <= 200) d GROUP BY Number ORDER BY Number[/code]Some results:[code]Number NumberCount ----------- ----------- 300 1303 1303 1305 2307 3307 3307 1309 2[/code][code]SELECT Number, SUM(NumberCount), count(*)FROM (SELECT Number, COUNT(*) as NumberCount FROM (SELECT ABS(CHECKSUM(NEWID()))%(500-300+1)+300 AS Number FROM dbo.Numbers WHERE number <= 200 ) d GROUP BY Number ) d2GROUP BY NumberORDER BY Number[/code]Some results:[code]Number ----------- ----------- ----------- 301 4 3304 1 1307 2 2308 1 1309 1 1[/code]

RE: random integer number scalar function

ChrisM@Work — Fri, 28 Nov 2008 07:57:27 GMT

[quote][b]Jeff Moden (11/28/2008)[/b][hr]That's about what I would expect from an almost real set of random numbers.[/quote]You mean [i]almost reel[/i], right? :P-- did you catch the dupes?

RE: random integer number scalar function

Jeff Moden — Fri, 28 Nov 2008 07:54:18 GMT

That's about what I would expect from an almost real set of random numbers.

RE: random integer number scalar function

ChrisM@Work — Fri, 28 Nov 2008 07:38:17 GMT

Now this [i]is[/i] interesting. Watch what happens when you try to find out how many of those random integers are randomly repeated:[code]SELECT Number, COUNT(*) FROM (SELECT ABS(CHECKSUM(NEWID()))%(500-300+1)+300 AS Number FROM dbo.Numbers WHERE number <= 200) d GROUP BY Number ORDER BY Number[/code]:D

RE: random integer number scalar function

kodracon — Fri, 28 Nov 2008 07:12:03 GMT

thanks thats a useful technique!

RE: random integer number scalar function

Jeff Moden — Wed, 26 Nov 2008 22:09:00 GMT

Here's a "fix" for your code... doesn't change a thing in the code... just the way the "seed" for the RAND function works...[code]select dbo.fx_getrandomnumber(500, 300, rand(CHECKSUM(NEWID())))FROM dbo.TallyWHERE N <= 10[/code]That will randomly generate 10 "different" integers with the understanding that it's the nature of random numbers to occasionally be duplicate in any set of random numbers.If I may suggest, if you need to write that much code to use a function, you may want to consider not even using a function. Just do it "inline".[code] SELECT ABS(CHECKSUM(NEWID()))%(500-300+1)+300 FROM dbo.Tally WHERE N <= 10[/code]

RE: random integer number scalar function

Jeff Moden — Wed, 26 Nov 2008 22:00:10 GMT

Ummm... good idea and nice try... but doesn't work to create different random numbers within the same Select. For example, using the eample Select you have with your code....[code]select dbo.fx_getrandomnumber(500, 300, rand())FROM dbo.TallyWHERE N <= 10[/code]... will return 10 identical numbers.

random integer number scalar function

kodracon — Wed, 26 Nov 2008 08:48:43 GMT

Comments posted to this topic are about the item [B]random integer number scalar function[/B]