大型数据库第5章架构索引与视图.ppt

2.使用系统存储过程查看索引信息 语法格式： [EXEC] sp_helpindex table_name 3.使用系统视图查看索引信息 （1）系统视图sys.indexes sys.indexs用于显示数据库元数据中存储的索引类型/文件组或分区方案ID和索引选项的当前设置。 Sys.indexs中type字段表示索引类型（0为堆，1为聚集索引，2为非聚集索引，3为XML索引） （2）系统视图sys.index_columns sys.index_columns可以查看索引的列ID、索引内的位置、索引列的排序顺序等。 查看索引属性 SQL Server提供的Transact-SQL函数INDEXPROPERTY能够在给定表标识号/索引名称及属性名称的前提下，返回指定查看的索引属性值。其语法如下： INDEXPROPERTY (table_id, index, property) 其中：table_id为为其提供索引属性信息的表的标识号。index是索引的名称，将为该索引返回属性信息。property包含将要返回的索引属性的名称。 例：查看student表的索引ix_include的索引属性IndexDepth（索引深度）。 select Indexproperty(OBJECT_ID(student),ix_include,IndexDepth) 更名与删除索引 使用系统存储过程给索引更名 使用系统存储过程给索引更名的语法格式： sp_rename [ @objname = ] object_name , [ @newname = ] new_name [ , [ @objtype = ] object_type ] 优化索引 在SQL Server2008中，索引的数据是由系统自动维护的。在增加、删除修改数据后，索引数据都有可能会更新。久而久之，这些索引数据可能会分散在硬盘的各个位置，也有可能将本应该存在同一个页上的索引分散到多个页上，由此产生文件碎片。当碎片增多时，会影响SQL Server的查询性能。可以重新生成索引和重新组织索引两种方法来整理索引碎片。 重新生成索引会将原有索引删除并重建一个相同索引。通过重新生成索引，可以利用原来指定的填充因子设置，来删除碎片、回收磁盘空间，然后对连续页中的索引重新排序。 重新组织索引功能会使用最少的系统资源来重新组织索引，通过对叶子节点页的物理方式重新排序，使叶节点从左到右的逻辑顺序相匹配，从而达到整理表或视图中的索引碎片的目的。重新组织索引功能还会压缩索引页，压缩基于现有的填充因子值。重新组织索引不删除原有索引。 重新生成或重新组织索引的方法 在SSMS中可以重新生成或重新组织索引，索引逻辑碎片在索引页中所占比例小时无需重新生成。 使用alter index语句的rebuild参数可以重新生成索引。 使用alter index语句的REORGANIZE参数可以重新组织索引。 查看索引碎片的方法 使用SSMS查看索引碎片。 使用函数sys.dm_db_index_physical_stats查看索引碎片。该函数返回一个表，要查看表里的内容，可以使用select语句来查询。 例：返回database_demo数据库中所有表的所有索引和分区的大小和碎片统计信息。为了获得最佳性能并限制返回的统计信息，扫描模式设置为 LIMITED。 SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(databse_demo),NULL, NULL,NULL,LIMITED); 例：假设已将student表划分为4个成员表，每个年级一张表。每个成员表都在sgrade列上定义了约束。例如 ：2011成员表定义为： CREATE TABLE [dbo].[student2011]( [sno] [char](10) primary key, [Sname] [char](20) NULL, [Ssex] [char](2) NULL, [Sage] [int] NULL, [Sdept] [char](2) NULL, [Sgrade] [int] check(sgrade=2011) ) 然后，定义一个视图，该视图通过UNION ALL将所有的4个成员表中的数据合并为一个结果集。 CREATE VIEW studentall AS SELECT * FROM student2011 UNION ALL SELECT * FROM student2012 Union all SELECT * FROM student2013 Union all SELECT * FROM student2014 查看视图 创建了一个视图后，如果视