DAY-8
Building
and maintaining indexes
Indexes
can be categorized into two primary types: clustered and non-clustered. There
are several other types of indexes. The indexes are created on a column or
columns on tables and views. The purpose of clustered and non-clustered indexes
is to improve how the Microsoft SQL Server Database Engine accesses the data.
While both index types may improve read operations, there is also a possibility
that they could negatively affect the performance of some operations like
inserts and updates.Therefore, you need to be selective when creating
indexes.In most cases, highly transactional databases are indexed differently
than those that support mostly read operations.In the next set of exercises,
you will create a clustered index.
Create
your first clustered index using SSMS
1.
Open SQL Server
Management Studio (SSMS) and connect to a server.
2.
Expand the
Databases folder.
3.
Expand the
AdventureWorks2012 database.
4.
Expand the Tables
folder.
5.
Expand the dbo.DatabaseLog table.
6.
Right-click the
Indexes folder.
7.
Select New Index |
Clustered Index.
8. In the New Index dialog box, click the Add button.
9. In the Select Columns dialog box, check the box next to
the PostTime column.
10. Click OK.
11. Click Ascending in the New Index dialog box.
12. In the newly available drop-down list, select
Descending.
13. In the Name text box, type CIX_DatabaseLog_PostTime.
Click OK.
Clustered
index structure
Each table
or view can have only one clustered index.This is because a clustered index
changes the way the data is stored and sorted.Both clustered and nonclustered
indexes store information in a balanced tree, or B-tree. A B-tree identifies
similar data and groups it together. The fast access of the data that an index
provides can be attributed to the fact that searches on a B-tree are based on
key values.Since a B-tree groups records with similar keys together, the
Database Engine will need to navigate only a few pages to find the records.
Nonclustered
index structure
The B-tree
structure of the nonclustered index is similar to that of a clustered index.
The leaf level contains bookmarks that direct it to data rows (leaf level) in
the clustered index that contains the data. Since the nonclustered index does
not contain any data, it does not affect the way data is stored or sorted. As a
result, you can have multiple nonclustered indexes on a single table. In
Microsoft SQL Server, you can create an index that includes a column or columns
that are already part of an existing index. As a result, you should always
consider changing or replacing an existing index prior to adding new indexes.
Create
your first nonclustered index using SSMS
1.
Open SSMS and connect to a server.
2.
Expand the Databases folder.
3.
Expand the AdventureWorks2012 database.
4.
Expand the Tables folder.
5.
Expand the Sales.SalesOrderHeader table.
6.
Right-click the Indexes folder.
7.
Select New Index | Non-Clustered Index.
8.
In the Index Name text box, type IX_SalesOrderHeader_OrderDate.
9.
Click Add.
10. Check the box next to the
OrderDate column. Click Ok.
Adding included
columns
Recall from the discussion of nonclustered indexes that the
leaf level contains bookmark lookups to the leaf level of the clustered
index.This operation can sometimes slow down the processing of a query.To
circumvent this issue, SQL Server allows you to add additional information in
the leaf level of a nonclustered index.You do so by adding the INCLUDED
argument to your index creation script. In short, the included column improves
the performance of a query by eliminating the need for it to obtain data from
the clustered index.This technique is synonymous with a tuning strategy known
as covering indexes. A covering index is a nonclustered index that has all the
information at the leaf level to satisfy a query.
Add
included columns to an index using SSMS
1.
Open SSMS and connect to a server.
2.
Expand the Databases folder.
3.
Expand the AdventureWorks2012 database.
4.
Expand the Tables folder.
5.
Expand the Sales.SalesOrderHeader table.
6.
Expand the Indexes folder.
7.
Right-click the IX_SalesOrderHeader_OrderDate index and select
Properties.
8.
In the Index Properties dialog box, select the Included Columns
tab.
9.
Click the Add button.
10.
Check the boxes next to the Status and AccountNumber columns. Click OK.
Adding filters to
indexes
Just
as included columns enhance the capabilities of a nonclustered index, so do
filters. Filtered indexes optimize nonclustered index performance by using a
filtered predicate to refine data down to a small subset. As a result, you have
a smaller index that requires less storage and maintenance, and offers improved
performance. Filtered indexes are ideal for columns that contain a smaller set
of pertinent values for queries.
Add a
filter to an index using SSMS
1.
Open SSMS and connect to a server.
2.
Expand the Databases folder.
3.
Expand the AdventureWorks2012 database.
4.
Expand the Tables folder.
5.
Expand the Sales.SalesOrderHeader table.
6.
Expand the Indexes folder.
7.
Right-click the IX_SalesOrderHeader_OrderDate index and select
Properties.
8.
Click Filter in the Select a Page section of the Index Properties
dialog box.
9.
Type OnlineOrderFlag = 0 in the Filter Expression text box. Click
Ok.
Thanks
& Regards
Srikanth Goud- Virtualization Administrator
