Day 9

Quiz

1:Which is larger, a nonclustered or clustered index?
A1: A nonclustered index is usually larger than a clustered index on the same key. This is because the leaf level requires one row in the index for each row in the table.
2:How do you force a table scan in SQL Server?
A2: By specifying an index number of 0 in the optimizer hint.

Exercises

Q1:1. Write a query to output the contents of the Employees table in the Northwind database. Display the Execution Plan for this query.
A1: The following is the SQL statement you can use to display the Employees table. Remember to set the option in the Query Analyzer to display the Execution Plan.
Select * From Employees

Q2:Add a WHERE clause to the query based on the LastName column and see whether the execution plan has changed.
A2: The following is the SQL statement that adds the WHERE condition to the previous exercise answer. Remember to set the option in the Query Analyzer to display the Execution Plan.
Select *
From Employees
Where LastName Like 'M%'

Q3:Force the use of the nonclustered index on the PostalCode. Check the execution plan to see what the effect is.
A3: The following is the SQL statement that adds the hint to force the use of the PostalCode index. Remember to set the option in the Query Analyzer to display the Execution Plan.
Select *
From Employees (index = 'PostalCode')
Where LastName Like 'M%'

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset