SQL Server Index系列之一

Agenda

•Index Structures

•Primary Key and Clustered Index

•Constraints and Indexes

•Syntax for creating and managing indexes

•Special Indexes

•Index Access Methods

•DMVs for Index tuning

 

Index Structures

1. A heap is a table without a clustered index.

 

 

--Heap if object_id('IndexStructure', 'U') is not null begin drop table IndexStructure; end create table IndexStructure ( Col1 int, Col2 int ); select * from sys.indexes where object_id = object_id(N'IndexStructure'); select object_name(object_id) as name, p.*, a.type_desc, a.first_iam_page from sys.partitions p join sys.system_internals_allocation_units a on p.partition_id = a.container_id where object_id = object_id('IndexStructure');  

2. Relationship between sys.indexes, sys.partitions, and sys.allocation_units

 

3. Clustered Index

Clustered index physically stores the data in sorted order

 

--Clustered Index create unique clustered index IX_CL_UN_Col1 on IndexStructure(Col1); select * from sys.indexes where object_id = object_id(N'IndexStructure');

4. Nonclustered index

 --Nonclustered Index create nonclustered index IX_NCL_Col2 on IndexStructure(Col2); select * from sys.indexes where object_id = object_id(N'IndexStructure'); select object_name(object_id) as name, p.*, a.type_desc, a.first_iam_page from sys.partitions p join sys.system_internals_allocation_units a on p.partition_id = a.container_id where object_id = object_id('IndexStructure');

 

5. Structure of Index Pages

--Structure of Index Pages if not exists(select * from IndexStructure) begin insert into IndexStructure(Col1,Col2) values (1,2); end USE master; GO if object_id('sp_table_pages','U') is null begin CREATE TABLE sp_table_pages (PageFID tinyint, PagePID int, IAMFID tinyint, IAMPID int, ObjectID int, IndexID tinyint, PartitionNumber tinyint, PartitionID bigint, iam_chain_type varchar(30), PageType tinyint, IndexLevel tinyint, NextPageFID tinyint, NextPagePID int, PrevPageFID tinyint, PrevPagePID int, Primary Key (PageFID, PagePID)); end TRUNCATE TABLE sp_table_pages; INSERT INTO sp_table_pages exec('dbcc ind ( Test, [dbo.IndexStructure], -1)'); select * from sp_table_pages