Microsoft Press microsoft sql server 2005 PHẦN 3 pptx

144 Chapter 3 Review

All pieces of data need to be uniquely identified within the tables. Referential integrity

is crucial to the successful operation of the database application.

How would you define the table structures to meet the needs of the patient claims

database?

Suggested Practices

Before doing the following suggested practices, skip forward in this book to read

Chapter 5, “Working with Transact-SQL.” This chapter familiarizes you with the

basics of adding data to a table as well as retrieving it. Understanding these functions

is important for performing the practice tasks, which will help you see how the vari￾ous table structures interact with data.

Creating Tables

■ Practice 1 Insert some data into the StateProvince, Country, and AddressType

tables. Retrieve the data from the table and inspect the identity column. Change

the seed, increment, or both for the identity column and insert more rows.

Retrieve the data from the table. Are the values in the identity column what you

expected?

■ Practice 2 Concatenate the City, StateProvince, and PostalCode columns

together. Change the data type of the resulting new column from a varchar to a

char. Execute the same query you used in Practice 1. Why do the results differ?

Creating Constraints

■ Practice 1 Insert some data into the CustomerAddress table. What happens when

you do not specify an AddressType? What happens when you do not specify

either a Country or StateProvince?

■ Practice 2 Change the value in one of the foreign key columns to another value

that exists in the referenced table. What happens? Change the value to some￾thing that does not exist in the referenced table. What happens? Is this what you

expected?

■ Practice 3 Try to insert a row into the Customer table that has a negative value for

the credit line. Are the results what you expected?

■ Practice 4 Insert a row into the Customer table without specifying a value for the

outstanding balance. Retrieve the row. What are the values for the outstanding

balance and available credit? Are they what you expected?

C0362271X.fm Page 144 Friday, April 29, 2005 7:30 PM

Chapter 3 Review 145

Take a Practice Test

The practice tests on this book’s companion CD offer many options. For example, you

can test yourself on just the content covered in this chapter, or you can test yourself on

all the 70-431 certification exam content. You can set up the test so that it closely sim￾ulates the experience of taking a certification exam, or you can set it up in study mode

so that you can look at the correct answers and explanations after you answer each

question.

MORE INFO Practice tests

For details about all the practice test options available, see the “How to Use the Practice Tests” sec￾tion in this book’s Introduction.

C0362271X.fm Page 145 Friday, April 29, 2005 7:30 PM

C0362271X.fm Page 146 Friday, April 29, 2005 7:30 PM

147

Chapter 4

Creating Indexes

As you saw in Chapter 3, “Creating Tables, Constraints, and User-Defined Types,” cre￾ating tables is the first step of building a useful database. You then need to add data

to the tables. However, if you never retrieve the data in the table, you are simply wast￾ing storage space. SQL Server does not need to have indexes on a table to retrieve

data. It can simply scan a table to find the piece of data that is requested. However,

most organizations store massive amounts of data in a table and need to be able to

retrieve data instantly. To allow rapid data retrieval while ensuring that performance

does not decline as users add rows to a table, you need to add indexes to your tables.

Indexes are not a new concept or strictly a database concept. We use indexes every

day. At the back of this book, you will find an index in printed form. If you wanted to

read about full-text indexes to prepare for your exam, you could find the information

in two different ways. You could open this book, start at page 1, and scan each page

until you found the information you needed. Or you could turn to the index at the

back of the book, locate full-text indexing, and then go directly to the corresponding

page or pages that discuss this topic. You find the information either way, but using

the index is much more efficient. In this chapter, you will explore how SQL Server

builds and uses indexes to ensure fast data retrieval and performance stability. You

will then learn how to build clustered, nonclustered, and covering indexes on your

tables to achieve the optimal balance between speed and required index maintenance

overhead.

Exam objectives in this chapter:

■ Implement indexes.

❑ Specify the filegroup.

❑ Specify the index type.

❑ Specify relational index options.

❑ Specify columns.

❑ Disable an index.

❑ Create an online index by using an ONLINE argument.

C0462271X.fm Page 147 Friday, April 29, 2005 7:31 PM

148 Chapter 4 Creating Indexes

Lessons in this chapter:

■ Lesson 1: Understanding Index Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149

