Database Reference
In-Depth Information
Figure 8.20
Remove index by DROP INDEX statement.
Figure 8.21
Indexes created for CLASS table.
ACTIVITY 8.2 CREATING AND REMOVING INDEXES
For a large table or a view, the use of an index can speed up searching and sorting. In this
activity, you will learn how to create an index and how to use the index to improve perfor-
mance. Suppose that we want to create an index with an SQL statement on Windows Azure
SQL Database. Follow the steps below to accomplish this task:
1. On your local computer, log on to Windows Azure SQL Database portal through the
URL
https://
yourserver
.database.windows.net
where yourserver is your SQL Database server name.
2. Enter the database name
Class_Registration
. hen, enter your user name and pass-
word to log on to Windows Azure SQL Database.
3. Once you have logged on, click the
New Query
icon at the upper-left corner of your
screen.
4. First, let us exam the performance of the following SQL statement before indexing:
SELECT ClassID, CourseName
FROM COURSE, CLASS
GROUP BY CourseName, ClassID
5. Highlight the code and click the
Actual Plan
tab. After the SQL statement is exe-
cuted, click the link
Query Plan
. As shown in Figure 8.22, the sorting takes a lot of
workload while executing the query.
6. To view the cost of the SELECT statement, double click the SELECT node. As shown
in Figure 8.23, the total cost for the SELECT statement is 0.0184056.
7. To improve the performance, enter the following SQL statement to create an index on the
table COURSE. he column CourseName will be used as the index to improve sorting.
CREATE INDEX Class_Index on COURSE(CourseName)
