Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Can you please recommend resources to write efficient store procedures, refactor and optimize them?

EDIT: - jcolebrand ♦

The intent of the answers to this post may be books or non-transient blogs. Please try not to refer to resources that have a tendency to disappear.

This question is being allowed as being intentful for long term purposes, but is not a typical Stack Exchange network question. Please do not use this question as merit why another question should be permissible to be asked. Reference to it may be made as a request for evaluation on a case-by-case basis.

share|improve this question
add comment

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

3 Answers

The best two books here are both from Itzik Ben Gan - Inside SQL Server T-SQL Querying and Inside SQL Server T-SQL Programming.

Read the Querying one first. Covers all the important information on how queries are processed, thinking in terms of sets, etc. Covers all aspects of querying. The second book goes into programming constructs when working with T-SQL. Amazing books.

share|improve this answer
1  
+1 These are essential reading. –  Paul White Mar 1 '13 at 23:24
add comment

Two excellent suggestions from @MikeWalsh. I would add Grant Fritchey's SQL Server 2008 Query Performance Tuning Distilled along with the excellent free ebooks from Redgate. SQL Server Execution Plans is an excellent reference to start with.

From a different angle, I strongly believe knowledge of internals are important for all aspects of working with SQL Server. To this end, both Microsoft® SQL Server® 2008 Internals and Professional SQL Server 2008 Internals and Troubleshooting are essential reading.

share|improve this answer
2  
Huge +1 on the Professional SQL Server 2008 Internals and Troubleshooting book. Maybe I'm biased because I'm tech editing the next edition ;-) But seriously - it is an excellent book for internals and it doesn't go over your head either. –  Mike Walsh Nov 13 '11 at 0:19
add comment

Here's a good book for code refactoring: Joe Celko - SQL Programming Style. I like to compare it to MS Manual of Style for Technical Publications. The latter one you use to write your articles, blogs and documentation; the first one to write your code

If you have problems with code readability (either yours or team's), this book will help you set your own standards. Here's from its review:

  • Help you write Standard SQL without an accent or a dialect that is used in another programming language or a specific flavor of SQL, code that can be maintained and used by other people.
  • Enable you to give your group a coding standard for internal use, to enable programmers to use a consistent style.
  • Give you the mental tools to approach a new problem with SQL as your tool, rather than another programming language one that someone else might not know!

However, when you set the refactoring standards, don't apply them manually, but use a tool to refactor the code

share|improve this answer
    
Can you recommend any tools that you have used? –  threeFourOneSixOneThree Sep 26 '13 at 10:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.