数据库索引实例
A. 使用简单索引
下面的示例为authors表的au_id列创建索引。
SET NOCOUNT OFF
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'au_id_ind')
DROP INDEX authors.au_id_ind
GO
USE pubs
CREATE INDEX au_id_ind
ON authors (au_id)
GO
B. 使用唯一聚集索引
下面的示例为emp_pay表的employeeID列创建索引，并且强制唯一
性。因为指定了 CLUSTERED子句，所以该索引将对磁盘上的数据
进行物理排序。
SET NOCOUNT ON
USE pubs
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'emp_pay')
DROP TABLE emp_pay
GO
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'employeeID_ind')
DROP INDEX emp_pay.employeeID_ind
GO
USE pubs
GO
CREATE TABLE emp_pay
(
employeeID int NOT NULL,
base_pay money NOT NULL,
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)
GO
SET NOCOUNT OFF
CREATE UNIQUE CLUSTERED INDEX employeeID_ind
ON emp_pay (employeeID)
GO
C. 使用简单组合索引
下面的示例为order_emp表的orderID列和employeeID列创建索
引。
Set Nocount On
USE pubs
If Exists(Select * From Information_Schema.Tables
Where Table_Name='order_emp')
Drop Table order_emp
Go
USE pubs
If Exists(Select name From sysindexes
Where name='emp_order_ind')
Drop Index
order_emp.emp_order_ind
Go
USE pubs
Go
Create Table order_emp
(orderID int IDentity(1000,1),
employeeID int NOt Null,
orderdate datetime Not Null Default Getdate(),
orderamount money Not Null
)
Insert order_emp(employeeID,orderdate,orderamount)
values(1,1/03/98,2039.82)
Insert order_emp(employeeID,orderdate,orderamount)
values(1,1/22/98,445.29)
Insert order_emp(employeeID,orderdate,orderamount)
values(4,4/05/98,689.39)
Insert order_emp(employeeID,orderdate,orderamount)
values(7,3/21/98,1929.04)
Insert order_emp(employeeID,orderdate,orderamount)
values(7,3/21/98,445.77)
Insert order_emp(employeeID,orderdate,orderamount)
values(7,3/22/98,2178.98)
Go
set Nocount Off
create Index emp_order_ind
On order_emp(orderID,employeeID)
D. 使用 FILLFACTOR 选项
下面的示例使用 FILLFACTOR子句，将其设置为 100。FILLFACTOR
为 100将完全填满每一页，只有确定表中的索引值永远不会更改
时，该选项才有用。
SET NOCOUNT OFF
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'zip_ind')
DROP INDEX authors.zip_ind
GO
USE pubs
GO
CREATE NONCLUSTERED INDEX zip_ind
ON authors (zip)
WITH FILLFACTOR = 100
E. 使用 IGNORE_DUP_KEY
下面的示例为emp_pay表创建唯一聚集索引。如果输入了重复的
键，将忽略该 INSERT或 UPDATE语句。
SET NOCOUNT ON
USE pubs
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'emp_pay')
DROP TABLE emp_pay
GO
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'employeeID_ind')
DROP INDEX emp_pay.employeeID_ind
GO
USE pubs
GO
CREATE TABLE emp_pay
(
employeeID int NOT NULL,
base_pay money NOT NULL,
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)
GO
SET NOCOUNT OFF
GO
CREATE UNIQUE CLUSTERED INDEX employeeID_ind
ON emp_pay(employeeID)
WITH IGNORE_DUP_KEY
F. 使用 PAD_INDEX 创建索引
下面的示例为authors表中的作者标识号创建索引。没有
PAD_INDEX子句，SQL Server将创建填充 10%的叶级页，但是叶级
之上的页几乎被完全填满。使用 PAD_INDEX时，中间级页也填满
10%。
（说明 如果没有指定 PAD_INDEX，唯一聚集索引的索引页上至少）
会出现两项。
SET NOCOUNT OFF
USE pubs
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'au_id_ind')
DROP INDEX authors.au_id_ind
GO
USE pubs
CREATE INDEX au_id_ind
ON authors (au_id)
WITH PAD_INDEX, FILLFACTOR = 10
G. 为视图创建索引
下面的示例将创建一个视图，并为该视图创建索引。然后，引入
两个使用该索引视图的查询。
USE Northwind
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON
GO
--Create view.
CREATE VIEW V1
WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Revenue, OrderDate, ProductID, COUNT_BIG(*) AS COUNT
FROM dbo.[Order Details] od, dbo.Orders o
WHERE od.OrderID=o.OrderID
GROUP BY OrderDate, ProductID
GO
--Create index on the view.
CREATE UNIQUE CLUSTERED INDEX IV1 ON V1 (OrderDate, ProductID)
GO
--This query will use the above indexed view.
SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Rev, OrderDate, ProductID
FROM dbo.[Order Details] od, dbo.Orders o
WHERE od.OrderID=o.OrderID AND ProductID in (2, 4, 25, 13, 7, 89, 22, 34)
AND OrderDate >= '05/01/1998'
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC
--This query will use the above indexed view.
SELECT OrderDate, SUM(UnitPrice*Quantity*(1.00-Discount)) AS Rev
FROM dbo.[Order Details] od, dbo.Orders o
WHERE od.OrderID=o.OrderID AND DATEPART(mm,OrderDate)= 3
AND DATEPART(yy,OrderDate) = 1998
GROUP BY OrderDate
ORDER BY OrderDate ASC
- 1楼lishehe昨天 20:41
- csdn可以插入代码的，这样不美观啊
- Re: hanxuemin123451小时前
- 回复lishehen恩 呵呵 直接贴过来的