SQL for Developers Tips and Tricks: 1. Introduction

Lately I’ve been mostly working on research projects related to Machine Learning (both for my thesis and as a private contractor). Therefore, my ability to blog on development issues I’m encountering is somewhat limited. Therefore I have decided to start blogging about stuff I’ve learned and used along the years, both to keep it as a written memory and maybe to help some readers…

So for starter, I’ve decided to summarize various SQL issues I liked/learned/used. Some I got from fellow developers or DBA’s and some I had to learn on my own (sometimes at great personal risk to become completely crazy). I’ll try to do it in an organized fashion, each blog focusing on some other subject. The vantage point is that of a software developer not a DBA. I have seen many cases where a developer coming from C/C++ started using SQL. The first few days she invests in getting a good grip at the basic syntax (SELECT, INSERT, UPDATE, …). Then she starts writing stored procedures and learns some SQL programming concepts (like CURSORs). At some point, she starts getting acquainted with Views and Functions and learns various helpful built-in SQL functions. Then, slowly but surely, the problems start to arise, usually performance. Once the performance issues are more or less under control, there comes a time to design a new database. This new database inevitably includes many flaws, which will have to be dealt with at some point or another.

I have gone through this, and many good programmers I know have gone through a similar sequence. The purpose of this set of posts is to try and give the programmer a set of tools and knowledge base that can help her go through this ordeal with more ease and confidence. This is by no means a complete tutorial, but I still hope it will be of help. If there are any specific requests, please send a comment with the details. If I know the answer, I’ll try to asses it in the future posts.

One last thing before I present the list of issues I intend to cover – since I’m still too short in hands-on experience with SQL2005, most of the information will be provided with SQL2000 in mind. I guess that most of the issues are applicable to SQL2005 as well, although the new SQL version certainly brings a whole series of new issues (with the CLR and all) as well as additional and revised tools. Also, these posts will assume familiarity with the basic SQL syntax (SELECT, INSERT, UPDATE).

Following is the list of issues I plan to asses. This is a preliminary list and may be altered along the way, but should give you some idea of what to expect.

1. Introduction
2. Basic tools you should keep at the ready
1. Helpful web sites
2. SQL Query Analyzer – things too many people learn too late
3. SQL Profiler
3. SQL Programming for Performance – basic programming tips that can help reduce future performance problems
1. Similar code that runs faster
2. Cursors
3. Temporary tables
4. Use of indexes
4. Locks and Deadlocks – using and troubleshooting
1. Locking in SQL 2000 and isolation levels
2. Simple deadlock scenario
3. Troubleshooting locks and deadlocks
5. Troubleshooting performance problems
1. SQL Profiler to the rescue – how to configure, what to look at
2. SQL Query Analyzer - reading the Execution Plan
3. SQL Server configuration
4. Analyzing index behavior
5. Partitioned tables
6. Some advanced Tips and Tricks
1. TBD

Things I do not plan to discuss at this point:

• Accessing SQL objects programmatically (SQL DMO)
• Using ADO/ADO.NET
• OLAP
• XML Integration
• Reporting and Notification
• Scheduled tasks
• Roles and Permissions