Module 6 implementing table structures in sql server 2008 r2
This presentation is the property of its rightful owner.
Sponsored Links
1 / 26

Module 6 Implementing Table Structures in SQL Server ® 2008 R2 PowerPoint PPT Presentation


  • 84 Views
  • Uploaded on
  • Presentation posted in: General

Module 6 Implementing Table Structures in SQL Server ® 2008 R2 . Module Overview. SQL Server Table Structures Working with Clustered Indexes Designing Effective Clustered Indexes. Lesson 1: SQL Server Table Structures. What is a H eap? Operations on Heaps Forwarding Pointers

Download Presentation

Module 6 Implementing Table Structures in SQL Server ® 2008 R2

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Module 6 implementing table structures in sql server 2008 r2

Module 6

Implementing Table Structures in SQL Server ®2008 R2


Module overview

Module Overview

  • SQL Server Table Structures

  • Working with Clustered Indexes

  • Designing Effective Clustered Indexes


Lesson 1 sql server table structures

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


What is a heap

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


Operations on heaps

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


Forwarding pointers

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.


What is a clustered index

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


Operations on clustered indexes

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


Unique vs non unique clustered indexes

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


Demonstration 1a rebuilding heaps

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


Lesson 2 working with clustered indexes

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


Creating clustered indexes

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);


Dropping a clustered index

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;


Altering a clustered index

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;


Incorporating free space in indexes

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


Demonstration 2a clustered indexes

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


Lesson 3 designing effective clustered indexes

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


Characteristics of good clustering keys

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


Appropriate data types for clustering keys

Appropriate Data Types for Clustering Keys


Creating indexed views

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


Indexed view considerations

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


Demonstration 3a indexed views

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


Lab 6 implementing table structures in sql server

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


Lab scenario

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.


Lab review

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?


Module review and takeaways

Module Review and Takeaways

  • Review Questions

  • Best Practices


  • Login