Writing efficient and optimized SQL Server queries and stored procedures needs to be known to some degree by most developers even if you are not a database administrator. I think a good understanding of the basics can get you pretty far as a developer on most projects. There are plenty of blogs, websites, books and other resources where more specialized developers have graciously offered their expertise to help you during those times when it is better to know WHERE to find the answer as opposed to KNOWING the answer.
25 SQL Commandments
An article, called 25 SQL Commandments, is a list of tips to help a developer avoid the plague of poorly written SQL statements ( Read Full Article ). The article itself is written for Oracle with some specific Oracle commandments, but a lot of the commandments hold true for SQL Server, too. The 25 SQL Commandments briefly are:
- Know your data and business application well.
- Test your queries with realistic data.
- Write identical SQL statements in your applications.
- Use indexes on the tables carefully.
- Make an indexed path available.
- Use Explain Plan and TKPROF where possible.
- Think globally when acting locally.
- The WHERE clause is crucial.
- Use WHERE instead of HAVING for record filtering.
- Specify the leading index columns in WHERE clauses.
- Evaluate index scan vs. full table scan.
- Use ORDER BY for index scan.
- Know thy data.
- Know when to use large-table scans.
- Minimize table passes.
- Join tables in the proper order.
- Use index-only searches when possible.
- Redundancy is good.
- Keep it simple, stupid.
- You can reach the same destination in different ways.
- Use the special columns.
- Use explicit cursors over implicit cursors.
- Explore and take advantage of the Oracle parallel query option.
- Reduce network traffic and increase throughput.
SQL Server Indexes
If you look back at a number of my articles, humbly categorized as High Performance ASP.NET Websites Made Easy, I had one particular article called
where I talked about the fundamental need to understand SQL Server Indexes and using Query Analyzer to understand Execution Plans for those queries in your web application. The 25 SQL Commandments talk a lot about indexes and how you want to take advantage of those indexes in your WHERE and ORDER BY clauses as well as in your JOIN operations.
Use Cases - Knowing Your Data and How It Will Be Used
Although over time your customer will always come up with new ways of accessing and using your SQL Server Data that you never knew possible, the key to organizing your data and writing your queries and stored procedures is understanding how the data will be used by each role in your application. These roles and uses for the data are usually organized into Use Cases. Use Cases not only help you break your project into iterations ( Agile and Iterative Development Life Cycle ), but also help you understand how the data will be accessed in your web application.
Those queries and stored procedures that will be 1) used more often and 2) whose data will unlikely be cached by the client will be those queries and stored procedures that need higher precendence in terms of performance.
High Performance ASP.NET Websites Made Easy
Listening To: Outkast - I Like The Way You Move
Drinking: Matcha Green Tea