by Itzik Ben-Gan
A common comment from students who’ve taken my course “Advanced Transact-SQL Querying, Programming and Tuning for SQL Server 2000 & 2005” is that they didn’t realize how much they didn’t know about T-SQL. Although I didn’t create the course to s
One of the most gratifying components of the course is that many attendees say they were able to immediately apply the knowledge they acquired in the course to their production systems—sometimes within the same week they attended the class. The course’s practical nature is one of the reasons it’s one of my most popular offerings. Here’s a brief look at how I created the course and its key elements. (You can find the course’s technical description, outline, and schedule at http://learning.solidq.com.)
When I designed this course for experienced programmers and DBAs, I had the following objectives in mind:
1) Focus on set-based thinking: The course encourages students to adopt the special mindset required to write efficient set-based solutions within the relational database model—that is, to think in terms of sets. Understanding why set-based solutions are typically superior to iterative or cursor-based solutions can be difficult even for experienced professionals. However, another course objective is for students to understand SQL’s weak points and to be able to identify problems for which cursor-based solutions outperform set-based solutions.
2) Deliver practical solutions: The course needs to address the many practical problems that SQL Server programmers and DBAs face daily in production environments. So, for example, it covers coping with the challenges involved in handling temporal data, overlapping periods, paging techniques, maintaining custom sequences, pivoting techniques, custom aggregates, running aggregates, randomization, finding the top n rows for each group, the treatment of graphs (employee organizational charts, bills of materials), dynamic SQL, relational division problems, efficient stored procedure programming (compilations, recompilations, plan reuse), efficient trigger programming, concurrency (transactions, isolations, deadlocks), indexing, treatment of large volumes of data, and the list goes on.
3) Handle complex problems through simple and efficient solutions: It is important for me to show students that they can address complex problems by using simple and efficient solutions. Some programmers pride themselves on being able to develop complex code. However, although some problems can get quite complex, the key to robust, efficient solutions isn’t necessarily to write complex code, which makes your solutions harder to maintain. The key to robust, efficient solutions is to write fairly simple and natural code that is easy to maintain.
4) Emphasize performance: The course needs to help students understand the way the SQL Server engine internally processes code so they could use this knowledge to optimize their solutions. The course focuses on performance and query tuning, showing the various access methods available to the optimizer for accessing data and processing queries. Students learn how to analyze and interpret execution plans, how to index data efficiently, and how to revise their code to optimize solutions. A key to meeting this course objective was to introduce multiple solutions to each problem, explaining the circumstances in which each technique is more efficient than other approaches. The course shares many techniques and concepts that students can add to their toolbox.
5) Introduce new SQL Server 2005 features: When I designed this course, I had both SQL Server 2000 and SQL Server 2005 in mind. With every problem discussed, I demonstrate solutions that are applicable to SQL Server 2000 and solutions that use SQL Server 2005’s new language elements to simplify and optimize those solutions. The following are among the new features the course covers: new table operators (APPLY, PIVOT, UNPIVOT), the OVER clause and analytical ranking functions, new set operations (INTERSECT and EXCEPT), TOP enhancements, Common Table Expressions (CTEs) and recursive queries, the new OUTPUT clause for modification statements, the new MAX specifier for variable-length data types, the XML data type, the BULK rowset provider, CLR/.NET integration, stored procedure-related enhancements (statement-level recompile, RECOMPILE and OPTIMIZE FOR hints), exception handling with the new TRY/CATCH construct, new optimization options (USE PLAN hint, included non-key columns in indexes), and new isolation levels (SNAPSHOT, READ COMMITTED SNAPSHOT).
To get a sense of the level of material the course delivers, you can read the sample chapters from my books Inside Microsoft SQL Server 2005: T-SQL Querying (Microsoft Press, 2006) and Inside Microsoft SQL Server 2005: T-SQL Programming (Microsoft Press, 2006). You can find the sample chapters at http://www.insidetsql.com/sample_chapters.htm.
I have a passion for teaching and for SQL. Even though I’ve delivered this course many times, I always enjoy teaching it again. I discover something new in each delivery, and I hope this look at the course from the developer perspective inspires you to learn more about T-SQL’s capabilities. We might even meet in an upcoming class, where we can share our experiences with SQL Server and T-SQL!
