Transcript SQL Server 2012: Indexing

SQL IMPLEMENTATION & ADMINISTRATION
Indexing & Views
LET’S TALK INDEXING
One of the most important routes to high performance in a SQL Server database is the index.
Indexes speed up the querying process by providing swift access to rows in the data tables,
similarly to the way a book’s index helps you find information quickly within that book.
The primary reason indexes are built is to provide faster data access to the specific data your
query is trying to retrieve. This could be either a clustered or non-clustered index. Without
having an index SQL Server would need to read through all of the data in order to find the rows
that satisfy the query. If you have ever looked at a query plan the difference would be an Index
Seek vs a Table Scan as well as some other operations depending on the data selected.
Indexes directly affect the performance of database applications. This article uses analogies to
describe how indexes work. The estimated execution plan feature of the Query Window is
utilized to compare the performance of two queries in a batch.
“An index makes the query fast” is the most basic explanation of an index.
An index is a distinct structure in the database that is built using the create index statement. It
requires its own disk space and holds a copy of the indexed table data. That means that an
index is pure redundancy. Creating an index does not change the table data; it just creates a
new data structure that refers to the table. A database index is, after all, is very much like the
index at the end of a book: it occupies its own space, it is highly redundant, and it refers to the
actual information stored in a different place.
INDEXING
SQL Server 2012: Indexing
Searching in a database index is like searching in a printed telephone directory. The
key concept is that all entries are arranged in a well-defined order. Finding data in
an ordered data set is fast and easy because the sort order determines each entries
position.
A database index is, however, more complex than a printed directory because it
undergoes constant change. Updating a printed directory for every change is
impossible for the simple reason that there is no space between existing entries to
add new ones. A printed directory bypasses this problem by only handling the
accumulated updates with the next printing. An SQL database cannot wait that long.
It must process insert, delete and update statements immediately, keeping the
index order without moving large amounts of data.
Indexes are created on columns in tables or views. The index provides a fast way to
look up data based on the values within those columns. For example, if you create
an index on the primary key and then search for a row of data based on one of the
primary key values, SQL Server first finds that value in the index, and then uses the
index to quickly locate the entire row of data. Without the index, a table scan would
have to be performed in order to locate the row, which can have a significant effect
on performance.
INDEXING
SQL Server 2012: Indexing
In addition to an index being clustered or non-clustered, it can be configured in other ways:
Composite index: An index that contains more than one column. In both SQL Server 2005 and
2008, you can include up to 16 columns in an index, as long as the index doesn’t exceed the
900-byte limit. Both clustered and non-clustered indexes can be composite indexes.
Unique Index: An index that ensures the uniqueness of each value in the indexed column. If the
index is a composite, the uniqueness is enforced across the columns as a whole, not on the
individual columns. For example, if you were to create an index on the FirstName and
LastName columns in a table, the names together must be unique, but the individual names
can be duplicated.
A unique index is automatically created when you define a primary key or unique constraint:
Primary key: When you define a primary key constraint on one or more columns, SQL
Server automatically creates a unique, clustered index if a clustered index does not
already exist on the table or view. However, you can override the default behavior
and define a unique, non-clustered index on the primary key.
Unique: When you define a unique constraint, SQL Server automatically creates a unique,
non-clustered index. You can specify that a unique clustered index be created if a
clustered index does not already exist on the table.
Covering index: A type of index that includes all the columns that are needed to process a
particular query. For example, your query might retrieve the FirstName and LastName columns
from a table, based on a value in the ContactID column. You can create a covering index that
includes all three columns.
INDEXING
SQL Server 2012: Indexing
LET’S TALK INDEXING ON VIEWS
Creating Indexed Views
An indexed view is different from other views because it is materialized and stored on disk in
the same way as a table. An interesting point about an indexed view is that the query optimizer
may reference a view to improve performance even if it is not referenced in the query.
You use an indexed view which calculates aggregated data and for use when the cost of
materializing the view proves less than the cost of aggregating data in a standard view.
Before you can create an indexed view, you need to make sure that all the referenced tables
meet a few requirements. First, all referenced tables must be contained within the same
database. If any computed columns in the base tables are not deterministic, they must be
removed. Deterministic is defined as always returning the same value or result set. Since a
requirement of an indexed view is that it be deterministic, all the columns in the base table
must also be deterministic. You can use the following code, which leverages the
COLUMNPROPERTY scalar function to determine if the column is deterministic.
A clustered index cannot be created on a view if the view references any nondeterministic
functions.
VIEWS
Views – Additional Information
Working with Indexed Views
• In reality, you’ll use queries that require a lot of calculation and data manipulation; such
complex queries can take a toll on your system resources and thus slow it.
• Use indexed views to get around this bottleneck.
• Doing so does, however, slow data entry, as both the data itself and the materialized view
must be updated.
• Using indexes on complex views has its benefits, the first being performance.
• Every time a view is queried, SQL Server must materialize the view.
• Materialization is the process of performing all the JOINs and calculations necessary to
return a resultset to the user.
• If the view is complex (requiring a large number of calculations and JOINs), indexing it can
speed up access because the resultset will never again need to be materialized.
• The Query Optimizer is the component in SQL Server that analyzes your queries, compares
them with available indexes, and decides which index returns a result set the fastest.
• Once you’ve indexed a view, the Query Optimizer considers this view in all future queries
no matter what you’re querying.
• This means queries on other tables may benefit from the index you create on the view.
VIEWS
Views – Additional Information
Disadvantages of Indexing a View
• The disadvantage of indexing a view comes from the overhead it incurs on the system.
• First, indexed views take up disk space because they’re stored as separate objects in
the database that look just like tables with a clustered index.
• Because clustered indexes store the actual data rather than just a pointer to the data
in the base tables, they require extra disk space.
• When you update the underlying table or tables of the view, however, the indexed view
must be immediately updated to reflect the changes to the base table.
• This means if you create an indexed view on a table and then make changes to the
records in that table, SQL Server automatically updates the view at the same time.
Partitioned View
• A partitioned view displays horizontally divided data from a set of member tables across
one or more servers, making the data appear as if from one table.
• The partitioned view uses the UNION ALL clause to combine the results of SELECT
statements on all the member tables in a single result set.
•
•
•
SQL Server distinguishes between:
• Local partitioned views
• Distributed partitioned views
In a local partitioned view, all participating tables and the view itself reside
on the same instance of SQL Server. Use partitioned data locally by using
partitioned tables rather than partitioned views.
In a distributed partitioned view, at least one of the participating tables
resides on a different (remote) server.
VIEWS
Views – Additional Information
As beneficial as indexes can be, they must be designed carefully. Because they can
take up significant disk space, you don’t want to implement more indexes than
necessary. In addition, indexes are automatically updated when the data rows
themselves are updated, which can lead to additional overhead and can affect
performance. As a result, index design should take into account a number of
considerations.
You can partition views in the same way you can partition tables and for the same
reasons: store parts of your views on different spindles or even different servers.
SUMMARY
Summary