湖南it网站建设mxtia,织梦网站如何做地区分站,公司官网制作多少钱,天津企业免费建站SQL Server 索引和表体系结构#xff08;三#xff09; 原文:SQL Server 索引和表体系结构#xff08;三#xff09;包含列索引 概述 包含列索引也是非聚集索引#xff0c;索引结构跟聚集索引结构是一样#xff0c;有一点不同的地方就是包含列索引的非键列只存储在叶子节… SQL Server 索引和表体系结构三 原文:SQL Server 索引和表体系结构三包含列索引 概述 包含列索引也是非聚集索引索引结构跟聚集索引结构是一样有一点不同的地方就是包含列索引的非键列只存储在叶子节点包含列索引的列分为键列和非键列所谓的非键列就是INCLUDE中包含的列至少需要有一个键列且键列和非键列不允许重复非键列最多允许1023列也就是表的最多列-1由于索引键列不包括非键必须遵守现有索引大小的限制最大键列数为 16总索引键大小为 900 字节的要求所以引进了包含列索引。 正文 创建包含列索引 ----创建表
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 非键列具有下列优点 它们可以是不允许作为索引键列的数据类型。 在计算索引键列数或索引键大小时数据库引擎不考虑它们。 当查询中的所有列都作为键列或非键列包含在索引中时带有包含性非键列的索引可以显著提高查询性能。这样可以实现性能提升因为查询优化器可以在索引中找到所有列值不访问表或聚集索引数据从而减少磁盘 I/O 操作。当索引包含查询引用的所有列时它通常称为“覆盖查询”。 创建覆盖查询 覆盖查询就是创建的索引列包含查询所引用的所有列时 查询列都设为键列 当我们的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 个字节4030400*2940个字节大于900字节这时我们可以将[contactname] ,[contacttitle]包含在非键列中 2.将大数据类型设为非键列 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*280个字节同时索引也是覆盖索引索引的列包含查询用到的列当我们查询数据时直接在索引页中查找数据就可以不需要访问数据页减少磁盘IO提高性能 带有包含列的索引准则 设计带有包含列的非聚集索引时请考虑下列准则 在 CREATE INDEX 语句的 INCLUDE 子句中定义非键列。只能对表或索引视图的非聚集索引定义非键列。除 text、ntext 和 image 之外允许所有数据类型。精确或不精确的确定性计算列都可以是包含列。有关详细信息请参阅为计算列创建索引。与键列一样只要允许将计算列数据类型作为非键索引列从 image、ntext 和 text 数据类型派生的计算列就可以作为非键包含性列。 不能同时在 INCLUDE 列表和键列列表中指定列名。INCLUDE 列表中的列名不能重复。 列大小准则 必须至少定义一个键列。最大非键列数为 1023 列。也就是最大的表列数减 1。索引键列不包括非键必须遵守现有索引大小的限制最大键列数为 16总索引键大小为 900 字节。所有非键列的总大小只受 INCLUDE 子句中所指定列的大小限制例如varchar(max) 列限制为 2 GB。 列修改准则 修改已定义为包含列的表列时要受下列限制 除非先删除索引否则无法从表中删除非键列。除进行下列更改外不能对非键列进行其他更改 将列的为空性从 NOT NULL 改为 NULL。增加 varchar、nvarchar 或 varbinary 列的长度。 注意事项 键列的大小尽量小有利用提高效率将用于搜索和查找的列为键列键列尽量不要包含没必要的列。例如上面建立的覆盖查询列虽然companynamecontactname加起来作为键列也不会超过900字节但是这样键大小就变大了降低了查询效率避免添加不必要的列。添加过多的索引列键列或非键列会对性能产生下列影响 一页上能容纳的索引行将更少。这样会使 I/O 增加并降低缓存效率。需要更多的磁盘空间来存储索引。特别是将 varchar(max)、nvarchar(max)、varbinary(max) 或 xml 数据类型添加为非键索引列会显著增加磁盘空间要求。这是因为列值被复制到了索引叶级别。因此它们既驻留在索引中也驻留在基表中。索引维护可能会增加对基础表或索引视图执行修改、插入、更新或删除操作所需的时间 总结 如果您觉得文章对你有帮助活动活动你的手指麻烦给个推荐这也是对我一种鼓励在此表示感谢。 备注 作者沉寂的石头 博客http://www.cnblogs.com/chenmh 欢迎大家转载但转载时必须注明文章来源且在文章开头明显处给明链接否则保留追究责任的权利。 欢迎大家拍砖 posted on 2014-06-13 16:20 NET未来之路 阅读(...) 评论(...) 编辑 收藏 转载于:https://www.cnblogs.com/lonelyxmas/p/3786083.html
