InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 18:35:18 GMT20http://www.sqlservercentral.com/Forums/Topic566838-146-1.aspx[quote][b]bossimal (9/25/2008)[/b][hr]Hi All,Hungarian notation is awful and inflexible. I have attached the database naming conventions I developed for our organisation (identifying references removed) and it's worked wonders for our databases and applications. Our previous databases were very messy and we often faced problems of changing data types etc as previously described by Jeff. Now, it's easier to read, code and maintain and the lagtime to get new developers up to speed has decreased as the notation is far more intuitive.Any constructive feedback is welcome.Jane[/quote]Jane,I tried opening your document but I get redirected to the Forum Page.Could you please send me the document?Thanks.Wed, 19 Sep 2012 16:13:22 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspxHeh... "It Depends".... might be a very long interview. :-PTue, 09 Feb 2010 05:58:53 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspxDo you have one?Tue, 09 Feb 2010 05:37:39 GMTVinicioAizpuruahttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspx16 months later I should hope the guy has got his answer already... :-)Tue, 09 Feb 2010 02:51:53 GMTpaul.knibbshttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspxWhat we do in our shop is to add a prefix to the name of the field like: sName: (s) for Char, VarchardBirthdate (d) for Datetime nPayment (n) numeric fileld dec, int, smallint In the case you have to upgrade a numeric field smallint to int for intance then the name on the database will remain the same.Hope this help you in what you are looking for.Mon, 08 Feb 2010 06:27:45 GMTVinicioAizpuruahttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspx[quote][b]andrew gothard (10/4/2008)[/b][hr] "Are you good with cursors" "Brilliant - I never use them". This is often misunderstood[/quote]Heh... I often ask that question to find out what the interviewee actually knows about development. Your answer is the one I'm looking for. :PWhen someone asks me that question on an interview, my answer is similar. But, then I also turn the question around an ask something like, "Why, do you folks use a lot of cursors here?". If it's the kind of company that I know I'll like working for, they'll immediately puke in the basket at the very thought. Makes for a good ice-breaker. :DSat, 04 Oct 2008 15:57:33 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspx[quote][b]Jeff Moden (10/3/2008)[/b][hr][quote][b]Andrew Gothard (10/3/2008)[/b][hr][quote]...it's a snakepit of cursors and totally unnecessary temp tables and runs forever?[/quote]You just described the code at my new job... :P except instead of cursors, it's just the opposite... huge, slow joins with criteria that make it impossible to get any better than scans on some really large tables instead of seeks (or starter seeks), crazy indexes on everything that keep simple inserts and updates from ever having a chance of being fast and still don't help selects, using replication like it was a stored procedure, and a really poor general understanding of how a database should work. Even the ETL side of the house is a "bomb" because it's riddled with Perl scripts and other external handlers. They used Perl [/quote]http://upload.wikimedia.org/wikipedia/en/f/f4/The_Scream.jpg[quote]You managers out there... take heed of the code. There's never really enough time to do it again... you need to do it right the first time even if it takes a little longer (which it really doesn't). Taking heed of the code also means hiring great Developers and I've found that a whole lot of letters behind their name doesn't necessarily mean they're a great developer... learn how to do a bloody interview! ;)[/quote]Nice idea - but the problem is that explaining database specific issues to certain types of developer is like trying to explain general dev issues to a particularly stroppy end-user. If they don't understand it - how do they interview on it? I've been asked in the past "Are you good with cursors" "Brilliant - I never use them". This is often misunderstoodSat, 04 Oct 2008 15:36:54 GMTandrew gothardhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspx[quote][b]Jeff Moden (10/2/2008)[/b][hr][quote][b]Andrew Gothard (10/2/2008)[/b][hr][quote][b]J (10/2/2008)[/b][hr][i]"'dbo.selectthisandthat"[/i]For my taste, I find this harder to read (mid-50 eyesight and all), it requires more attention to figure out the meaning of the name. I feel something is lost in readability - for humans.I am sure there are other tricks to improve performance by making the code (a bit) less readable...[/quote]Don't think it's anything to do with age mate - IMO it's just a horrible crabby style of coding that deliberately ignores any consideration of legibility or the way people actually read. Right up there with everything on one line, no spaces after commas, no spaces after keywords and sundry other vileness leading to unreadable gibberish[/quote]Wow! Two more folks with the right idea! I love it! :)[/quote]Well - don't get me started on using **** like x, t1, t2 etc as pseudonyms for tables with meaningful names either. I have heard two 'reasons' for this. "It saves typing". FFS! It makes it more readable. Eh? Ok - if you're working with something like BaaN and use tblSalesHeaderCompanyCompanyName rather than ttdsls040400 - fair enough. If you're telling me that c in the middle of god knows how many lines rather than the actual table name is 'more readable' than you're talking utter nonsense.I don't use x as a variable name when I'm coding - I don't use it to refer to a table with a meaningful name either.Sat, 04 Oct 2008 15:08:41 GMTandrew gothardhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspx[i]Jeff, when you get that place straightened out, write it all down and publish it as "Tales from the deep Kimchie"; sounds like it would be best seller! [/i]YES !!! Go for it!Fri, 03 Oct 2008 08:49:05 GMTJ-440512http://www.sqlservercentral.com/Forums/Topic566838-146-1.aspxJeff, when you get that place straightened out, write it all down and publish it as "Tales from the deep Kimchie"; sounds like it would be best seller!Fri, 03 Oct 2008 05:45:12 GMTJim Russell-390299http://www.sqlservercentral.com/Forums/Topic566838-146-1.aspx[quote][b]Andrew Gothard (10/3/2008)[/b][hr][quote]...it's a snakepit of cursors and totally unnecessary temp tables and runs forever?[/quote]You just described the code at my new job... :P except instead of cursors, it's just the opposite... huge, slow joins with criteria that make it impossible to get any better than scans on some really large tables instead of seeks (or starter seeks), crazy indexes on everything that keep simple inserts and updates from ever having a chance of being fast and still don't help selects, using replication like it was a stored procedure, and a really poor general understanding of how a database should work. Even the ETL side of the house is a "bomb" because it's riddled with Perl scripts and other external handlers. They used Perl because they couldn't think of a way to do an "impossible" split in T-SQL. I've rewritten one of those scripts, all in T-SQL... it used to take 40 minutes to execute... now, it only takes 90 seconds. Because that script get's used about 200 times a day, they actually had to build a staging scheduler and a monster support system around it. It makes it very difficult to do any sort of "rerun", etc, etc. We'll be able to do away with that whole system and replace it with just a couple of high performance stored procedures that also happen to be highly scalable.And, the way they used DTS is an absolute nightmare... everything we do requires the handling of multiple files across multiple customer databases. There's more bloody custom built, unnecessary Active-X components just to do a simple loop back or to get a file list from a directory than you can shake a stick at and it's all as slow as molasses in January.Hardcoding is rampant. We can't even change the main system password because they hardcoded it into GUI's, stored procs, and a bazillion DTS jobs. And, heh, there might be 3 lines of embedded comments for every 500 lines of stored procedure.The cool part about all of that is... none of the original folks are at the company anymore. We have some great people who made some great documentation for the system in the last several months that also happen to be some pretty good designers and Developers on top of that and we're rebuilding the system one sick stored proc or DTS job at a time. They hired me because they recognized that when you're handling as much data as the system requires, you should probably do it in the database and they just didn't know how to do it. Things like that "impossible" split I told you about.I told you that story to tell you this one... this whole miserable system is what happens when you don't have coding standards and when you want everything in a hurry. I've even had to write "verification code" because no one was able to prove that the system was actually working correctly. The company was in deep Kimchie because of the code that is the bread-n-butter of the company couldn't handle any scalability to speak of. It had almost gone bankrupt because of major customer disappointment in all areas just because of bad, slow, non-scalable code. And, no, not even the purchase of new hardware has been able to fix any of that.You managers out there... take heed of the code. There's never really enough time to do it again... you need to do it right the first time even if it takes a little longer (which it really doesn't). Taking heed of the code also means hiring great Developers and I've found that a whole lot of letters behind their name doesn't necessarily mean they're a great developer... learn how to do a bloody interview! ;)Fri, 03 Oct 2008 05:34:20 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspx[quote]Any developer that does not do this, is a lazy developer and a person I definately do not want on my team. A little harsh maybe, but I've had to re-format many a developers code just to see what the procedure is supposed to do.A little time now can save a lot of time later.[/quote]In all honestly I'd say it's as much about competence and general attitude as 'laziness'. Occluded and badly laid out code in my experience goes hand in hand with shoddy thinking and highly sub-optimal solutions and generally unmaintainble code. Anyone else here tend to find that the more poorly and inconsistently the code is laid out the more often it's a snakepit of cursors and totally unnecessary temp tables and runs forever?Fri, 03 Oct 2008 02:29:12 GMTAndrew Gothard-467944http://www.sqlservercentral.com/Forums/Topic566838-146-1.aspxHi Jeff,Thanks for posting back about the case sensitivity of procedure executions, makes for good reading.Oh, and on the point of the dba getting over-ridden by 'business needs'. Trust me, he did get angry and I beleive he even ended up resigning over some similar issues where he was not being taken notice off.I agree 100% with the previous comments on coding standards. I mean, whats the point of having a dba if you dont let them do there job.JackalFri, 03 Oct 2008 01:49:19 GMTNicHopperhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspx[quote][b]Jackal (10/2/2008)[/b][hr]Hi Jeff,The following article contains a sample test.http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/hitsandmisses/1776/When I replicated the test (in SS2K), it did appear to be as the article listed. However, re-doing the test this morning (in SS2K5) does not provide the results as the article defines. I guess it would be best for Mike to comment on this, as it is his article.Jackal[/quote]Ok... this one really intrigued me especially since I've never heard of such a thing before. Thanks for bringing it up, Jackal! Even though the article is well above average for readability and clarity and the author makes some really good points that may have been true in the past, the testing I did pretty much convinces me that the whole article has the basic fault of being incorrect on every point except for the point about the two part naming convention.Here's the proc I made...[code]USE AdventureWorks--DROP PROCEDURE dbo.TestProcedureGOCREATE PROCEDURE dbo.TestProcedure @LastName nvarchar(50)ASSELECT * FROM Person.Contact WHERE LastName = @LastNameGO[/code]And here's the code I ran from two different SPID's...[code]Exec dbo.TestProcedure @LastName = 'Miller'Exec TestProcedure @LastName = 'Miller'Exec dbo.testprocedure @LastName = 'Miller'Exec dbo.TestProcedure @lastname = 'Miller'Exec dbo.TestProcedure @Lastname = 'Miller'Exec dbo.TestProcedure @LastName = 'Miller'Exec dbo.TestProcedure @LastName = 'Miller'Exec TestProcedure @LastName = 'Miller'Exec TestProcedure @LastName = 'Miller'Exec dbo.testprocedure @LastName = 'Miller'Exec dbo.TestProcedure @lastname = 'Miller'Exec dbo.TestProcedure @Lastname = 'Miller'Exec dbo.TestProcedure @LastName = 'Miller'Exec dbo.TestProcedure @LastName = 'Miller'Exec TestProcedure @LastName = 'Miller'[/code]Notice that the first call to the procedure is in the absolute correct case for both the proc name and the parameter. Here's what I got on the first set of runs after the proc was made... I have a couple of other things turned on like CACHE INSERT and RECOMPILE. Do notice that even the proc with the 1 part naming convention and all case "flavors" that I used all got cache hits... not a single cache miss except for the original "compile" of the script....[img]http://www.sqlservercentral.com/Forums/Attachment1518.aspx[/img][img]http://www.sqlservercentral.com/Forums/Attachment1519.aspx[/img]Like I said, the article was very well written and may have been true, at one time... but as of SQL Server 2005 SP2, most of that article is not true. Do notice, however, how long it took the 1 part naming convention to run until SQL Server really "got the idea" of what to run. That's why it's so very important to use at least a 2 part naming convention. :)Thu, 02 Oct 2008 20:51:21 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspx[quote][b]Andrew Gothard (10/2/2008)[/b][hr][quote][b]J (10/2/2008)[/b][hr][i]"'dbo.selectthisandthat"[/i]For my taste, I find this harder to read (mid-50 eyesight and all), it requires more attention to figure out the meaning of the name. I feel something is lost in readability - for humans.I am sure there are other tricks to improve performance by making the code (a bit) less readable...[/quote]Don't think it's anything to do with age mate - IMO it's just a horrible crabby style of coding that deliberately ignores any consideration of legibility or the way people actually read. Right up there with everything on one line, no spaces after commas, no spaces after keywords and sundry other vileness leading to unreadable gibberish[/quote]Wow! Two more folks with the right idea! I love it! :)Thu, 02 Oct 2008 20:22:08 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspx[quote][b]Jackal (10/2/2008)[/b][hr]Hi J,I'm not saying that a dba's views would be ignored and I dont want to wonder off track. It's just in situations I have worked in, business requirements often come before the dba's requirements.I have seen many a dba get angry because 'some code' has had to go in quickly to do this or that.I agree with you 100% in your views though.[/quote]Obviously, the DBA's didn't get angry enough. It doesn't take any more time to do it right and, if the DBA was doing his/her job, they'd simply refuse to promote the code. Of course, if they really wanted to do the job right, they'd have the nads to write a set of standards and get management's buy-in or find a job at a company that would.DBA's that let crap code into the server just because someone wants it real bad, isn't really doing the job a DBA should be doing... protecting the server, the data, and the code. :hehe:Remember, "If you want it [i]real bad[/i], that's the way you'll usually get it." :PThu, 02 Oct 2008 20:20:20 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspx[quote][b]Jackal (10/2/2008)[/b][hr]Hi Jeff,No problem, let me your views when you've had a look.Just on another point, ANSI 89 vs ANSI 92, do you know where I can find a quick summary of the benefits of ANSI 92 over 89?ThanksJackal[/quote]Heh... I can tell you in the form of a couple of questions... What's the difference between a 4 function calculator and one with a square root key? If you're doing something complex, wouldn't you rather have a scientific or business calculator to use? :DI've found that, if you try to do anything really worthwhile in SQL, true code portability is pretty much a myth. Lots of folks will tell you that they try to write code as close to ANSI standards as they can and will actually go out of the way to do something in a more difficult manner to try to make code portable rather than using the extensions built into either SQL Server or Oracle or whatever SQL dialect they happen to be strapped with. That might be ok for low level GUI code, but when it comes to high performance, highly scalable batch code, I use ALL the tools available in whatever RDBMS I happen to be using.For example... SQL Server doesn't have a CONNECT BY clause... would you give that baby up in Oracle just because a lot of RDBMS's don't have it and it might make your code non-portable? Hell no! Would you give up the power of UPDATE FROM in SQL Server just because Oracle doesn't have it. Hell no! Yeah, I know... MERGE is the way to go in Oracle... would you give it up just because it's not portable to most RDBMS's yet. Heeeelllll no!Heh... that's my long winded version of saying, "No, sorry... I have no links that explain the differences between ANSI 89 and ANSI 92." :hehe:Thu, 02 Oct 2008 20:14:13 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspx[quote][b]John Oliver (10/2/2008)[/b][hr]Thankyou Jeff for the vote of confidence. It is good to know I also have a like minded person somewhere out there.It is always troubling when you are at someones desk when they ask for help and you sit there and have to re-format their code.Of course, the response you get is "Dont bother with that, I just need the thing to work" !!GRRRrrrr.And Jeff, I love my comments as I'm usually the poor sucker who has to re-visit my own code in 6 - 12 months time, and I'm always asking myself "Why did I do it that way?".[/quote]Agreed with all... I like your "tag line" on your posts, too, but my favorite would be [i]"Sometimes banging the [b]developer's [/b]head against a wall is the only solution." [/i]:PThu, 02 Oct 2008 19:55:03 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspx[quote][b]Jackal (10/2/2008)[/b][hr][quote][b]J (10/2/2008)[/b][hr][i]"This way I can just tell the developer to run stored procedure 'dbo.selectthisandthat' and not have to worry about them calling 'dbo.SelectThisAndThat' and getting potential cache misses."[/i]Would it be so much more difficult to order the developpers to use the exact name and casing as shown in the list of stored procedures ?[/quote]I understand your point J, and I agree with you 100%, but based off past experiences asking developers to doing something and them actually doing it are two very different things. I guess it depends the scenario and the extent of control you have over the database.[/quote]Heh... I fixed that in my previous shop... we instantiated standards and code reviews. That means that proper formatted, highly readable code became of part of their job and it figured strongly on merit increases and COL raises. You can bet dollars to Navy beans they turned to. ;)My feeling is that the DBA has the final say so on what goes into the database... the standards we put in place gave the DBA the right to override any manager including the CIO and the CEO when it came to naming conventions, code functionality, code readability, code performance, and code scalability... schedules be damned! Of course, all that stuff was carefully laid out in the standards, the developers all had copies of it, and code review rejections for standards violations were kept track of.As a result of all we did, change controls and the related post implementation sanity checks dropped from an average of 4 hours to just 15 minutes. Rework in the QA stage went from 70% down to less than 5%. The time to research lengthy/complicated code modifications went down from an average of 2 days to about 15-30 minutes. And the time difference for the developers to do it right instead of producing the unreadable crap they were previously writing was almost negligible.When we first changed to the standards and started to enforce them, almost every developer got pissed off... some whiners even quit (thank goodness) because of it. As time wore on, they got used to it and developed some rather remarkable code that always ran first time (ie. very near zero defects). New developers that came in were told about the standards and the related expectations before they were hired. Quality of the the code went up, performance of the code when up, scalability of the code went up, deadlocks and blocking went down, and rework just about disappeared."Make it work, make it fast, make it pretty... and it aint done 'til it's pretty!" ;)Thu, 02 Oct 2008 19:50:05 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspxThankyou Jeff for the vote of confidence. It is good to know I also have a like minded person somewhere out there.It is always troubling when you are at someones desk when they ask for help and you sit there and have to re-format their code.Of course, the response you get is "Dont bother with that, I just need the thing to work" !!GRRRrrrr.And Jeff, I love my comments as I'm usually the poor sucker who has to re-visit my own code in 6 - 12 months time, and I'm always asking myself "Why did I do it that way?".Thu, 02 Oct 2008 19:39:22 GMTJohn Oliverhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspx[quote][b]John Oliver (10/2/2008)[/b][hr]You have found one of my little bug bears here. There is no excuse for producing badly formatted code. Just because code has to go in quickly does NOT mean a developer has to produce garbage code that cannot be read easily.Any developer worth their weight should automatically produce code (be it for a production release or just simply writing a query to get some information) that is both readable and sensibly formatted without this taking any additional time.Any developer that does not do this, is a lazy developer and a person I definately do not want on my team. A little harsh maybe, but I've had to re-format many a developers code just to see what the procedure is supposed to do.A little time now can save a lot of time later.[/quote]Bloody well outstanding, John! I'm [font="Arial Black"][size="3"]SO[/size][/font] happy to finally meet someone who feels exactly the same way I do about this whole formatting thing and actually has the hair to say it out loud instead of me always being the one. :)The big thing is in troubleshooting code. I've had to do exactly what you do ...[quote]but I've had to re-format many a developers code just to see what the procedure is supposed to do[/quote]...a million times both at work and on these fine forums. I can almost always tell that code is going to be absolute crap for performance and/or resources just by looking at the format... if the developer (using the term loosely) doesn't give a rat's patooti about how it looks, they usually also don't care how well it works or not.Add the requirements for proper embedded documentation that explains the "Why" of the code and you'll have found a kindred spirit in me. :DAnd, yes... I check for code readability and the use of common sense standards on interviews. If that offends someone, then they can simply go somewhere else to find a job. ;)Thu, 02 Oct 2008 19:26:53 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspxYou have found one of my little bug bears here. There is no excuse for producing badly formatted code. Just because code has to go in quickly does NOT mean a developer has to produce garbage code that cannot be read easily.Any developer worth their weight should automatically produce code (be it for a production release or just simply writing a query to get some information) that is both readable and sensibly formatted without this taking any additional time.Any developer that does not do this, is a lazy developer and a person I definately do not want on my team. A little harsh maybe, but I've had to re-format many a developers code just to see what the procedure is supposed to do.A little time now can save a lot of time later.Thu, 02 Oct 2008 17:04:40 GMTJohn Oliverhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspxHi J,I'm not saying that a dba's views would be ignored and I dont want to wonder off track. It's just in situations I have worked in, business requirements often come before the dba's requirements.I have seen many a dba get angry because 'some code' has had to go in quickly to do this or that.I agree with you 100% in your views though.Thu, 02 Oct 2008 09:29:13 GMTNicHopperhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspxJackall,If "your" developers ignore your advice, you can bet there will always be one if not two who will decide that "selectthisandthat" is not legible enough and improve the casing. No matter how much you try, you cannot "legislate" ego problems out of existence with casing style. And without seeing this as an ego problem, I would object to such casing. (It is an another matter if I would decide to ignore a DBA though. I just might develop my code with what I consider better casing and before releasing the code, use search and replace to restore the recommended casing).Thu, 02 Oct 2008 09:22:39 GMTJ-440512http://www.sqlservercentral.com/Forums/Topic566838-146-1.aspx[quote][b]J (10/2/2008)[/b][hr][i]"This way I can just tell the developer to run stored procedure 'dbo.selectthisandthat' and not have to worry about them calling 'dbo.SelectThisAndThat' and getting potential cache misses."[/i]Would it be so much more difficult to order the developpers to use the exact name and casing as shown in the list of stored procedures ?[/quote]I understand your point J, and I agree with you 100%, but based off past experiences asking developers to doing something and them actually doing it are two very different things. I guess it depends the scenario and the extent of control you have over the database.Thu, 02 Oct 2008 09:14:30 GMTNicHopperhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspx[i]"This way I can just tell the developer to run stored procedure 'dbo.selectthisandthat' and not have to worry about them calling 'dbo.SelectThisAndThat' and getting potential cache misses."[/i]Would it be so much more difficult to order the developpers to use the exact name and casing as shown in the list of stored procedures ?Thu, 02 Oct 2008 09:09:13 GMTJ-440512http://www.sqlservercentral.com/Forums/Topic566838-146-1.aspx[quote][b]J (10/2/2008)[/b][hr][i]"'dbo.selectthisandthat"[/i]For my taste, I find this harder to read (mid-50 eyesight and all), it requires more attention to figure out the meaning of the name. I feel something is lost in readability - for humans.I am sure there are other tricks to improve performance by making the code (a bit) less readable...[/quote]Don't think it's anything to do with age mate - IMO it's just a horrible crabby style of coding that deliberately ignores any consideration of legibility or the way people actually read. Right up there with everything on one line, no spaces after commas, no spaces after keywords and sundry other vileness leading to unreadable gibberishThu, 02 Oct 2008 09:02:15 GMTAndrew Gothard-467944http://www.sqlservercentral.com/Forums/Topic566838-146-1.aspx[i]"'dbo.selectthisandthat"[/i]For my taste, I find this harder to read (mid-50 eyesight and all), it requires more attention to figure out the meaning of the name. I feel something is lost in readability - for humans.I am sure there are other tricks to improve performance by making the code (a bit) less readable...Thu, 02 Oct 2008 08:22:58 GMTJ-440512http://www.sqlservercentral.com/Forums/Topic566838-146-1.aspxHi Jeff,No problem, let me your views when you've had a look.Just on another point, ANSI 89 vs ANSI 92, do you know where I can find a quick summary of the benefits of ANSI 92 over 89?ThanksJackalThu, 02 Oct 2008 06:57:45 GMTNicHopperhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspxThanks, Jackal. I sure appreciate it. I'll take a look.Thu, 02 Oct 2008 06:23:51 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspxHi Jeff,The following article contains a sample test.http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/hitsandmisses/1776/When I replicated the test (in SS2K), it did appear to be as the article listed. However, re-doing the test this morning (in SS2K5) does not provide the results as the article defines. I guess it would be best for Mike to comment on this, as it is his article.JackalThu, 02 Oct 2008 01:50:31 GMTNicHopperhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspx[quote][b]Jackal (10/1/2008)[/b][hr]As execution plans for procedures are case sensitive.[/quote]Please show me a Microsoft document or viable test code that proves that. :)Wed, 01 Oct 2008 20:57:17 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspxI guess it's best practise then to always define a case for objects to be in, i.e always lower case. As execution plans for procedures are case sensitive.This way I can just tell the developer to run stored procedure 'dbo.selectthisandthat' and not have to worry about them calling 'dbo.SelectThisAndThat' and getting potential cache misses.JackalWed, 01 Oct 2008 09:53:54 GMTNicHopperhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspxNaming stored procedures:[font="Courier New"]stp_ prefix, arbitary, to avoid using "sp". the name of the application module which invokes the stored proc. what it doesexample:stp_EDI_Import_Customer_Order.[/font]This way, the stored procs invoked by the same module are listed one next to the other and I have to do less searching to find a particular stored proc., instead of sometimes having to go back to the application code and find out from there the name of the stored proc invoked.Or, if a stored proc is part of a chain to maintain inventory then I would use something likestp_INV_Sales_Remove_Stock,stp_INV_Receiving_Add_Stock, etc.Wed, 01 Oct 2008 09:30:45 GMTJ-440512http://www.sqlservercentral.com/Forums/Topic566838-146-1.aspxThe naming convention I prefer is as follows.Table Names : Use the plural of the entity. [code]Customers[/code]Parent Child tables : Use the plural of the last entity - lose plural on parent entity [code]CustomerOrders[/code] [code]CustomerOrderItems[/code]Stored Procedures : Prefix with "p_" First word in name is action being performed Singular Entity (even for searches that return multiple rows). By clause used for Criteria [code]p_GetCustomerOrderByCustomerName[/code] [code]p_CreateCustomerOrder[/code]Columns : No Prefix. Singular [code]Surname[/code] ID's all use singular entity name [code]CustomerID[/code]Parameters : No prefix Singular [code]@Surname nvarchar(60)[/code]Local Variables : Prefix with v_ Singular [code]declare @v_MyError int[/code]Tue, 30 Sep 2008 18:19:23 GMTJohn Oliverhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspxI wish I could have scrapped Hungarian notation on the database I've been working on for the past couple of years. Almost every table has a tbl... prefix, which adds nothing and has led to there being several views with tbl... prefixes as requirements evolved. :(My recommendations are along the lines of Jane's document. Avoid underscores. Use Pascal case. Prefix things so they group nicely independent of what they are (e.g.procedures ClientInsert & ClientUpdate rather than the other way, tables like IssueStatus, IssueText, IssueAssigneeList, etc.).Oh, for the luxury of redesigning a system from scratch... :)Tue, 30 Sep 2008 07:33:28 GMTDerek Dongrayhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspx[quote][b]Jackal (9/29/2008)[/b][hr]Hi all,Jane, thanks for posting your article, one things I did notice was;"Stored procedures should be named by function and the object they work on. Standard prefix like ‘p’, ‘usp’ or ‘sp_’ (contra-indicated by Microsoft for performance reasons) are not recommended"I understand about 'sp_' as this indicates a system stored procedure to SQL, however does the 'usp' also indicate this?Thanks,Jackal[/quote]No... 'usp' does not indicate a system stored procedure... it just indicates bad form because it uses Hungarian notation. :PMon, 29 Sep 2008 14:18:01 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspxTry...Catch instead of @li_RetCode for error handling...I forgot this is part of the SS2K5 forum. Mentioning the new way of doing things is a valid point as I thought I was in an SS2K forum.When I get to SS2K5, I will use the new constructs, but I am not about to go back through all my stored procedures to retrofit the new construct.Mon, 29 Sep 2008 08:13:13 GMTJ-440512http://www.sqlservercentral.com/Forums/Topic566838-146-1.aspxI follow the ParentChild table naming convention, but use plural names. Ends up kind of awkward sometimes, like "CustomersTypesCategories", but makes it very easy to find things in the Management Studio object window, and very easy to tell what a table is, at a glance. Keeps all related tables together in sequence.I use an underscore for many-to-many joins. "Customers_Orders" (yes, I had a database with multiple customers per order). Always named those based on the most common left-to-right in From clauses. In 2005, I like to include a synonym for the inverse name: "Orders_Customers". That way, you don't have to remember which way it goes, just use what makes sense.On the subject of output parameters, I've found them very useful a number of times. I tend to rip large procs into a series of smaller procs that are called by a master proc. Makes documentation and maintenance easier. Passing data back and forth by using input and output parameters is quite efficient in this case. If I have multiple rows to pass back and forth, I'll have the master proc build a temp table, and the sub procs insert into it. If it's not multi-row, I usually use params.On using a local variable to return error values, that works. I haven't had much use for it, since I tend these days to use Try...Catch, and have a proc specifically for handling those.On naming procs, I like to use the main table they affect/select and a very brief action description in the name. The place I currently work prefixes proc names with "ins_", "del_", "sel_", "upd_", but I can't recommend that, since it makes things harder to find in the object explorer. It's fine for single-table CRUD, but it's a mess for anything more complex.If I'm working on procs that affect the Customers table, I want them all listed under things like "CustomersCreate", "CustomersList", "CustomersDel" (based on table "Customers"). That way, if I need to refactor the table at all, I don't have to look all over the place to find them, they're all listed one after the other.Of course, that breaks down a bit in any database that's at all complex, since procs for the Orders table will often have to pull data from the Customers table (joins, Where In, etc.). Still have to select from sys.sql_modules where definition like '%customers%', to make sure I get everything. But most of the code will be in one place most of the time. Makes it easier for me.Oh, and on Jeff's thing of naming the table's ID column, on parent tables, I use "CustomerID", "OrderID", etc. On child tables, I don't use the parent table name in the ID column. So the ID for "CustomersTypes" would be "TypeID". Means I have to use a few more two-part names, but it's slightly less typing overall in the long run. (This from a guy who creates tables with names like "CustomersTypesCategories". Less typing! Ha!)Other people think different ways, and use different standards. None of them are "absolutely right", it's very subjective.Mon, 29 Sep 2008 07:59:07 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic566838-146-1.aspxAbout naming parameters:I have been using @pi_ prefix for "parameter, integer" and @ls_ prefix for "local variable, string".I am one who uses very long stored proedures to say, process incoming Electronic Data Interchange incoming customer purchase orders with multiple validaton steps, etc. And I am not talking about a few hundred lines either. TGhink about an order of magnitude higher. All set-based but (quick, hide the children) procedural code.Never swa the need to distihguish between input and output parameters, in fact, I have yet to use an output parameter. I welcome any feedback as how an output parameter could be useful in a practical applicaiton.One thing I will do is return a result code to identify processing errors, and for this I use a local variable instead of an OUTPUT parameter.: [font="Courier New"]DECLARE @li_RetCode intSET @li_RetCode = -1 IF @@ERROR <> 0 SET @li_RetCode = -2 ELSE SET @l;i_RetCode = 0IF @li_RetCode <> 0 RETURN @li_RetCode IF @@ERROR <> 0 SET @li_RetCode = -2 [/font]OR[font="Courier New"] IF @@ERROR <> 0 SET @li_RetCode = -2 ELSE SET @li_RetCode = 0IF @li_RetCode = 0 BEGIN. . IF @@ERROR <> 0 SET @li_RetCode = -2 END[/font]ORDECLARE @lb_InTransaction bitSET @lb_InTransaction = 0 IF @@ERROR <> 0 SET @li_RetCode = -2 ELSE SET @li_RetCode = 0IF @li_RetCode <> 0 GOTO RATSBEGIN TRANSET @lb_InTransaction = 0 IF @@ERROR <> 0 SET @li_RetCode = -2 IF @li_RetCode <> 0 GOTO RATS IF @@ERROR <> 0 SET @li_RetCode = -2 IF @li_RetCode <> 0 GOTO RATSCOMMIT TRANRETURN @li_RetCodeRATS:IF @lb_InTransaction = 1 ROLLBACK TRANRETURN @li_RetCodeAny constructive comment on the above is welcome, especially if someone can point out why this is a bad idea.Mon, 29 Sep 2008 07:03:53 GMTJ-440512