sql index
只有表或视图的所有者才能为表创建索引。表或视图的所有者可以随时创建索引,无论表中是否有数据。可以通过指定限定的数据库名称,为另一个数据库中的表或视图创建索引。
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ WITH < index_option > [ ,...n] ]
为表或视图创建唯一索引(不允许存在索引值相同的两行)。视图上的聚集索引必须是 UNIQUE 索引。
创建一个对象,其中行的物理排序与索引排序相同,并且聚集索引的最低一级(叶级)包含实际的数据行。一个表或视图只允许同时有一个聚集索引。
具有聚集索引的视图称为索引视图。必须先为视图创建唯一聚集索引,然后才能为该视图定义其它索引。
在创建任何非聚集索引之前创建聚集索引。创建聚集索引时重建表上现有的非聚集索引。
对于索引视图,只能为已经定义了聚集索引的视图创建非聚集索引。因此,索引视图中非聚集索引的行定位器一定是行的聚集键。
是索引名。索引名在表或视图中必须唯一,但在数据库中不必唯一。索引名必须遵循标识符规则。
若要在确定性视图中查找列,请使用 COLUMNPROPERTY 函数(IsDeterministic 属性)。该函数的 IsPrecise 属性可用来确定键列是否精确。
必须先为视图创建唯一的聚集索引,才能为该视图创建非聚集索引。
在 SQL Server 企业版或开发版中,查询优化器可使用索引视图加快查询的执行速度。要使优化程序考虑将该视图作为替换,并不需要在查询中引用该视图。
强烈建议在服务器的任一数据库中创建计算列上的第一个索引视图或索引后,尽早在服务器范围内将 ARITHABORT 用户选项设置为 ON。
应用索引的列。指定两个或多个列名,可为指定列的组合值创建组合索引。在 table 后的圆括号中列出组合索引中要包括的列(按排序优先级排列)。
确定具体某个索引列的升序或降序排序方向。默认设置为 ASC。
说明 中间级索引页上的行数永远都不会小于两行,无论 FILLFACTOR 的值有多小。
说明 显式的 FILLFACTOR 设置只是在索引首次创建时应用。SQL Server 并不会动态保持页上可用空间的指定百分比。
下表说明如何在已指定 FILLFACTOR 的情况下填充索引页。
1% -99 一个可用项 <= FILLFACTOR% 填满
重要 用某个 FILLFACTOR 值创建聚集索引会影响数据占用存储空间的数量,因为 SQL Server 在创建聚集索引时会重新分布数据。
如果没有为索引指定 IGNORE_DUP_KEY,SQL Server 会发出一条警告消息,并回滚整个 INSERT 语句。
如果键没有改变(提供的索引名和列与原索引相同),则 DROP_EXISTING 子句不会重新对数据进行排序。在必须压缩索引时,这样做会很有用。
无法使用 DROP_EXISTING 子句将聚集索引转换成非聚集索引;不过,可以将唯一聚集索引更改为非唯一索引,反之亦然。
指定过期的索引统计不会自动重新计算。若要恢复自动更新统计,可执行没有 NORECOMPUTE 子句的 UPDATE STATISTICS。
重要 如果禁用分布统计的自动重新计算,可能会妨碍 SQL Server 查询优化器为涉及该表的查询选取最佳执行计划。
指定用于生成索引的中间排序结果将存储在 tempdb 数据库中。如果 tempdb 与用户数据库不在同一磁盘集,则此选项可能减少创建索引所需的时间,但会增加创建索引时使用的磁盘空间。
在给定的 filegroup 上创建指定的索引。该文件组必须已经通过执行 CREATE DATABASE 或 ALTER DATABASE 创建。
CREATE INDEX 语句同其它查询一样优化。SQL Server 查询处理器可以选择扫描另一个索引,而不是执行表扫描,以节省I/O 操作。在某些情况下,可以不必排序。
自上一次文件组备份以来受 CREATE INDEX 语句影响的全部文件组必须作为一个单位备份。有关文件和文件组备份的更多信息,请参见 BACKUP。
备份和 CREATE INDEX 操作不相互防碍。如果正在进行备份,则在完整日志记录模式中创建索引,而这可能需要额外的日志空间。
若要显示有关对象索引的报表,请执行 sp_helpindex。
可以为临时表创建索引。在除去表或终止会话时,所有索引和触发器都将被除去。
索引键允许的最大大小为 900 字节,不过 SQL Server 2000 允许在可能包含大量可变类型列的列上创建索引,而这些列的最大大小超过 900 字节。
在 SQL Server 2000 中,还可以在计算列和视图上创建索引。在视图上创建唯一聚集索引可以提高查询性能,因为视图存储在数据库中的方式与具有聚集索引的表的存储方式相同。
CREATE TABLE t1 (a int, b int, c AS a/b)
INSERT INTO t1 VALUES ('1', '0')
相反,如果创建表之后在计算列 c 上创建索引,则上述 INSERT 语句将失败。
CREATE TABLE t1 (a int, b int, c AS a/b)
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1.c
INSERT INTO t1 VALUES ('1', '0')
定义索引视图的 SELECT 语句不得包含 TOP、DISTINCT、COMPUTE、HAVING 和 UNION 关键字。也不能包含子查询。
SELECT 列表中不得包含星号 (*)、'table.*' 通配符列表、DISTINCT、COUNT(*)、COUNT(<expression>)、基表中的计算列和标量聚合。
使用 AVG 的复杂聚合无法参与索引视图的 SELECT 列表。不过,如果查询使用这样的聚合,则优化程序将能使用该索引视图,用 SUM 和 COUNT_BIG 的简单聚合组合代替 AVG。
索引的创建者必须拥有表。所有表、视图和索引必须在同一数据库中创建。
定义索引视图的 SELECT 语句不得包含视图、行集函数、行内函数或派生表。同一物理表在该语句中只能出现一次。
搜索条件中不允许使用子查询或者 CONTAINS 或 FREETEXT 谓词。
可以创建索引的视图的定义主体必须具有确定性且必须精确,这类似于计算列上的索引要求。请参见在计算列上创建索引。
CREATE INDEX 的权限默认授予 sysadmin 固定服务器角色、db_ddladmin 和 db_owner 固定数据库角色和表所有者且不能转让。
下面的示例为 authors 表的 au_id 列创建索引。
IF EXISTS (SELECT name FROM sysindexes
下面的示例为 emp_pay 表的 employeeID 列创建索引,并且强制唯一性。因为指定了 CLUSTERED 子句,所以该索引将对磁盘上的数据进行物理排序。
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'employeeID_ind')
DROP INDEX emp_pay.employeeID_ind
commission decimal(2, 2) NOT NULL
CREATE UNIQUE CLUSTERED INDEX employeeID_ind
下面的示例为 order_emp 表的 orderID 列和 employeeID 列创建索引。
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'order_emp')
IF EXISTS (SELECT name FROM sysindexes
DROP INDEX order_emp.emp_order_ind
orderID int IDENTITY(1000, 1),
orderdate datetime NOT NULL DEFAULT GETDATE(),
INSERT order_emp (employeeID, orderdate, orderamount)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (5, '5/30/98', 1929.04)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (1, '1/03/98', 2039.82)
INSERT order_emp (employeeID, orderdate, orderamount)
INSERT order_emp (employeeID, orderdate, orderamount)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (7, '3/21/98', 1598.23)
INSERT order_emp (employeeID, orderdate, orderamount)
INSERT order_emp (employeeID, orderdate, orderamount)
VALUES (7, '3/22/98', 2178.98)
ON order_emp (orderID, employeeID)
下面的示例使用 FILLFACTOR 子句,将其设置为 100。FILLFACTOR 为 100 将完全填满每一页,只有确定表中的索引值永远不会更改时,该选项才有用。
IF EXISTS (SELECT name FROM sysindexes
CREATE NONCLUSTERED INDEX zip_ind
下面的示例为 emp_pay 表创建唯一聚集索引。如果输入了重复的键,将忽略该 INSERT 或 UPDATE 语句。
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'employeeID_ind')
DROP INDEX emp_pay.employeeID_ind
commission decimal(2, 2) NOT NULL
INSERT emp_pay VALUES (1, 500, .10)
INSERT emp_pay VALUES (2, 1000, .05)
INSERT emp_pay VALUES (3, 800, .07)
INSERT emp_pay VALUES (5, 1500, .03)
INSERT emp_pay VALUES (9, 750, .06)
CREATE UNIQUE CLUSTERED INDEX employeeID_ind
ON emp_pay(employeeID) WITH IGNORE_DUP_KEY
说明 如果没有指定 PAD_INDEX,唯一聚集索引的索引页上至少会出现两项。
IF EXISTS (SELECT name FROM sysindexes
WITH PAD_INDEX, FILLFACTOR = 10
下面的示例将创建一个视图,并为该视图创建索引。然后,引入两个使用该索引视图的查询。
--Set the options to support indexed views.
SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,
ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON
SELECT SUM(UnitPrice*Quantity*(1.00-Discount))
AS Revenue, OrderDate, ProductID, COUNT_BIG(*) AS COUNT
FROM dbo.[Order Details] od, dbo.Orders o
CREATE UNIQUE CLUSTERED INDEX IV1 ON V1 (OrderDate, ProductID)
--This query will use the above indexed view.
SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Rev,
FROM dbo.[Order Details] od, dbo.Orders o
WHERE od.OrderID=o.OrderID AND ProductID
in (2, 4, 25, 13, 7, 89, 22, 34)
--This query will use the above indexed view.
SELECT OrderDate, SUM(UnitPrice*Quantity*(1.00-Discount))
FROM dbo.[Order Details] od, dbo.Orders o
WHERE od.OrderID=o.OrderID AND DATEPART(mm,OrderDate)= 3
AND DATEPART(yy,OrderDate) = 1998
http://www.cnblogs.com/jams742003/archive/2010/09/08/1821523.html //原文