--
--19.2.5节示例
--
创建学生表和名字列上的聚集索引
CREATE TABLE Student
(
StuID INT IDENTITY PRIMARY KEY NONCLUSTERED, --指定该主键为非聚集索引
Name NVARCHAR(10) NOT NULL,
Birthday DATE NOT NULL,
Sex BIT NOT NULL DEFAULT(0)
)
GO
CREATE CLUSTERED INDEX CIX_Student_Name --单独创建聚集索引
ON Student(Name)
创建选课表和唯一的非聚集索引
CREATE TABLE ChooseCourse
(
ChooseID INT IDENTITY PRIMARY KEY, --这里是聚集索引
CourseID INT NOT NULL,
StuID INT NOT NULL,
Status TINYINT NOT NULL
)
CREATE UNIQUE NONCLUSTERED INDEX IX_ChooseCourse_CourseIDStuID
ON ChooseCourse(CourseID,StuID)
创建有包含列的索引
CREATE INDEX IX_Student_Birthday
ON Student(Birthday)
INCLUDE(Sex)
--
--19.2.6节示例
--
重建索引
ALTER INDEX IX_ChooseCourse_CourseIDStuID
ON dbo.ChooseCourse
REBUILD
重新组织索引
ALTER INDEX ALL
ON Student
REORGANIZE
禁用索引
ALTER INDEX ALL
ON Student
DISABLE
GO
SELECT * --聚集索引被禁用，查询将抛出异常
FROM Student
DROP INDEX CIX_StudentName
ON Student
