sql索引语句

创建索引

CREATE UNIQUE CLUSTERED INDEX idx_cno
ON course(courseno DESC)

CREATE INDEX IDX_student ON student(sname)
INCLUDE (phone) ON [PRIMARY]
delete from student  where sname='张晓晓'
CREATE UNIQUE INDEX  UN_EMAIL ON student(Email)
insert into student values('17126113345','长的的','男','2000-09-08','170601',780,NULL,NULL)
SELECT * FROM student

查看表中索引的平均碎片

SELECT AVG_FRAGMENTATION_IN_PERCENT
FROM SYS.dm_db_index_physical_stats(db_id(),object_id('course'),null,null,null)

重组索引

ALTER INDEX ALL ON student REORGANIZE
ALTER INDEX IDX_student ON student REORGANIZE

索引的重建

ALTER INDEX IDX_student ON student REBUILD
WITH(PAD_INDEX=ON,FILLFACTOR=70)

索引的维护

索引的重组

索引的重建

ALTER INDEX idx_cno ON course
--REORGANIZE
REBUILD
WITH(PAD_INDEX=ON,FILLFACTOR=70) 

重建索引

IF EXISTS(SELECT * FROM sysindexes WHERE name='IDX_student')
DROP INDEX student.IDX_student
CREATE INDEX IDX_student ON student(sname)

DROP_EXISTING 索引的重建

CREATE INDEX IDX_student ON student(sname,studentno)
WITH(PAD_INDEX=ON,FILLFACTOR=80,DROP_EXISTING=ON)

索引的禁用

ALTER INDEX un_email ON student DISABLE
INSERT INTO student VALUES('1726113387','张大大','男','2000-09-08','170501',NULL,null,null)
SELECT * FROM student 

索引的删除

DROP INDEX student.IDX_student