Solutions to Practice Questions - Chapter 6 docx

¤NIIT S.1

1. c. Primary XML index

2. In a clustered index, data is stored at the leaf level of the B-Tree. The SQL Server

performs the following steps when it uses a clustered index to search for a value:

a. The SQL Server obtains the address of the root page from the sysindexes table,

which is a system table containing the details of all the indexes in the database.

b. The search value is compared with the key values on the root page.

c. The page with the highest key value less than or equal to the search value is

found.

d. The page pointer is followed to the next lower level in the index.

e. Steps 3 and 4 are repeated until the data page is reached.

f. The rows of data are searched on the data page until the search value is found. If

the search value is not found on the data page, no rows are returned by the

query.

3. d. You need not drop XML indexes in order to modify the primary key on a table.

4. Using the sys.dm_db_index_physical_stats system function

5. WITH ENCRYPTION option

6. A standard view does not contain any data, but derives its data from the underlying

tables. However, in the indexed view, the rows of the view are stored in the database

in the same format as a table.

7. Indexing a view helps in improving the query performance. Another benefit of

creating an indexed view is that the optimizer starts using the view index in queries

that do not directly name the view in the FROM clause. If the query contains

references to columns that are also present in the indexed view, and the query

optimizer estimates that using the indexed view offers the lowest cost access

mechanism, the query optimizer selects the indexed view.

8. The full-text search feature helps you to search for complex strings in the database. It

also helps in improving the query performance when the data in a table is large.

9. The full-text index population methods are:

z Full population: Use this method to populate the full-text catalog or the full￾text index for the first time

z Change tracking-based population: Use this method to propagate the changes

made in the table to the full-text index.

Solutions to Practice Questions

Chapter 6