Database Reference
In-Depth Information
Figure 8.18
Verify that view is dropped.
8.3 Indexes
As more and more data are stored in a database, tables and views can grow very large. To search
for a piece of information in a large table or view can be time consuming. Indexes are created on
tables or views to speed up the search. Indexes are helpful for improving the query performance
in the following scenarios:
◾
he searching condition in a WHERE clause is used to match values in a column to a spe-
ciic value. In these cases, if an index is created on a column involved in a search condition,
it will signiicantly improve performance.
◾
When joining two tables with a foreign key column, a join condition is used to match a value
in the primary key column to values in the foreign key column. he use of an index in the
matching process will certainly improve performance.
◾
When sorting values in a column, an index created on the column will speed up the sorting
process.
On a table or a view, multiple indexes can be created. However, in some situations, too many
indexes may not improve query performance. Reindexing occurs whenever data in an indexed
column are modiied or the structure of the column is changed. Reindexing slows down perfor-
mance. herefore, it is not a good idea to create too many indexes on a frequently updated table.
Indexes are created on a balance tree or
B-tree
, which is a data structure covered by a data struc-
ture course in the computer science curriculum. Based on how an index is stored on the B-tree, there
are two types of indexes:
clustered index
and
nonclustered index
. A clustered index places each
row in a table directly on the leaf node of the B-tree. A nonclustered index places the reference to each
row in a table on the leaf node. he clustered index has better performance since data can be accessed
directly on a B-tree. When a table is created, Windows Azure SQL Database automatically creates a
clustered index on the primary key. Owing to the nature of the cloud, database iles are automatically
placed on multiple drives. herefore, there is no need to specify the ile groups and create partitions
while creating an index. hus, SQL statements that create indexes on Windows Azure SQL Database
will not support the options such as Fillfactor in a nonclustered index, ile group, or partition.
An index is called a
composite index
if it includes multiple columns, such as a clustered
index on a combination primary key. In general, a composite index requires that each row in the
