"SQL Server Indexing for Developers" by Ray Spaeth

There are multiple ways to index SQL Server tables and the right indexes can reduce query times by orders of magnitude. The wrong indexes may not only never be used but could degrade overall database performance. Knowing how the query optimizer creates a query plan (e.g. does it use a table scan, an index scan, or an index seek) is very important in designing indexes. Many developers do not have a DBA on their team so this talk will focus on the core techniques a developer should know to do good index design. The talk will be a mix of slides and performance demos. We'll start with a quick overview of how indexes are stored in the database and the importance of having up to date index statistics and avoiding index fragmentation. We'll contrast clustered versus non-clustered indexes and take a close look at how a particular kind of non-clustered index, the covering index, can be extremely helpful to query performance. We'll also compare primary keys versus clustered indexes and examine the case where the column(s) in the primary key should not be the same as the column(s) in the clustered index. We'll then look at how to structure the where clause of queries so they are "SARGable" so the query optimizer is more likely to use an index seek. We'll also look at other topics such as index selectivity and the impact of too many indexes in an OLTP database. If time permits, we'll look at other index-related topics such as indexed views.

Bio: Ray Spaeth

Ray Spaeth is an independent software developer and database administrator of retail and wholesale support systems. He started building database applications in the late 1980s with dBase and gradually migrated from DOS to Windows based PC applications. In 1995 he started building client-server applications using SQL Server and created a comprehensive ordering/inventory/invoicing system for Lane-Borgesesia, then a large international yarn wholesaler based in Colorado Springs. Also during the mid 90s Western Pacific Airlines awarded him a contract to build and support the system that ran their retail stores in Colorado Springs and Denver. For the past 16 years he's been building a complex set of client-server, server-only, and web service applications for CMIC Consulting, a Texas based company that provides much of the IT infrastructure for the National Marketing Group (NMG), a marketing/buying group representing over 6500 independent appliance and electronics dealers in the US and Canada. These applications utilize eight SQL Server databases to support or run NMG's processes for order management, pricing, dealer merchandising, data driven dealer price tags, dealer web sites, dealer sales reporting, and competitor price checking. When not developing software and database systems, he likes to go cross-country biking, hiking, mountain climbing (has climbed ten of Colorado's 14ers), and flat water kayaking.

This meeting will be held in the Team USA Swimming building at the Olympic Training Center at our normal meeting time of 5:30 PM - CLICK here TO GET THE LOCATION AND TIME DETAILS.