This document provides an overview of indexes in SQL Server, including:
- Core concepts such as how SQL Server accesses data, the need for indexes, and index structures.
- Different types of indexes including clustered, nonclustered, single column, and composite indexes.
- How to work with indexes including creating indexes, viewing index statistics, and resolving fragmentation.
- A demonstration of creating clustered and nonclustered indexes and viewing index statistics.
- An exercise lab for implementing indexes on tables.
This document provides an overview of indexes in SQL Server, including:
- Core concepts such as how SQL Server accesses data, the need for indexes, and index structures.
- Different types of indexes including clustered, nonclustered, single column, and composite indexes.
- How to work with indexes including creating indexes, viewing index statistics, and resolving fragmentation.
- A demonstration of creating clustered and nonclustered indexes and viewing index statistics.
- An exercise lab for implementing indexes on tables.
This document provides an overview of indexes in SQL Server, including:
- Core concepts such as how SQL Server accesses data, the need for indexes, and index structures.
- Different types of indexes including clustered, nonclustered, single column, and composite indexes.
- How to work with indexes including creating indexes, viewing index statistics, and resolving fragmentation.
- A demonstration of creating clustered and nonclustered indexes and viewing index statistics.
- An exercise lab for implementing indexes on tables.
This document provides an overview of indexes in SQL Server, including:
- Core concepts such as how SQL Server accesses data, the need for indexes, and index structures.
- Different types of indexes including clustered, nonclustered, single column, and composite indexes.
- How to work with indexes including creating indexes, viewing index statistics, and resolving fragmentation.
- A demonstration of creating clustered and nonclustered indexes and viewing index statistics.
- An exercise lab for implementing indexes on tables.
Download as PPTX, PDF, TXT or read online from Scribd
Download as pptx, pdf, or txt
You are on page 1/ 35
Module 5
Introduction to Indexes Module Overview
Core Indexing Concepts
Data Types and Indexes Heaps, Clustered, and Nonclustered Indexes • Single Column and Composite Indexes Lesson 1: Core Indexing Concepts
How SQL Server Accesses Data
The Need for Indexes Index Structures Selectivity, Density and Index Depth Index Fragmentation • Demonstration: Viewing Index Fragmentation How SQL Server Accesses Data
• Table Scan • SQL Server reads all table pages • Any query can be satisfied by a table scan • Will result in the slowest response to a query • A table without indexes is called a heap
• Index • SQL Server uses index pages to find the desired rows • Different types • Clustered and nonclustered • Rowstore and columnstore The Need for Indexes Index Structures SQL Server B-Tree Structure Selectivity, Density and Index Depth
• Selectivity • A measure of how many rows are returned compared to the total number of rows • High selectivity means a small number of rows when related to the total number of rows
• Density • A measure of the lack of uniqueness of data in the table • High density indicates a large number of duplicates
• Index Depth • Number of levels within the index • Common misconception that indexes are deep Index Fragmentation
• How does fragmentation occur?
• SQL Server reorganizes index pages when data modifications cause index pages to split
• Types of fragmentation: • Internal – pages are not full • External – pages are out of logical sequence
• Detecting fragmentation • SQL Server Management Studio – Index Properties • System function – sys.dm_db_index_physical_stats Demonstration: Viewing Index Fragmentation
In this demonstration, you will see how to:
• Identify fragmented indexes • View the fragmentation of an index in SSMS Lesson 2: Data Types and Indexes
Numeric Index Data
Character Index Data Date-Related Index Data GUID Index Data BIT Index Data • Indexing Computed Columns Numeric Index Data
• Using numeric values in indexes
• Benefits • Small in size • More values can fit in a single page • Faster to read
• Negatives • Small data types will be more dense Character Index Data
• Character data types in indexes
• Benefits • Character data is often searched • Better performance than a heap
• Negatives • Slower to search than a numeric index • Can become fragmented because data does not tend to be sequential Date-Related Index Data
• Using date data types in indexes
• Benefits • Smaller in size • More values can fit in a single page • Quite faster to read
• Negatives • Small data types will be more dense GUID Index Data
• Using the GUID data type in indexes
• Benefits • Highly selective • Fast to read
• Negatives • Updates and deletes do not perform as well BIT Index Data
• Using BIT data type in indexes
• Benefits • Extremely small in size • More values can fit in a single page • Fast to read; in some circumstances could be highly selective • Negatives • The index will be very dense Indexing Computed Columns
• Indexing computed columns
• Benefits • Calculated values are stored in the index • Values updated automatically
• Negatives • Frequent changes can impair performance • Computed columns must be deterministic Lesson 3: Heaps, Clustered, and Nonclustered Indexes
Heaps Operations on a Heap Clustered Indexes Operations on a Clustered Index Primary Keys and Clustering Keys Nonclustered Indexes Operations on Nonclustered Indexes • Demonstration: Working with Clustered and Nonclustered Indexes Heaps
A heap is a table with:
• No specified order for pages within the table • No specified order for rows within each page
Data will be inserted in the first space in a page
that is found Operations on a Heap
• INSERT • Each new row can be placed in the first available page with sufficient space • UPDATE • The row can remain on the same page if it still fits; otherwise, it can be removed from the current page and placed on the first available page with sufficient space • DELETE • Frees up space on the current page • Data is not overwritten, space is just flagged as available for reuse • SELECT • Entire table needs to be scanned Clustered Indexes
A clustered index: • Has pages that are logically ordered • Has rows that are logically ordered, and where possible, physically ordered within pages • Can only be declared once on a table
The logical order is specified by a clustering key
Operations on a Clustered Index
• INSERT • Each new row must be placed into the correct logical position • May involve splitting pages of the table • UPDATE • The row can remain in the same place if it still fits and if the clustering key value is still the same • If the row no longer fits on the page, the page needs to be split • If the clustering key has changed, the row needs to be removed and placed in the correct logical position within the table • DELETE • Frees up space by flagging the data as unused • SELECT • Queries related to the clustering key can seek • Queries related to the clustering key can scan and avoid sorts Primary Keys and Clustering Keys
• Primary key • Must be unique • Cannot contain NULL values • Only one per table • Implemented as a constraint
• Clustering key • Must be unique • Specifies the logical ordering of rows • Only one per table • Can be automatically created Nonclustered Indexes
A nonclustered index : • Can be on a heap or clustered index • Will take up extra space • Needs to be updated when the underlying data is modified A table can have a maximum of 999 nonclustered indexes. Operations on Nonclustered Indexes
• INSERT • Each nonclustered index that is added to a table will decrease the performance of inserts • UPDATE • The index will need to be kept up to date if the location of the data changes • DELETE • Similar to updates, deleted data needs to be removed from the index • SELECT • Performance improvements for queries that the index covers Demonstration: Working with Clustered and Nonclustered Indexes
In this demonstration, you will see how to:
• Create a clustered index • Remove fragmentation on a clustered index • Create a covering index Lesson 4: Single Column and Composite Indexes
Single Column vs. Composite Indexes
Ascending vs. Descending Indexes Index Statistics • Demonstration: Viewing Index Statistics Single Column vs. Composite Indexes
• Indexes are not always constructed on a single
column • Composite indexes tend to be more useful than single column indexes: • Having an index sorted first by customer, then by order date, makes it easy to find orders for a particular customer on a particular date • Two columns together might be selective while neither is selective on its own • Index on A,B is not the same as an index on B,A Ascending vs. Descending Indexes
• Indexes could be constructed in ascending or
descending order • In general, for single column indexes, both are equally useful • Each layer of a SQL Server index is double-linked (that is, linked in both directions) • SQL Server can start at either end and work towards the other end • Each component of a composite index can be ascending or descending • Might be useful for avoiding sort operations Index Statistics
• SQL Server needs to have knowledge of the layout of the
data in a table or index before it optimizes and executes queries • Needs to create a reasonable plan for executing the query • Important to know the usefulness of each index • Selectivity is the most important metric • By default, SQL Server automatically creates statistics on indexes • Can be disabled • Recommendation is to leave auto-creation and auto-update enabled Demonstration: Viewing Index Statistics
In this demonstration, you will see how to:
• View the statistics of an index via Transact-SQL • View the statistics of an index with SSMS • View the database settings related to statistics Lab: Implementing Indexes
Exercise 1: Creating a Heap
Exercise 2: Creating a Clustered Index • Exercise 3: Creating a Covering Index
Logon Information Virtual machine: 20762C-MIA-SQL User name: ADVENTUREWORKS\Student Password: Pa55w.rd
Estimated Time: 30 minutes
Lab Scenario
One of the most important decisions when
designing a table is to choose an appropriate table structure. In this lab, you will choose an appropriate structure for some new tables required for the relationship management system. Lab Review
• In this lab, you learned how to create a table
without any indexes, create a table with a clustered index and add a nonclustered key in the form of a covering index. Module Review and Takeaways
