復(fù)合索引(索引有兩個以上的列)
索引在數(shù)據(jù)庫中是以B樹的形式存儲的。包含A,B兩個列的索引會首先根據(jù)A列建B樹，A列的葉節(jié)點上才會開始根據(jù)B列建B樹。所以包含兩個列的索引就需要根據(jù)查詢條件所在列來決定兩個列在索引中的順序。舉例：
show sourceview sourceprint?01 USE [Test]
02 GO
03 /****** 對象: Table [dbo].[testIndexOrder]
腳本日期: 05/27/2010 09:11:26 ******/
04 SET ANSI_NULLS ON
05 GO
06 SET QUOTED_IDENTIFIER ON
07 GO
08 CREATE TABLE [dbo].[testIndexOrder](
09 [ID] [int] IDENTITY(1,1) NOT NULL,
10 [FirstName] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
11 [LastName] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
12 [Desc] [nvarchar](400) COLLATE Chinese_PRC_CI_AS NULL,
13 CONSTRAINT [PK_testIndexOrder] PRIMARY KEY CLUSTERED
14 (
15 [ID] ASC
16 )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
17 ) ON [PRIMARY]
18 GO
19 /****** 對象: Index [IX_testIndexOrder] 腳本日期: 05/27/2010 09:11:51 ******/
20 CREATE NONCLUSTERED INDEX [IX_testIndexOrder] ON [dbo].[testIndexOrder]
21 (
22 [FirstName] ASC,
23 [LastName] ASC
24 )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
25 GO