■ Lesson 2: Creating Clustered Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154

■ Lesson 3: Creating Nonclustered Indexes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161

Before You Begin

To complete the lessons in this chapter, you must have

■ SQL Server 2005 installed.

■ A copy of the AdventureWorks sample database installed in the instance.

Real World

Michael Hotek

Several years ago, after SQL Server 6.5 had been on the market for awhile, I started

a project with a new company in the Chicago area. This company had the great

idea to help people find apartments in the area that met the customers’ criteria.

One of the employees had read about a programming language called Visual Basic

that would enable them to create the type of application they needed to manage

the hundreds of apartment complexes in the area. The application was created,

tested, and put in production. Four months later, the business was growing rap￾idly, and the company opened offices in several dozen other cities.

This is when the company started having problems. Finding apartments by using

the SQL Server database application was taking longer and longer. Many associates

were getting so frustrated that they started keeping their own paper-based files. The

developer had reviewed all the code and couldn’t reproduce the problem. So

the company called me to take a look at the SQL Server side of the equation.

The first thing I did was ask the developer whether he had reviewed the indexes

on the tables in SQL Server. I had my answer to the performance problem when

the developer asked what an index was. It took me an hour to get to the cus￾tomer’s office downtown, and the performance problem was solved 15 minutes

later with the addition of some key indexes. I spent the rest of the day indexing

the other tables so they wouldn’t become problems in the future and explaining

to the developer what an index was, why it would help, and how to determine

what should be indexed.

C0462271X.fm Page 148 Friday, April 29, 2005 7:31 PM

Lesson 1: Understanding Index Structure 149

Lesson 1: Understanding Index Structure

An index is useful only if it can help find data quickly regardless of the volume of data

stored. Take a look at the index at the back of this book. The index contains only a

small sampling of the words in the book, so it provides a compact way to search for

information. If the index were organized based on the pages that a word appears on,

you would have to read many entries and pages to find your information. Instead, the

index is organized alphabetically, which means you can go to a specific place in the

index to find what you need. It also enables you to scan down to the word you are

looking for. After you find the word you are looking for, you know that you don’t have

to search any further. The way an index is organized in SQL Server is very similar. In

this lesson, you will see how SQL Server uses the B-tree structure to build indexes that

provide fast data retrieval even with extremely large tables.

After this lesson, you will be able to:

■ Explain SQL Server’s index structure.

Estimated lesson time: 20 minutes

Exploring B-Trees

The structure that SQL Server uses to build and maintain indexes is called a Balanced

tree, or B-tree. The illustration in Figure 4-1 shows an example of a B-tree.

Figure 4-1 General index architecture

A B-tree consists of a root node that contains a single page of data, zero or more inter￾mediate levels containing additional pages, and a leaf level.

Intermediate

Root

Leaf

C0462271X.fm Page 149 Friday, April 29, 2005 7:31 PM

150 Chapter 4 Creating Indexes

The leaf-level pages contain entries in sorted order that correspond to the data being

indexed. The number of index rows on a page is determined by the storage space

required by the columns defined in the index. For example, an index defined on a

4-byte integer column will have five times as many values per page as an index defined

on a char(60) column that requires 60 bytes of storage per page.

SQL Server creates the intermediate levels by taking the first entry on each leaf-level

page and storing the entries in a page with a pointer to the leaf-level page. The root

page is constructed in the same manner.

MORE INFO Index internals

For a detailed explanation of the entries on an index page as well as how an index is constructed,

see Inside Microsoft SQL Server 2005: The Storage Engine by Kalen Delaney (Microsoft Press, 2006)

and Inside Microsoft SQL Server 2005: T-SQL Querying by Itzik Ben-Gan (Microsoft Press, 2006).

By constructing an index in this manner, SQL Server can search tables that have bil￾lions of rows of data just as quickly it can tables that have a few hundred rows of data.

Let’s look at the B-tree in Figure 4-2 to see how a query uses an index to quickly find

data.

Figure 4-2 Building an index

If you were looking for the term “SQL Server,” the query would scan the root page. It

would find the value O as well as the value T. Because S comes before T, the query

knows that it needs to look on page O to find the data it needs. The query would then

