如何查询 SQL Server 2008 中的索引信息？

SQL Server 2008 查询索引

一、索引

索引是一种用于加速数据库查询的数据库对象，在SQL Server 2008中，索引可以显著提高数据检索速度，特别是在处理大量数据时，索引通过创建一个有序的数据结构来优化搜索操作，类似于书籍的目录。

二、索引的类型

1. 聚集索引（Clustered Index）

定义：聚集索引会对表中的数据进行物理排序，即表的数据按照索引的顺序存储在磁盘上。

特点：每个表只能有一个聚集索引，因为数据只能以一种方式排序，通常在主键列上创建聚集索引。

示例：

  CREATE CLUSTERED INDEX idx_employee_id ON Employees(EmployeeID);

2. 非聚集索引（Nonclustered Index）

定义：非聚集索引不改变表数据的物理存储顺序，而是创建一个独立的索引结构，包含指向实际数据行的指针。

特点：一个表可以有多个非聚集索引，适用于频繁查询但不需要排序的场景。

示例：

  CREATE NONCLUSTERED INDEX idx_lastname ON Employees(LastName);

三、如何创建索引

1. 使用TSQL语句创建索引

基本语法：

  CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
  ON table_name (column_name [ASC | DESC], ...)

示例：

聚集索引：

    CREATE CLUSTERED INDEX idx_employee_id ON Employees(EmployeeID);

非聚集索引：

    CREATE NONCLUSTERED INDEX idx_lastname ON Employees(LastName);

2. 使用SQL Server Management Studio (SSMS)创建索引

步骤：

1. 打开SQL Server Management Studio并连接到数据库实例。

2. 展开目标数据库，找到需要创建索引的表。

3. 右键点击表名，选择“设计”。

4. 在表设计窗口中，右键点击空白处，选择“索引/键”。

5. 在弹出的对话框中点击“添加”按钮，配置新索引的属性。

6. 输入索引名称，选择索引类型（聚集或非聚集），并选择要索引的列。

7. 保存更改。

四、如何查看现有索引

1. 使用系统存储过程sp_helpindex

语法：

  EXEC sp_helpindex table_name;

示例：

  EXEC sp_helpindex 'Employees';

输出结果：返回指定表的所有索引信息，包括索引名称、类型和列等。

2. 查询系统视图sys.indexes和相关视图

示例：

  SELECT 
      ind.name AS IndexName,
      OBJECT_NAME(ind.object_id) AS TableName,
      ind.type_desc AS IndexType,
      col.name AS ColumnName,
      keycol.key_ordinal AS KeyOrdinal
  FROM sys.indexes ind
      INNER JOIN sys.index_columns idxcol ON ind.object_id = idxcol.object_id AND ind.index_id = idxcol.index_id
      INNER JOIN sys.columns col ON idxcol.object_id = col.object_id AND idxcol.column_id = col.column_id
      INNER JOIN sys.key_constraints keycol ON idxcol.object_id = keycol.parent_object_id AND idxcol.index_id = keycol.unique_index_id
  WHERE ind.is_primary_key = 0 AND ind.is_unique_constraint = 0;

解释：该查询将返回当前数据库中所有用户定义的索引，包括索引名称、表名称、索引类型、列名称和列的键序。

五、删除索引

语法：

  DROP INDEX index_name ON table_name;

示例：

  DROP INDEX IX_Employees_LastName ON Employees;

注意事项：删除索引可能会影响数据库性能，特别是在频繁查询的情况下，在删除索引之前应仔细评估其影响。

六、常见问题与解答

Q1: 何时使用聚集索引和非聚集索引？

A1: 如果数据经常需要按照某一列进行范围查询或排序，可以考虑在该列上创建聚集索引，对于频繁查询但不需要排序的场景，可以使用非聚集索引，需要注意的是，一张表只能有一个聚集索引，但可以有多个非聚集索引。

Q2: 如何判断某个索引是否正在被使用？

A2: 可以通过以下方法来判断：

1、查询执行计划：在SQL Server Management Studio中查看查询的执行计划，看是否使用了指定的索引。

2、系统视图：查询系统视图sys.dm_db_index_usage_stats，查看特定索引的使用情况。

   SELECT object_id, index_id, user_seeks, user_scans, user_lookups, user_updates FROM sys.dm_db_index_usage_stats;

这个动态管理视图提供了有关索引使用情况的信息，可以帮助判断哪些索引是活跃的。