InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 01:38:56 GMT20http://www.sqlservercentral.com/Forums/Topic623503-263-1.aspxYou're welcome.Mon, 26 Jan 2009 14:54:01 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspx[quote][b]GSquared (1/26/2009)[/b][hr]Is that from when I told someone to stop using those on you, Jeff? Just last week, if I remember correctly.[/quote]Yes, Sir! And I have to tell you, thank you very much for the position you took on that one... it was very timely and [i]very [/i]much appreciated.Mon, 26 Jan 2009 11:51:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspxIs that from when I told someone to stop using those on you, Jeff? Just last week, if I remember correctly.Mon, 26 Jan 2009 11:18:46 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspxHeh... if it's not in the "SQL Dictionary", I usually don't get it, either. I learned one just recently that very well describes some of the source of "flames" on some of the threads I've seen over the years... cool sounding word (pair of words, actually)...[font="Arial Black"]ad hominem [/font]Mon, 26 Jan 2009 11:02:33 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspx[quote][b]Jeff Moden (1/23/2009)[/b][hr]Everything else sounds pretty [b]copasetic[/b].[/quote]That's the first time in many years I've have to go and look up the definition of a word because I had absolutely no idea what it meant and couldn't work it out! :)For those, like me, who don't use North American English:[quote][b]co·pa·cet·ic[/b] or [b]co·pa·set·ic[/b][i]adj.[/i]Very satisfactory or acceptable; fine: [i]"You had to be a good judge of what a man was like, and the English was copacetic" John O'Hara.[/i]--------------------------------------------------------------------------------[Origin unknown.][/quote](From [url]http://www.thefreedictionary.com/copacetic[/url])Mon, 26 Jan 2009 05:08:26 GMTDerek Dongrayhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspxLike I said... just a little of 2.8 GB... I've got files much larger than that. I've been through that with 2 companies now... they ignore the flagship product that could make a lot of bread and butter and go wandering off into the unknown for the "Gee-whiz" of it all. Terrible way to crash and burn because they never actually see it coming. Hurts a lot of good people working for them.Fri, 23 Jan 2009 20:49:59 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspx[quote][b]Jeff Moden (1/23/2009)[/b][hr]I'm not sure it was worth storing 43 years worth of numbers (480,000,000/30,000/365) even if (at 6 bytes with no overhead) that works out to be just 2.8 gig. Everything else sounds pretty copasetic.[/quote]We were using about 5-million per year, and that business line was expected to at least double in the next couple of years. If the marketing people had been able to market, even a little bit, and the salespeople had any inclination whatsoever to sell, it could actually have been pushed up to 10X volume in about three years, and kept going up from there. (50-million pieces per year was definitely possible.)Instead, they got a wild hair up their ... um ... well ..... Instead they decided to drop both of the successful product lines and pursue a line of business that had no proven track record, huge overhead, tremendous development cost and timeline, and nothing going for it except a sort of "wow, that would be cool if it could possibly work" ... well, that and and commissions that were higher than the successful product lines.480-million was overkill, but perhaps not as badly as it might sound.Fri, 23 Jan 2009 20:09:57 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspx[quote][b]peter (1/22/2009)[/b][hr]Right now I do feel tempted to try and put an article together that analyzes functions more in-depth and demonstrates how to best use them according to my personal experience. That is without drowning in RBAR or being heavy on procedural constructs that most of the time are not needed. But given that it takes me ages just to write this post (I have this problem with every post I make), don't expect it soon. A lot of the possible material is already earlier posts on this forum, but to make an informative coherent article takes a lot more work.I will give it some deeper thought! [/quote]Heh... while you're thinking, write in down in a Word document and save your examples... Then, all you have to do is organize the document, copy the code in, and in only a week or two, viola! You have a world class article.Fri, 23 Jan 2009 18:06:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspxI'm not sure it was worth storing 43 years worth of numbers (480,000,000/30,000/365) even if (at 6 bytes with no overhead) that works out to be just 2.8 gig. Everything else sounds pretty copasetic.Fri, 23 Jan 2009 18:04:10 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspxYeah, it was something in that range. I don't have access to that database any more (since it was for a company that no longer exists), but it was pretty huge.And, as per my original statement on it, imagine what tempdb would go through if I just tried to create that with a set of cross joins all in one pass.Fri, 23 Jan 2009 14:24:09 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspxAssuming my numbers are right, you generated a set of over 480 million codes overnight (before dropping the H8 and 69s)??I'd definitly not consider this slow by all means... and it's quite obvious why the 7th digit is not really required :-P. Can you imagine anyone needing and 7th or 8th digit for physical mailings (not e-mails) :w00t:.Fri, 23 Jan 2009 10:07:59 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspx[quote][b]Jeff Moden (1/22/2009)[/b][hr][quote][b]GSquared (1/22/2009)[/b][hr]Needed in batches of about 5-10,000 at a time, usually about 100k per week.So, there's on situation where I consider procedural code valid.[/quote]Are you saying it took overnight to run 10,000 of these? Also, what were you using for the random selections?[/quote]Not at all random. And no, it didn't take overnight to run 10k of these, it took overnight to try to pre-generate the whole set. Here's a bit more of the business scenario:You're mailing about 100k cards per week, a little over 5-million per year, each one needs to have a barcode and a matching human-readable code on it, between 4 and 6 characters, and needs to be completely unique. If needed (doubtful), it could expand to 7 characters at some undefined point in the future. The generated codes need to not spell out words, need to not have "license plate words", and definitely need to not have anything offensive in them. By "license plate words", I mean combinations like "I69U" (sexual), or "H8YU" ("H8" = "hate"), or "G02HL" (G-zero-two-H-L).When the address lists are being processed, the whole process, include CASS certification, postal presort, assigning these unique codes, and output in mail-merge format for printing, needs to take under 5 minutes, to fit the needs for production and mailing.These batches can be anywhere from 5,000 at a time to 30,000 at a time, but if more than 10,000 at a time, it gets broken down into individual presort and output lists no larger than 10,000 each.The CASS and presort engine runs approximately 5k/minute on existing hardware and software. Simple hardware upgrade, by test, could get about a 10% improvement for a cost comparable to 6 months' profit for this line of business, and was thus not a valid ROI.Given those business requirements, what would your solution be? And, yes, in that line of business, these requirements all made sense and were valid.Mine was to pre-generate and pre-clean the combinations, store those in a table with an ID column, and then assign them by simply joining to the ID column in the address list table.Pre-generating them the way I did took a few hours total, and then the assignment would take a few milliseconds for each batch.Not that it will matter, since that company is out of business, but if you can come up with something more efficient, I'd be curious about it.Edit: One more requirement is that the codes have to be easy to read and understand over a phone, across regional accents, when being read by people who are mostly over 60 years old. (Voices and intonation often change as age increases.) Thus, you need to eliminate either "M" or "N", since they are too similar over the phone, and you can't include "O", because it looks too much like a zero. Ended up eliminating "P" and "D", since they sound too much like "B", and "Z" because it looks like a "2" to some people. Can't remember all the letters we got rid of, but we ended up with base-28 if I remember correctly.Fri, 23 Jan 2009 09:17:38 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspx[quote][b]GSquared (1/22/2009)[/b][hr]Needed in batches of about 5-10,000 at a time, usually about 100k per week.So, there's on situation where I consider procedural code valid.[/quote]Are you saying it took overnight to run 10,000 of these? Also, what were you using for the random selections?Thu, 22 Jan 2009 17:54:12 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspx[quote][b]Jeff Moden (1/22/2009)[/b][hr][quote][b]peter (1/22/2009)[/b][hr]Jeff, any luck in finding the saved "function argument" you mentioned? [/quote]No, sorry... I can't even remember who the "argument" was with... [b]Touch a minimal set of data, a minimal number of times, and in a minimal amount of set based steps as the optimizer or query complexity allows you to get away with.[/b] It is all about getting your results fast and efficient (and preferably in a non-blocking way) and not about a holy war of set vs procedural.[quote]Use set based constructs where you can as it exploits indexes and such and break it up in several set based stages when it gets to complex or inefficent due to optimizer/information limitations. This later is perceived as procedural by some. I would like to counter that this observation it is irrelevant and that set based solutons usually manage to achieve the primary goal in one step...doing the least amount of work...but sometimes you nead to break things up to achive this goal.And remember that under the hood ALL software is procedural, the set based aproach is just a mathematical model on top of it that allows for automatic optimizations to happen...and as we all know, no single model is perfect.[/quote]That's mostly right (especially about touching the data the fewest number of times possible) except for one thing that I argue over and over... people simply give up on "set based" solutions too early because they simply haven't studied their trade well enough to "just know" the proper set based solution (I emphasize "proper" because there's a lot of wrong ways to do it and it sometimes picks up a bad name from those wrong ways). For me, it is a bit of a "holy war" because I've seen so much very slow, resource demanding code come from it along with some of the really lame excuses people use to justify RBAR.[/quote]I understand how you feel...there is a quite some bad code and even more bad advice out on the net and the deeper I get into things the more that seems to be the case. But rest assured that your articles do help people of every skill level, and I myself picked up a few tricks from SQL Central and you in particular. Personally I try to be pragmatic in that when confronted with a possible new ingredient for a solution I try to understand and apply it. If it works well and meets certain professional criteria (maintainability / abstraction) then I stick with it until something better is found by me or someone else. And I always try to improve on the original if nothing else just to understand the ingredient I use better and a lot of the time I succeed :). Right now I do feel tempted to try and put an article together that analyzes functions more in-depth and demonstrates how to best use them according to my personal experience. That is without drowning in RBAR or being heavy on procedural constructs that most of the time are not needed. But given that it takes me ages just to write this post (I have this problem with every post I make), don't expect it soon. A lot of the possible material is already earlier posts on this forum, but to make an informative coherent article takes a lot more work.I will give it some deeper thought!Thu, 22 Jan 2009 16:19:46 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic623503-263-1.aspxI actually had something a while back that worked better procedurally than set-based, similar to the PIN code thing here.Six character alpha-numerics, each one needs to be unique, certain limitations on combinations allowed (nothing that could end up being obscene/racist/etc.), certain limitations on which letters to use, because of readability issues and phonic similarity (when someone is reading "N10501" over the phone, it's hard to tell if it's "M10501" or "N10501", so we didn't inclue any "N"s in the combinations, and so on). And nothing below four characters allowed.Needed in batches of about 5-10,000 at a time, usually about 100k per week.Because of various letter limitations, ended up with basically a six-digit, base-23 number system, which is a little over 148-million valid codes. Enough for about 10 years of operation before we'd have to expand to seven digits.It was simple enough to set up a table of the allowed characters and build a six-level self-cross-join (with the last two being outer joins to allow four- or five-digit combos), with "distinct" in the select.I set it to run at the end of work on a Monday, and when I came in Tuesday, the machine it was running on had run out of disk space and tempdb and its log file were completely filling everything up! It had crashed completely after six hours.So I fell back and punted, and created a set of nested loops that would iterate through the characters for each digit, and insert the rows one at a time into a table in a database with simple recovery. It finished the whole thing in about an hour, then I deleted the "don't use anything that resembles these combinations" list in a single pass, and was done with that in another hour.After further testing, I found that creating them in batches of 12,167 (23 cubed), by replacing the innermost three While loops with a pre-created table, sped up the process so that it finished in a few minutes.In that case, the purely set-based solution, with no loops, etc., in my part of the code, was simply too much for the hardware I was running it on, and I had to break the problem down into smaller pieces and run those procedurally to keep the server from having fits.So, there's on situation where I consider procedural code valid.Thu, 22 Jan 2009 13:08:05 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspx[quote][b]peter (1/22/2009)[/b][hr]Jeff, any luck in finding the saved "function argument" you mentioned? [/quote]No, sorry... I can't even remember who the "argument" was with... [b]Touch a minimal set of data, a minimal number of times, and in a minimal amount of set based steps as the optimizer or query complexity allows you to get away with.[/b] It is all about getting your results fast and efficient (and preferably in a non-blocking way) and not about a holy war of set vs procedural.[quote]Use set based constructs where you can as it exploits indexes and such and break it up in several set based stages when it gets to complex or inefficent due to optimizer/information limitations. This later is perceived as procedural by some. I would like to counter that this observation it is irrelevant and that set based solutons usually manage to achieve the primary goal in one step...doing the least amount of work...but sometimes you nead to break things up to achive this goal.And remember that under the hood ALL software is procedural, the set based aproach is just a mathematical model on top of it that allows for automatic optimizations to happen...and as we all know, no single model is perfect.[/quote]That's mostly right (especially about touching the data the fewest number of times possible) except for one thing that I argue over and over... people simply give up on "set based" solutions too early because they simply haven't studied their trade well enough to "just know" the proper set based solution (I emphasize "proper" because there's a lot of wrong ways to do it and it sometimes picks up a bad name from those wrong ways). For me, it is a bit of a "holy war" because I've seen so much very slow, resource demanding code come from it along with some of the really lame excuses people use to justify RBAR.Thu, 22 Jan 2009 12:18:04 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspx[quote][b]Steve Jones - Editor (1/22/2009)[/b][hr]All software does get procedural, though parallelism might get through some of that, and the interrupt/jumps to new parts as we wait for IO can also help things appear to happen a little more "set based"However, the optimizations in software, specifically SQL Server in this case, do make thinking in sets more efficient than procedurally dealing with data.Does this matter with UDFs? No idea, but if you'd like to write up some articles on tests (or anyone else, for or against), I think it would be great to get that information out there. I don't know I have enough experience to devise good tests, but it would be great to see what you can come up with, as well as some result from Jeff and others.[/quote]Just a quick heads up for the function question. I remember doing a test way back when on 2000 and even a schema bound function had an impact (nothing huge but it seemed consistant). Now when you start querying base tables, you're just screwed. I just tuned a query yesterday. The whole thing ran in 0.5 secs on average (15K records returned). Once I added a function to calculate the # of business days between the rows' date and today, the query jumped to 3 minutes (server class machine 64 bit, 16gb of ram, quad procs, the db I was using is only 8 GB in size and on a super fast SAN... the table the function was using? 10 000 rows, correctly indexed, and less than 1 MB of data).I tuned by using a temp table + quirky update to figure out all the possible values I'd need which runs in under 13 MS then use that in an inner join.Thu, 22 Jan 2009 06:46:47 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspxAll software does get procedural, though parallelism might get through some of that, and the interrupt/jumps to new parts as we wait for IO can also help things appear to happen a little more "set based"However, the optimizations in software, specifically SQL Server in this case, do make thinking in sets more efficient than procedurally dealing with data.Does this matter with UDFs? No idea, but if you'd like to write up some articles on tests (or anyone else, for or against), I think it would be great to get that information out there. I don't know I have enough experience to devise good tests, but it would be great to see what you can come up with, as well as some result from Jeff and others.Thu, 22 Jan 2009 06:38:36 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspxJeff, any luck in finding the saved "function argument" you mentioned? I would love to have a look at that earlyer discussion. BTW. This seems to be part of the discussion where everyone asks code from everyone else, lol...so to change the tune:Until shown otherwise I stick to my conclusion that the code example I provided is strong indictation, if not proof of, that the slowdown with user definined functions has nothing to do with them not being set based at all. And that instead the usual experienced massive slowdown is caused by an unreal call overhead of the UDF functions themselfs (compared next to none for build-in functions). This is a direct result of the way Microsoft implemented them and not of a fundamental property of functions or procedural code as is often perceived by the pure set based line of thinking camp. Time to stop bashing on even sound functions/procedural code and instead put the blame where it belongs....at the desk of some Micrsoft SQL Server team member.Get me right! This is not an argument against the set based definition (such as Jeff posted). I fully subscribe to the core of that definition. Instead I augment it by stating that what it expresses it is just as valid for a well writen set based solution as for a well written procedural one. [b]Touch a minimal set of data, a minimal number of times, and in a minimal amount of set based steps as the optimizer or query complexity allows you to get away with.[/b] It is all about getting your results fast and efficient (and preferably in a non-blocking way) and not about a holy war of set vs procedural.Use set based constructs where you can as it exploits indexes and such and break it up in several set based stages when it gets to complex or inefficent due to optimizer/information limitations. This later is perceived as procedural by some. I would like to counter that this observation it is irrelevant and that set based solutons usually manage to achieve the primary goal in one step...doing the least amount of work...but sometimes you nead to break things up to achive this goal.And remember that under the hood ALL software is procedural, the set based aproach is just a mathematical model on top of it that allows for automatic optimizations to happen...and as we all know, no single model is perfect.Thu, 22 Jan 2009 02:47:11 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic623503-263-1.aspxSo, Kit... can I see your code like I asked, please?Wed, 21 Jan 2009 12:24:20 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspxOh yeah... almost forgot... you need a Tally table for that code to work... please see the following...[url=http://www.sqlservercentral.com/articles/TSQL/62867/][font="Arial Black"] The "Numbers" or "Tally" Table: What it is and how it replaces a loop. [/font][/url]Thu, 25 Dec 2008 11:57:19 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspx[quote][b]Kit Brandner (12/22/2008)[/b][hr]Try a 23-character long alphanumeric PIN code with certain characters excluded for legibility that can't be repeated in the database. So Jeff's solution wouldn't work. I suppose I should have just put that in the original message, but proprietary sounded better.One of the proposed set-based methods I was trying was having a table that contained all the valid characters, and a tally table. But this method didn't render desirable execution times, and often would hang in the case of generating large sets of PIN codes. I also tried working with CROSS JOINs, but that didn't pan out much further. Any other ideas?Consider this: the term "PIN code" doesn't necessarily apply to the bank machine, just like the term "algorithm" is a fancy word for a set of rules. And banks often allow for a PIN code longer than 4 digits.[/quote]Ok, Kit... Merry Christmas... in return, I sure would like to see the script you have for doing this... I dug up a fairly recent bit of code I made in response to a script posting and was actually going to make an article about it... I can probably still do that, but here's the code...First, we need to be able to use the NEWID() function within a User Defined Function... since that's not allowed, we gotta get sneaky... we need this view to do it for us...[code] CREATE VIEW dbo.RandomIntegerGenerator AS/********************************************************************************************************************** Purpose: Return a single random Integer for use in User Defined Functions. May return negative, 0, or positive whole numbers. Range of numbers is approximately +- 2 Billion----------------------------------------------------------------------------------------------------------------------- Revision History: Rev 00 - 13 Nov 2008 -- Jeff Moden Intial creation in response to the script titled "Generate a random password" posted at http://www.sqlservercentral.com/scripts/Random+number/64471/ **********************************************************************************************************************/ SELECT CHECKSUM(NEWID()) AS RandomInteger --CheckSum's return type is INTGO[/code]... and here's the code for the function that will generate just about any kind of password you want. The only thing I have left to do is to force it to use at least 1 character from each requested character set, on demand. Do notice that things like a lower case "L" and uppercase "O" have been omitted. As usual, my explanations for what's happening in the code and how to use it, are in the code. Please read the header for all the different things you can do with this function...[code] CREATE FUNCTION dbo.CreateRandomPassword/********************************************************************************************************************** Purpose: Generate random passwords of a given length using up to 3 types of characters.----------------------------------------------------------------------------------------------------------------------- Usage: SELECT dbo.CreateRandomPassword (@pCharacterSet, @pPasswordLength)----------------------------------------------------------------------------------------------------------------------- Parameters: ==== @pCharacterSet: (See "Programmer's Notes #2) When @pCharacterSet contains 'LOWER' anywhere, Then LOWER CASE ALPHA characters are included When @pCharacterSet contains 'UPPER' anywhere, Then UPPER CASE ALPHA characters are included When @pCharacterSet contains 'ALPHA' anywhere, Then both LOWER and UPPER CASE ALPHA characters are included When @pCharacterSet contains 'NUMERIC' anywhere, Then numeric digits 2 thru 9 are included When @pCharacterSet contains 'DIGITS' anywhere, Then numeric digits 0 thru 9 are included When @pCharacterSet contains 'SYMBOL' anywhere, Then special characters are included When @pCharacterSet contains 'ALL' anywhere, Then all character types except 'DIGITS' are included When @pCharacterSet IS NULL or BLANK, Then all character types are included ==== @pPasswordLength When @pPasswordLength < 1 Then will return 10 character password When @pPasswordLength > 8000 Then will return 8000 character password When @pPasswordLength IS NULL Then will return 10 character password Otherwise, will return desired length----------------------------------------------------------------------------------------------------------------------- Programmer's Notes: 1. Note that following characters have been eliminated to avoid confusion for the user. The exception is that when 'DIGITS' is included in the @pCharacterSet, both 0 and 1 will be included for things like PINs. 0 (zero) O (capital "O") 1 (one) l (lower case "L") I (capital "I") 2. There is no guarantee that at least 1 character from each of the "character sets" will actually be used. 3. Because NEWID(), the only "real" source of random numbers in SQL Server, is not allowed in user defined functions in either SQL Server 2000 or 2005, this function calls a the RandomIntegerGenerator view which generates random whole numbers (positive, zero, or negative) as Integers (INT datatype) with a range of about +- 2 Billion.----------------------------------------------------------------------------------------------------------------------- Technical References: 1. Title: The "Numbers" or "Tally" Table: What it is and how it replaces a loop. Author: Jeff Moden - 07 May 2008 Link: http://www.sqlservercentral.com/articles/TSQL/62867/ 2. Title: Performance Tuning: Concatenation Functions and Some Tuning Myths Author: Jeff Moden - 01 Jan 2008 Link: http://www.sqlservercentral.com/articles/Test+Data/61572/----------------------------------------------------------------------------------------------------------------------- Revision History: Rev 00 - 13 Nov 2008 -- Jeff Moden Intial creation in response to the script titled "Generate a random password" posted at http://www.sqlservercentral.com/scripts/Random+number/64471/ **********************************************************************************************************************/--===== Declare the input/output parameters (@pCharacterSet VARCHAR(256), @pPasswordLength INT)RETURNS VARCHAR(8000) AS BEGIN--===== Declare local variablesDECLARE @rPassword VARCHAR(8000), --Return variable @CharacterType INT, --Bitmap to determine character types @Characters VARCHAR(256) --Characters to build the password from SELECT--===== Refactor input values to bitmap values using bit OR's.@pCharacterSet = UPPER(@pCharacterSet), --For case sensitive installations@CharacterType = CASE WHEN @pCharacterSet LIKE '%LOWER%' THEN 1 ELSE 0 END | CASE WHEN @pCharacterSet LIKE '%UPPER%' THEN 2 ELSE 0 END | CASE WHEN @pCharacterSet LIKE '%ALPHA%' THEN 3 ELSE 0 END --Combo 1 and 2 | CASE WHEN @pCharacterSet LIKE '%NUMERIC%' THEN 4 ELSE 0 END | CASE WHEN @pCharacterSet LIKE '%DIGITS%' THEN 8 ELSE 0 END --For PINs | CASE WHEN @pCharacterSet LIKE '%SYMBOLS%' THEN 16 ELSE 0 END | CASE WHEN @pCharacterSet LIKE '%SPECIAL%' THEN 16 ELSE 0 END | CASE WHEN @pCharacterSet LIKE '%ALL%' THEN 23 ELSE 0 END, --Combo 1, 2, 4, and 16@CharacterType = ISNULL(NULLIF(@CharacterType,0),23), --Default to lower case if null, 0, blank, or not found@pPasswordLength = CASE --Limit the "psuedo-cursor loop" to the desired length with "limits" WHEN ISNULL(@pPasswordLength,0) < 1 THEN 10 WHEN @pPasswordLength > 8000 THEN 8000 ELSE @pPasswordLength END,--===== Initialize the password@rPassword = '',--===== Build the character set to build the password from.@Characters = '' + CASE WHEN @CharacterType & 1 = 1 THEN 'abcdefghijkmnopqrstuvwxyz' ELSE '' END --LOWER/ALPHA/ALL + CASE WHEN @CharacterType & 2 = 2 THEN 'ABCDEFGHJKLMNPQRSTUVWXYZ' ELSE '' END --UPPER/ALPHA/ALL + CASE WHEN @CharacterType & 4 = 4 THEN '23456789' ELSE '' END --NUMERIC/ALL + CASE WHEN @CharacterType & 8 = 8 THEN '0123456789' ELSE '' END --DIGITS + CASE WHEN @CharacterType & 16 = 16 THEN '">_!@#$%&=?<>' ELSE '' END --SYMBOL/SPECIAL/ALL--===== Using concatenation and a psuedo-loop provided by a Tally table, create a password using the desired -- mix of characters to the desired length. See "Programmer's Note 3" in the header for why the call to -- the RandomIntegerGenerator view is necessary. SELECT @rPassword = @rPassword + SUBSTRING(@Characters,(SELECT ABS(RandomInteger)%LEN(@Characters)+1 FROM dbo.RandomIntegerGenerator),1) FROM dbo.Tally t WITH(NOLOCK) WHERE t.N <= @pPasswordLength --Limit the "psuedo-cursor loop" to the desired length with "limits"--===== Return the assemble password and exit RETURN @rPassword ENDGO[/code]And, here's some test code... on my box, it gen's 10,000 23 character alpha-numeric passwords in about 1200 milli-seconds... that should be plenty fast enough for anyone...[code]DECLARE @StartTime DATETIME SET @StartTime = GETDATE() SELECT dbo.CreateRandomPassword('AlphaNumeric',23) FROM dbo.Tally WHERE N <= 10000 SELECT DATEDIFF(ms,@StartTime,GETDATE())[/code]I suppose you could reject passwords that have like adjacent letters, but then that would make the password pseudo-random and would cut down on the number of possibilities that someone would have to try to break a password.Thu, 25 Dec 2008 11:52:51 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspx[quote][b]RBarryYoung (12/23/2008)[/b][hr]Are we talking about UDF's or builtin functions? as far as I know, the only overhead that the builtin functions have is the CPU cost of their actual calculations.[/quote]Oh, it was for UDF's alright. And, like I said, at one point I proved that a certain type of UDF in the presence of a multi-processor box was actually faster than inline code. Come to think of it... I may have actually saved the argument on my hard drive somewhere... lemme take a look.Tue, 23 Dec 2008 20:09:57 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspxAre we talking about UDF's or builtin functions? as far as I know, the only overhead that the builtin functions have is the CPU cost of their actual calculations.Tue, 23 Dec 2008 20:01:00 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspx[quote][b]RBarryYoung (12/23/2008)[/b][hr][quote][b]Jeff Moden (12/23/2008)[/b][hrIt was a very long time ago that we did some testing on this very forum and I can't even remember who the players were. I can't find the tests but...[/quote]Jeff: Do you remember the approximate time frame?[/quote]Gosh... I wanna say about 4 years ago... it started off with someone saying that, except for a certain function, all functions were bad. What the heck was his name? I'll keep looking for it now and again...Tue, 23 Dec 2008 19:55:02 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspxThis would make a great article. We don't have enough of these comparing RBAR with set-based solutions.Tue, 23 Dec 2008 08:47:17 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspx[quote][b]Jeff Moden (12/23/2008)[/b][hrIt was a very long time ago that we did some testing on this very forum and I can't even remember who the players were. I can't find the tests but...[/quote]Jeff: Do you remember the approximate time frame?Tue, 23 Dec 2008 07:54:30 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspx[quote][b]Ninja's_RGR'us (12/23/2008)[/b][hr]The only problem I see is to be able to create a dynamic amount of keys without looping and without sacrificing performance (how do you handle making 10 keys and 10 000 000 keys from the same query with same linear performance). I have a couple theories, but no time to test... maybe next year.[/quote]Actually, I suspect that the real challenge is to efficiently exclude any duplicates from the result set.Tue, 23 Dec 2008 07:47:22 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspx[quote][b]Steve Jones - Editor (12/22/2008)[/b][hr]Indeed, my guess is Jeff if handcrafting something right now in the winter snows of MI.[/quote]Heh... just waiting for the specs on the 23 character password/pin. I also want a copy of the original code to test against.Tue, 23 Dec 2008 05:18:04 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspx[quote][b]peter (12/23/2008)[/b][hr]Jeff is quiet about my posts - too quiet....is a storm coming?LOL[/quote]Nah... not ignoring you, Peter. Functions do normally contain some unneccessary overhead. Normally... there are exceptions. It was a very long time ago that we did some testing on this very forum and I can't even remember who the players were. I can't find the tests but... on multi-processor boxes using certain functions, I remember the functions actually winning out over the inline code. They were a bit of an oddity and I can't remember what the heck they did, but they did beat inline code. My silence was because I was trying to find those tests and haven't been able to put my finger on them.Tue, 23 Dec 2008 05:16:46 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspxThe fact of the matter is that once you can build the keyusing a set solution, you can also validate it using the same solution (might make for a darn long query... but that would work).The only problem I see is to be able to create a dynamic amount of keys without looping and without sacrificing performance (how do you handle making 10 keys and 10 000 000 keys from the same query with same linear performance). I have a couple theories, but no time to test... maybe next year.Tue, 23 Dec 2008 04:14:31 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspxJeff is quiet about my posts - too quiet....is a storm coming?LOLTue, 23 Dec 2008 02:23:29 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic623503-263-1.aspxIndeed, my guess is Jeff if handcrafting something right now in the winter snows of MI.Mon, 22 Dec 2008 21:11:08 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspx[quote][b]Jeff Moden (12/22/2008)[/b][hr][quote][b]Kit Brandner (12/22/2008)[/b][hr]Try a 23-character long alphanumeric PIN code with certain characters excluded for legibility that can't be repeated in the database. So Jeff's solution wouldn't work. I suppose I should have just put that in the original message, but proprietary sounded better.One of the proposed set-based methods I was trying was having a table that contained all the valid characters, and a tally table. But this method didn't render desirable execution times, and often would hang in the case of generating large sets of PIN codes. I also tried working with CROSS JOINs, but that didn't pan out much further. Any other ideas?Consider this: the term "PIN code" doesn't necessarily apply to the bank machine, just like the term "algorithm" is a fancy word for a set of rules. And banks often allow for a PIN code longer than 4 digits.[/quote]Heh... no problem... I'll be right back... by the way... what are the "desirable execution times" and do you have any other rules that you'd like to apply?Also, you said "that can't be repeated in the database" meaning what... that the password must be unique? How many passwords ya got in the database?[/quote]Just like waving the flag in front of the proverbial bull...:)Mon, 22 Dec 2008 17:32:19 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic623503-263-1.aspxI can't think of a better xmas gift to give for Jeff...Let's go... post the specs and the code and see what we come up with.Mon, 22 Dec 2008 16:52:08 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspxAlso, if the code really isn't "proprietary", post your procedureal code and let us have a go at it, eh?Mon, 22 Dec 2008 15:45:14 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspx[quote][b]Kit Brandner (12/22/2008)[/b][hr]Try a 23-character long alphanumeric PIN code with certain characters excluded for legibility that can't be repeated in the database. So Jeff's solution wouldn't work. I suppose I should have just put that in the original message, but proprietary sounded better.One of the proposed set-based methods I was trying was having a table that contained all the valid characters, and a tally table. But this method didn't render desirable execution times, and often would hang in the case of generating large sets of PIN codes. I also tried working with CROSS JOINs, but that didn't pan out much further. Any other ideas?[/quote]Yeah, give us the specs and let us go at it!Heh, I feel a contest coming on... :w00t:Mon, 22 Dec 2008 15:41:30 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspx[quote][b]Kit Brandner (12/22/2008)[/b][hr]Try a 23-character long alphanumeric PIN code with certain characters excluded for legibility that can't be repeated in the database. So Jeff's solution wouldn't work. I suppose I should have just put that in the original message, but proprietary sounded better.One of the proposed set-based methods I was trying was having a table that contained all the valid characters, and a tally table. But this method didn't render desirable execution times, and often would hang in the case of generating large sets of PIN codes. I also tried working with CROSS JOINs, but that didn't pan out much further. Any other ideas?Consider this: the term "PIN code" doesn't necessarily apply to the bank machine, just like the term "algorithm" is a fancy word for a set of rules. And banks often allow for a PIN code longer than 4 digits.[/quote]Heh... no problem... I'll be right back... by the way... what are the "desirable execution times" and do you have any other rules that you'd like to apply?Also, you said "that can't be repeated in the database" meaning what... that the password must be unique? How many passwords ya got in the database?Mon, 22 Dec 2008 15:39:18 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspxTry a 23-character long alphanumeric PIN code with certain characters excluded for legibility that can't be repeated in the database. So Jeff's solution wouldn't work. I suppose I should have just put that in the original message, but proprietary sounded better.One of the proposed set-based methods I was trying was having a table that contained all the valid characters, and a tally table. But this method didn't render desirable execution times, and often would hang in the case of generating large sets of PIN codes. I also tried working with CROSS JOINs, but that didn't pan out much further. Any other ideas?Consider this: the term "PIN code" doesn't necessarily apply to the bank machine, just like the term "algorithm" is a fancy word for a set of rules. And banks often allow for a PIN code longer than 4 digits.Mon, 22 Dec 2008 15:33:59 GMTKit Brandnerhttp://www.sqlservercentral.com/Forums/Topic623503-263-1.aspx[quote][b]Steve Jones - Editor (12/22/2008)[/b][hr]I knew Jeff would have something![/quote]Heh... Thanks, Steve. I'm just surprised because it's such an old trick and it's posted everywhere. You would think that everyone would know this one.Mon, 22 Dec 2008 14:53:07 GMTJeff Moden