SQL Server 索引和表体系结构（包含列索引）

----创建表
CREATE TABLE [dbo].[Customers](
    [custid] [int] IDENTITY(1,1) NOT NULL,
    [companyname] [nvarchar](40) NOT NULL,
    [contactname] [nvarchar](30) NOT NULL,
    [contacttitle] [nvarchar](400) NOT NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
    [custid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

----创建包含列索引
CREATE NONCLUSTERED INDEX [IX1_Customers] ON [dbo].[Customers] 
(
    [companyname] ASC
)
INCLUDE ( [contactname])

WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO



这里的键列就是：companyname

非键列就是：contactname

非键列具有下列优点：

• 它们可以是不允许作为索引键列的数据类型。
  
  
• 在计算索引键列数或索引键大小时，数据库引擎不考虑它们。
  
  

当我们的SELECT查询是这样的
SELECT 
       [companyname]
      ,[contactname]
      ,[contacttitle]
  FROM [chenmh].[dbo].[Customers]
  where companyname='好孩子'
  
  
---这时我们选择将索引列都包含在索引建列中  
CREATE NONCLUSTERED INDEX [IX2_Customers] ON [dbo].[Customers] 
(
      
       [companyname] ASC
      ,[contactname] ASC
      ,[contacttitle] ASC
)
WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO



将会弹出警告：警告! 最大键长度为 900 个字节。索引 'IX2_Customers' 的最大长度为 940 个字节。对于某些大值组合，插入/更新操作将失败。

由于三个字段都是NVARCHAR字段类型，每个字符需要 2 个字节，（40+30+400）*2=940个字节，大于900字节，这时我们可以将[contactname] ,[contacttitle]包含在非键列中

CREATE NONCLUSTERED INDEX [IX3_Customers] ON [dbo].[Customers] 
(
      
       [companyname] ASC
      
)
INCLUDE ( [contactname]
        ,[contacttitle])
WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO



这时索引键大小所占字符就只有40*2=80个字节，同时索引也是覆盖索引，索引的列包含查询用到的列，当我们查询数据时直接在索引页中查找数据就可以，不需要访问数据页，减少磁盘IO，提高性能