SQL Server Index系列之一

最新推荐文章于 2025-04-22 16:12:29 发布

DBFocus

最新推荐文章于 2025-04-22 16:12:29 发布

阅读量522

点赞数

CC 4.0 BY-SA版权

文章标签： sql server object table allocation constraints structure

本文链接：https://blog.youkuaiyun.com/DBFocus/article/details/5406827

本文详细介绍了SQL中索引的基本结构及其工作原理，包括堆结构、聚集索引、非聚集索引等，并通过实例展示了不同索引类型的创建及查看方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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