move to the intermediate-level page that entry O points to. Note that this single oper￾ation has immediately eliminated three-fourths of the possible pages by scanning a

very small subset of values. The query would scan the intermediate-level page and

A, H

O, T

D, E,

F, G

U, V,

W, X,

Y, Z

A

D

H, I,

J, K

L, M,

N

H

L

O

S

T

U

O, P,

Q, R S T A, B,

C

Intermediate

Root

Leaf

C0462271X.fm Page 150 Friday, April 29, 2005 7:31 PM

Lesson 1: Understanding Index Structure 151

find the value S. It would then jump to the page that this entry points to. At this point,

the query has scanned exactly two pages in the index to find the data that was

requested. Notice that no matter which letter you choose, locating the page that con￾tains the words that start with that letter requires scanning exactly two pages.

This behavior is why the index structure is called a B-tree. Every search performed

always transits the same number of levels in the index—and the same number of pages

in the index—to locate the piece of data you are interested in.

Inside Index Levels

The number of levels in an index, as well as the number of pages within each level of

an index, is determined by simple mathematics. As previous chapters explained, a

data page in SQL Server is 8,192 bytes in size and can store up to 8,060 bytes of actual

user data.

If you built an index on a char(60) column, each row in the table would require

60 bytes of storage. That also means 60 bytes of storage for each row within the index.

If there are only 100 rows of data in the table, you would need 6,000 bytes of storage.

Because all the entries would fit on a single page of data, the index would have a single

page that would be the root page as well as the leaf page. In fact, you could store 134

rows in the table and still allocate only a single page to the index.

As soon as you add the 135th row, all the entries can no longer fit on a single page, so

SQL Server creates two additional pages. This operation creates an index with a root

page and two leaf-level pages. The first leaf-level page contains the first half of the

entries, the second leaf-level page contains the second half of the entries, and the root

page contains two rows of data. This index does not need an intermediate level

because the root page can contain all the values at the beginning of the leaf-level

pages. At this point, a query needs to scan exactly two pages in the index to locate any

row in the table.

You can continue to add rows to the table without affecting the number of levels in the

index until you reach 17,957 rows. At 17,956 rows, you have 134 leaf-level pages con￾taining 134 entries each. The root page has 134 entries corresponding to the first row

on each of the leaf-level pages. When you add the 17,957th row of data to the table,

SQL Server needs to allocate another page to the index at the leaf level, but the root

page cannot hold 135 entries because this would exceed the 8,060 bytes allowed per

page. So SQL Server adds an intermediate level that contains two pages. The first page

contains the initial entry for the first half of the leaf-level pages, and the second page

C0462271X.fm Page 151 Friday, April 29, 2005 7:31 PM

152 Chapter 4 Creating Indexes

contains the initial entry for the second half of the leaf pages. The root page now con￾tains two rows, corresponding to the initial value for each of the two intermediate￾level pages.

The next time SQL Server would have to introduce another intermediate level would

occur when the 2,406,105th row of data is added to the table.

As you can see, this type of structure allows SQL Server to very quickly locate the rows

that satisfy queries, even in extremely large tables. In this example, finding a row in a

table that has nearly 2.5 million rows requires SQL Server to scan only three pages of

data. And the table could grow to more than 300 million rows before SQL Server

would have to read four pages to find any row.

Keep in mind that this example uses a char(60) column. If you created the index on

an int column requiring 4 bytes of storage, SQL Server would have to read just one

page to locate a row until the 2,016th row was entered. You could add a little more

than 4 million rows to the table and still need to read only two pages to find a row. It

would take more than 8 billion rows in the table before SQL Server would need to

read three pages to find the data you were looking for.

Quick Check

■ What structure guarantees that every search performed will always transit

the same number of levels in the index—and the same number of pages in

the index—to locate the piece of data you are interested in?

Quick Check Answer

■ The B-tree structure that SQL Server uses to build its indexes.

Lesson Summary

■ A SQL Server index is constructed as a B-tree, which enables SQL Server to

search very large volumes of data without affecting the performance from one

query to the next.

■ The B-tree structure delivers this performance stability by ensuring that each

search will have to transit exactly the same number of pages in the index, regard￾less of the value being searched on.

C0462271X.fm Page 152 Friday, April 29, 2005 7:31 PM