首页 > 代码库 > 00.索引-综述

00.索引-综述

初始化
 
--1.创建数据库
IF DB_ID(‘IndexDB‘)IS NULL
BEGIN
       CREATE DATABASE [IndexDB]
END
ALTER DATABASE [IndexDB] SET RECOVERY SIMPLE;
GO
--2.创建表
----2.1 数据表
CREATE TABLE Employee001(
   Id NVARCHAR(32) NOT NULL,
   Name NVARCHAR(40) NOT NULL, 
   Department NVARCHAR(40) NULL,
   Organization NVARCHAR(40) NOT NULL,
   Company NVARCHAR(40) NOT NULL
)
----2.2 DBCC命令 DBCC IND(IndexDB,Employee001,-1) 执行结果存储,方便查询
CREATE TABLE DBCCIndResult(
  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 INT,
  PrevPagePID int,
  PRIMARY KEY (PageFID, PagePID)
)
----2.3 DBCC Page结果存储 存储 DBCC PAGE(PCT,1,807, 3) WITH TABLERESULTS 执行结果 方便查询
CREATE TABLE DBCCPageResult(
    ParentObject NVARCHAR(200),
    Object NVARCHAR(200),
    Field NVARCHAR(200),
    Value NVARCHAR(200)
)
--3.插入数据
DECLARE @i int;
SET @i=0;
WHILE @i<100000
BEGIN
INSERT INTO Employee001 VALUES(
REPLACE(NEWID(),‘-‘,‘‘),
‘Emp‘+CONVERT(nvarchar,@i),
‘Dep‘+CONVERT(nvarchar,@i%1000),
‘Org‘+CONVERT(nvarchar,@i%100),
‘Cmp‘+CONVERT(nvarchar,@i%10))
SET @i=@i+1;
END

00.索引-综述