Indexing Presentation – The Basics

Attached is the slide deck for a short presentation on indexing that was created to educate software developers on the basics of indexing.

Outline:

  • What is an Index
  • Types of Indexes
  • Non Clustered Indexes
  • Clustered Indexes
  • When To Avoid A Clustered Index
  • Covered Indexes
  • Index Usage Terminology
  • Summary
  • Additional Topics

Download Indexing_Overview.pdf

Here is the text outline of the presentation.

 

Indexing Overview Presentation

Presented by Steve Stedman

SQL Server Database Consultant

Indexing Overview

  • What is an Index
  • Types of Indexes
  • Non Clustered Indexes
  • Clustered Indexes
  • When To Avoid A Clustered Index
  • Covered Indexes
  • Index Usage Terminology
  • Summary
  • Additional Topics

What is an Index

  • Similar to the index at the back of a book.
  • An index provides a shortcut to get to your data.
  • Without an index to find a specific row in a table, it would required a full table scan.
  • Types of Indexes

Two types of indexes in SQL Server

  • Non-Clustered Indexes
  •        Traditional Indexing – contains pointers to the data.
  • Clustered Indexes
  •        Reorganizes the actual data on disk.

 

Non Clustered Indexes

  • Not required, but clustered indexes are recommended
  • Contain only the data specified in the index.
  • Do not change the base layout of the tables.
  •      Index structure is separate from the base table.
  • Use pointers to get to the data.
  • Can be created on most data types including char(), varchar(), and uniqueidentifiers.
  • Only one non-clustered index can be used per table reference in a query.
  • Can improve performance with multiple columns.

Clustered Indexes

  • Causes base table structure to change.
  • Only one clustered index per table.
  • Not required on a table.
  • Should never contain char(), varchar(), varbinary(), uniqueidentifiers, or other large or widely distributed identifiers.
  • Can significantly increase the size of a table and the database.
  • Can increase performance if used correctly.

When To Avoid A Clustered Index

  • If you already have a clustered index on a table you can’t create a second one.
  • Never use a clustered index on a GUID / UniqueIdentifier

 

Covered Indexes

  • Returns query results without accessing the base table.
  • Can lead to major performance increases.
  • Applies to Non-Clustered Indexes.

All columns requested in the query are somewhere in the index regardless of :

  • Where they are in the query
  • Where they are in the index

Index Usage Terminology

  • An Index Scan accesses all the rows in the index.
  • An Index Seek uses selective rows in the index.
  • The Seek is much quicker than the scan.

 

Summary

  • What is an Index
  • Types of Indexes
  • Non Clustered Indexes
  • Clustered Indexes
  • When To Avoid A Clustered Index
  • Covered Indexes
  • Index Usage Terminology
  • Summary
  • Additional Topics

 

Additional Topics

  • Determining Index Usage
  • Indexing for Preformance
  • When an Index is not used

 

SQL Server 2008 R2 Compatibility Levels

There was no change to the compatibility levels between SQL Server 2008 and SQL Server 2008 R2.

SQL Server can run in a number of different compatibility levels, but how do you change it and how do you set it. These compatibility levels reflect the version of SQL server.

  • 60 = SQL Server 6.0
  • 65 = SQL Server 6.5
  • 70 = SQL Server 7.0
  • 80 = SQL Server 2000
  • 90 = SQL Server 2005
  • 100 = SQL Server 2008 and SQL Server 2008 R2

Changing the compatibility level in SQL Server may change the behavior of specific of the functionality of the SQL Server. Be sure to research and test before changing compatibility levels.

Here is a sample script that will show you the current compatibility level, set it to 100, then show you the current level after it has been set.

Sp_dbcmptlevel dbname
GO
Sp_dbcmptlevel dbname, 100
GO
Sp_dbcmptlevel dbname
GO

This TSQL will generate the following output:

The current compatibility level is 90.
The current compatibility level is 100.

Once you change the compatibility level, you will want to be sure that your system still runs correctly.

What is SQL Server?

  • Originated as Sybase SQL Server

    • Acquired by Microsoft and named Microsoft SQL Server in 1989

  • Recent Versions (2005, 2008, and 2008 R2)

  • But what is it?

    • A system for managing large amounts of data

    • Provides a language TransactSQL (T-SQL) to access and manipulate data

    • A multi-user server based application

    • A relational database