Module 6 implementing table structures in sql server 2008 r2
Download
1 / 26

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


  • 126 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Module 6 Implementing Table Structures in SQL Server ® 2008 R2 ' - olympe


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



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


ad