Module 6 Implementing Table Structures in SQL Server ® 2008 R2

1. Module 6 Implementing Table Structures in SQL Server ®2008 R2

2. Module Overview • SQL Server Table Structures • Working with Clustered Indexes • Designing Effective Clustered Indexes

3. Lesson 1: SQL Server Table Structures • What is a Heap? • Operations on Heaps • Forwarding Pointers • What is a Clustered Index? • Operations on Clustered Indexes • Unique vs. Non-Unique Clustered Indexes • Demonstration 1A: Rebuilding Heaps

4. What is a Heap? • A table with: • No specified order for pages within the table • No specified order for data within each page • Data that is inserted or modified can be placed anywhere within the table IAM Page Heap Data Pages

5. Operations on Heaps • INSERT • Each new row can be placed in the first available page with sufficient space • UPDATE • The row can either remain on the same page if it still fits, otherwise, it can be removed from the current page and placed on the first available page with sufficient space • DELETE • Frees up space on the current page • Data is not overwritten, space is just flagged as available for reuse • SELECT • Entire table needs to be read for most queries, if no indexes are available

6. Forwarding Pointers • Data modifications in heaps can leave forwarding pointers • Row IDs in other indexes do not need to be updated • Can lead to performance issues over time • Only a single forwarding pointer is used • Performance would be improved by removing forwarding pointers and updating other indexes • No easy option for this prior to SQL Server 2008 • SQL Server 2008 and later introduced ability to rebuild a table (including a heap) • ALTER TABLE WITH REBUILD Forwarding pointers are references left at the original location of a row, when the row has been moved.

7. What is a Clustered Index? • Table pages stored in logical order • Rows stored in logical order within table pages • Single clustered index per table Root Index Page Intermediate Level Index Pages Leaf Nodes Data Pages

8. Operations on Clustered Indexes • INSERT • Each new row must be placed into the correct logical position • May involve splitting pages of the table • UPDATE • The row can either remain in the same place if it still fits and if the clustering key value is still the same • If the row no longer fits on the page, the page needs to be split • If the clustering key has changed, the row needs to be removed and placed in the correct logical position within the table • DELETE • Frees up space by flagging the data as unused • SELECT • Queries related to the clustering key can seek • Queries related to the clustering key can scan and avoid sorts

9. Unique vs. Non-Unique Clustered Indexes • Clustered indexes can be • Unique • Non-Unique • SQL Server must be able to identify individual rows • Adds a uniqueifier (4 bytes long) when needed for non-unique indexes • Always specify indexes as unique if they are

10. Demonstration 1A: Rebuilding Heaps • In this demonstration you will see how to: • Create a table as a heap • Check the fragmentation and forwarding pointers for a heap • Rebuild a heap

11. Lesson 2: Working with Clustered Indexes • Creating Clustered Indexes • Dropping a Clustered Index • Altering a Clustered Index • Incorporating Free Space in Indexes • Demonstration 2A: Clustered Indexes

12. Creating Clustered Indexes • Can be created by specifying PRIMARY KEY on table • Can be created directly CREATETABLEdbo.Article (ArticleIDintIDENTITY(1,1)PRIMARYKEY, ArticleNamenvarchar(50)NOTNULL, PublicationDatedateNOTNULL ); CREATETABLEdbo.LogData (LogIDintIDENTITY(1,1), LogDataxmlNOTNULL ); ALTERTABLEdbo.LogData ADDCONSTRAINTPK_LogData PRIMARYKEY (LogId); CREATECLUSTEREDINDEXCL_LogTime ONdbo.LogTime(LogTimeID);

13. Dropping a Clustered Index • Drop an external index via DROP INDEX • Drop a PRIMARY KEY constraint via ALTER TABLE • May not be possible where foreign key references exist DROPINDEXCL_LogTimeONdbo.LogTime; ALTERTABLEdbo.LogData DROPCONSTRAINTPK_LogData;

14. Altering a Clustered Index • Some modifications permitted via ALTER INDEX • Can REBUID or REORGANIZE • Can DISABLE • Cannot modify the key columns of the index • CREATE INDEX WITH DROP EXISTING can do this ALTERINDEXCL_LogTimeONdbo.LogTime REBUILD; ALTERINDEXALLONdbo.LogTime REBUILD; ALTERINDEXALLONdbo.LogTime REORGANIZE;

15. Incorporating Free Space in Indexes • Free space can be left in indexes, including clustered indexes • FILLFACTOR • PADINDEX • Free space can improve performance of certain operations • Default value can be changed using sp_configure ALTERTABLEPerson.Person ADDCONSTRAINTPK_Person_BusinessEntityID PRIMARYKEYCLUSTERED ( BusinessEntityIDASC ) WITH (PAD_INDEX=OFF,FILLFACTOR= 70); GO

16. Demonstration 2A: Clustered Indexes • In this demonstration you will see how to: • Create a table with a clustered index • Detect fragmentation in a clustered index • Correct fragmentation in a clustered index

17. Lesson 3: Designing Effective Clustered Indexes • Characteristics of Good Clustering Keys • Appropriate Data Types for Clustering Keys • Creating Indexed Views • Indexed View Considerations • Demonstration 3A: Indexed Views

18. Characteristics of Good Clustering Keys • Good clustering keys have specific properties • Short • Static • Increasing (not necessarily monotonically) • Unique • Limits on clustering keys • 16 columns • 900 bytes

19. Appropriate Data Types for Clustering Keys

20. Creating Indexed Views • Clustered indexes can be based on views as well as on tables • Other database engines call these "materialized views" • Query optimizer may use indexed views to speed up query operations • Even if the view is not mentioned in the query • Indexed View Benefit • Can greatly speed up queries • Indexed View Costs • Can significantly impact performance of data modification operations • Need to consider the balance of SELECT vs. data modification operations

21. Indexed View Considerations • For an indexed view to be used in a query, a series of session SET options needs to match those when the indexed view was created • Enterprise Edition and Datacenter Edition of SQL Server will automatically utilize indexed views where it makes sense to do so • Other editions need to use the NOEXPAND query hint • View definition must be deterministic • View must be created WITH SCHEMABINDING • Full list of considerations is contained in Books Online

22. Demonstration 3A: Indexed Views • In this demonstration you will see how to: • Obtain details of indexes created on views • See if an indexed view has been used in an estimated execution plan

23. Lab 6: Implementing Table Structures in SQL Server • Exercise 1: Creating Tables as Heaps • Exercise 2: Creating Tables with Clustered Indexes • Challenge Exercise 3: Comparing the Performance of Clustered Indexes vs. Heaps (Only if time permits) Logon information Estimated time: 45minutes

24. Lab Scenario One of the most important decisions when designing a table is to choose an appropriate table structure. In this lab, you will choose an appropriate structure for some new tables required for the relationship management system.

25. Lab Review • When is it important that a clustered index has an increasing key? • Which table structure is automatically assigned when a table is assigned a primary key during the table creation, without specifying a structure?

26. Module Review and Takeaways • Review Questions • Best Practices