Share via

20 tips to write a good stored procedure (is really just 12)

  • Article

A few days ago there was an article with 20 tips to write a good stored procedure (requires free registration to read). The problem is that there are really only 12 good tips (and 4 bad and 4 neither good or bad). So let me go over the tips one by one and comment on them:

  1. Capital letters for keywords and proper indentation. With todays code editors with syntax high lighting I don't see why you want to high light keywords with capital letters. The code editor will do that for you. And suggesting proper indentation is not really a tip to write a good stored procedure. It's common (coding) sense! So I don't think this one counts... Score (good-not really-bad advice IMHO): 0-1-0.
  2. Use SQL-92 syntax for joins. If MS SQL server drops support for the old syntax this is good advice. Score: 1-1-0
  3. Use as few variables as possible. The article mentions cache utilization as an argument. Sounds like premature optimization to me. I'd say use as many variables as makes sense to make the code most readable. If that turns out to be a problem, then you optimize. So in general I found this advice to be bad. Score: 1-1-1
  4. Minimize usage of dynamic queries. Kudos to the article to pointing out how to minimize the bad of dynamic queries and I guess technically minimizing could mean zero but that is really the only good advice; don't use dynamic queries. So once again a bad, or at least misleading advice IMHO. Score: 1-1-2
  5. Use fully qualified names. If you don't do this you might end up with some weird behavior so this is a good advice. Score: 2-1-2
  6. Set NOCOUNT on. Good advice: Score: 3-1-2
  7. Don't use sp_ prefix. Score: 4-1-2
  8. KEEPFIXED PLAN. Learn from this article and use it correctly. Hard to argue with "learn something and use it right". Score: 5-1-2
  9. Use select instead of set. Once again performance is mentioned as a motivator. However the potential bad side effects of using select rather than set are more important in my opinion. The problem with select is that the variable might not be set if a query returns no rows and the set gives you an error if the select returns more than one row. Read more about it here. I'd definitely prefer set over select. Score: 5-1-3
  10. Compare the right thing in the where clause. This advice just confuses me. The article talks about what operators are the fastest and then refers to this page talking about preference. Even though the article is confusing on this point the basic idea is correct. For example using IN is generally faster than NOT IN. So I'll call this one a draw. Score: 5-2-3
  11. Avoid OR in WHERE clause. This is good advise for good performance. Score: 6-2-3
  12. Use CAST over Convert. CAST is SQL92 standard. Convert is not. Score: 7-2-3
  13. Avoid distinct and order by. Once again this is common sense. Don't do things you don't need... Score: 7-3-3
  14. Avoid cursors. This falls into the same category as dynamic queries to me. The only good advice is don't use cursors. Score: 7-3-4
  15. Select only the columns you need. Common sense! Score: 7-4-4
  16. Sub queries vs joins. Article lists a few good rule of thumbs. I think you should use whatever is most readable. Score: 8-4-4
  17. Create table vs select into. Article points out important differences. Score: 9-4-4
  18. Use variables instead of temporary tables. Score: 10-4-4
  19. Use proper indexes. Score: 11-4-4
  20. Use profiler. Many tips in the article suggest you do things to improve performance. But I think doing so before you know you have a problem is a waste of time and resources. So this advice is actually one of the best advices in the article. Score: 12-4-4

Comments

  • Anonymous
    August 19, 2009
  1. "# Set NOCOUNT off. Good advice: Score: 3-1-2" That should be "Set NOCOUNT ON".
  2. "The problem with select is that the variable might not be set if a query returns no rows" In the example, there is no query: SELECT @Var1 = @Var1 + 1, @Var2 = @Var2 - 1 vs: SET @Var1 = @Var1 + 1 SET @Var2 = @Var2 - 1
  3. "Avoid OR in WHERE clause." The example given results in a non-sargable query, which is bad for performance: SELECT emp_name FROM table_name WHERE LOWER(emp_name) = 'edu' The query optimiser cannot use an index on the emp_name column to satisfy this query. Either use a case-insensitive collation, or create a persisted computed column to store the normalized data. Also, normalizing strings to lower case is a bad idea - you should always normalize to upper case.

  • Anonymous
    August 19, 2009
    Thanks for pointing out the typo in 6. Regarding 9, I just wanted to point out a common pitfall when using select leaving variables unchanged. If the select is just a way to group a number of set it is not that big deal I think, but in my experience variables are typically set from values being selected from tables rather than not. You comment on OR is correct. The example given is not that great, but the basic idea of not using OR is still a good advice I think. From a general perspective I mean.

  • Anonymous
    November 27, 2012
    Good analysis... thank u for the Corrections You Specified.