SQL Server索引、表压缩
索引是一种磁盘上的数据结构，建立在表或视图的基础上。使用索引可以使数据的获取更快更高校，也会影响其他的一些性能，如插入或更新等。
索引主要分为两种类型：聚集索引和非聚集索引。
字典的目录就是一个索引，按照拼音查询想要的字就是聚集索引（物理连续，页码与目录一一对应），偏旁部首就是一个非聚集索引（逻辑连续，页码与目录不连续）。
聚集索引存储记录是物理上连续存在的，而非聚集索引是逻辑上的连续，物理存储并不连续。
聚集索引一个表中只能有一个，而非聚集索引一个表中可以有多个。
索引的利弊
使用索引是为了避免全表扫描，因为全表扫描是从磁盘上读取表的每一个数据页，如果有索引指向数据值，则只需要读少次数的磁盘就可以。
带索引的表在数据库中占用更多的空间，同样增、删、改数据的命令所需时间会更长。
索引的存储机制
书中的目录是一个字词以及所在的页码列表，数据库中的索引是表中的值以及各值存储位置的列表。
聚集索引是在数据库中新开辟一个物理空间，用来存放他排列的值，当有新数据插入时，他会重新排列整个物理存储空间。
非聚集索引只包含原表中的非聚集索引的列和指向实际物理表的一个指针。
数据表的基本结构
当一个新的数据表创建时，系统将在磁盘中分配一段以8k为单位的连续空间。当一个8k用完的时候，数据库指针会自动分配一个8k的空间，每个8k的空间称为一个数据页，并分配从0-7的页号，每个文件的第0页记录引导信息叫页头，每8个数据页由64k组成形成扩展区。全部数据页的组合形成堆。
SQL Server规定行不能跨越数据页，所以每行记录的最大数量只能是8k，这就是为什么char和varchar这两种字符类型容量要限制在8k以内的原因，存储超过8k的数据应使用text类型，其实text类型的字段值不能直接录入和保存，它是存储一个指针，指向由若干个8k的数据页所组成的扩展区，真正的数据其实放在这些数据页中。
什么情况下设置索引
1、定义主键的数据列（sql server默认会给主键一个聚集索引）。
2、定义有外键的数据列
3、对于经常查询的数据列
4、对于需要在指定范围内频繁查询的数据列
5、经常在where子句中出现的数据列
6、经常出现在关键字 order by、group by、distinct后面的字段。
什么情况下不要设置索引
1、查询中很少涉及的列，重复值比较多的列。
2、text、image、bit数据类型的列
3、经常存取的列
4、经常更新操作的表，索引一般不要超过3个、最多不要5个。虽说提高了访问速度，但会影响更新操作。
聚集索引
1、使用SSMS创建聚集索引
展开要创建索引的表->右击索引->选择新建索引->聚集索引->新建索引点添加->选择列->选择升序或降序->输入名字->确定。
默认情况下，生成主键的同时将自动创建一个聚集索引。
2、使用T-SQL创建聚集索引
use webDBgocreate clustered index index_name /*聚集索引名*/on table_name( id desc)with(drop_existing=on); /*如果存在则删除*/
每张表或者视图只能包含一个聚集索引，因为聚集索引改变了数据存储与排列方式。无论是聚集还是非聚集索引，都将信息存储在平衡树或B-树中，B-树识别类似数据并将他们组合在一起，正是由于B-树中的检索基于键值，因此索引可以提升数据访问的速度。B-树将具有类似键的组合起来，所以数据库引擎只需搜索少量页面即可找到目标记录。
非聚集索引
每张表上可以有多个非聚集索引，可以在某个列上创建一个索引，也可以在已经是现有索引组成部分的多列上创建索引。
SSMS创建方法同上，T-SQL创建方法如下：
use webDBgocreate nonclustered index fei /*聚集索引名*/on defualt( hits desc)
添加索引选项
fillfactor：用于在创建索引时，每个索引页的数据占索引大小的百分比，默认100.当需要频繁修改表时，建议设置为70-80，不经常更新时建议90.
pad_index:用于索引中间级中每个页上保持开放的空间。不能设置值，他的值继承自fillfactor。
小例子
use webDBgocreate table ceshi --新建表( id int identity(1,1) primary key, name varchar(20), code varchar(20), [date] datetime)--插入10w条测试数据declare @n intset @n = 1while @n <100000beginInsert into ceshi (name,code,[date]) values (‘name‘+cast(@n as varchar(20)),‘code‘+cast(@n as varchar(20)),getutcdate())set @n=@n+1end--查看索引情况set statistics io on --查看磁盘ioset statistics time on --查看sql语句分析编译和执行时间select * from ceshi--查看索引情况exec sp_helpindex ceshiselect * from ceshi where name = ‘name1‘--ctrl+l 查看执行计划 聚集索引扫描开销100%，考虑优化为索引查找，在name上建立非聚集索引--建立非聚集索引create index name_index on ceshi( name)--再次查看索引情况 多出来新建的非聚集索引exec sp_helpindex ceshi--在运行上面的语句select * from ceshi where name = ‘name1‘--明显发现速度变快了 ， ctrl+l 发现聚集索引和非聚集索引各占50%
管理索引
exec sp_helpindex ceshi --查看该表中的索引exec sp_rename ‘ceshi.name_index‘,‘new_name‘ --改名drop index ceshi.new_name --删除索引dbcc showcontig(ceshi,new_name) --检查碎片dbcc indexdefrag(webDB,ceshi,new_name) --整理碎片update statistics ceshi --更新表中所有索引的统计
表压缩SQL Server的主要性能取决于磁盘I/O效率，SQL Server 2008提供了数据压缩功能来提高磁盘I/O效率。
表压缩意味着减小数据的磁盘占有量，所以压缩可以用在堆表、聚集索引的表、非聚集索引的表、索引视图、分区表上。
可压缩的数据类型
smallint、int、Bigint、decimal、numeric、real、float、money、smallmoeny、bit、datetime、datetime2、datetimeoffset、char、nchar、binary、rowversion。
SQLServer中有两种压缩类型：数据与备份
行压缩
压缩会改变数据的物理存储方式，但不需要对代码做任何修改。
行压缩流程：首先识别表中每一列的数据类型，然后转换为可变长度，最后将存储空间的请求总量减少到实际需求量。
如：固定长度的类型int、char、nchar等，在数据页中以不定长度的方式存储(存储真实数据长度)。
1、SSMS行压缩
表->右键->存储->管理压缩->对所有分区使用相同压缩类型->右侧 选择row->立即执行->完成。
在压缩堆表或聚集索引时并不同时包含非聚集索引，因此需要另外单独对非聚集索引进行操作。
索引->右键->存储->管理压缩->对所有分区使用相同压缩类型->右侧 选择row->立即执行->完成。
2、T-SQL行压缩
--在现有表进行压缩--聚集alter table ceshi rebuild with(data_compression=row)--非聚集alter index new_name on ceshi rebuild with(data_compression=row) --在创建表时进行压缩create table yasuo( id int primary key, name varchar(50), mail varchar(50))with (data_compression=row)
创建时指定行压缩方式，这时并未发生改变。只要数据插入表中，该行即被压缩。
页压缩
页压缩通过执行额外的一些步骤增强了行压缩的功能。
页压缩步骤：行压缩、前缀压缩、字典压缩。
首先对于每一列将确定一个值，此值可以减少每一列中值的存储空间。一旦确定该值后，每一列的前缀值的行将被存储在页头中。所有的信息称为压缩信息，存储在页头之下。标识的值（前缀值）位于没列中，将由指向压缩信息部分中对应值的引用进行替换。
下一步字典压缩，搜索整个页面而非单个列，重复值被移动到页头的压缩信息部分，取而代之的是指向该值的引用。
在SSMS中页压缩步骤与行压缩步骤一致，只是选择压缩方式为Page。T-SQL中将row改成page即可。
需要注意
1、如果保留在内存中的数据是压缩的，一旦被选中，则必须先进行解压缩。
2、在插入新行时，数据也是行或页压缩的。
3、当更新或删除时，行压缩对象保留当前的压缩级别。但是页压缩可能需要重新计算，取决于发生变化的数据量。
用哪种压缩
需要频繁更新的对象应该使用行压缩。
只是执行读取操作的应该使用页压缩。
SQL Server索引、表压缩
标签：
小编还为您整理了以下内容，可能对您也有帮助：
SQLServer2008中的数据压缩功能
SQL Server SP 为我们带来了vardecimal功能 这项功能使得原来定长的decimal数据在数据文件中以可变长的格式存储 据称这项功能可以为典型的数据仓库节省 %的空间 而SQL Server 在这一基础上又进一步增强了数据压缩功能 SQL Server 现在支持行压缩和页面压缩两种选项 数据压缩选项可以在以下对象上启用
未创建聚簇索引的表
创建聚簇索引的表
非聚簇索引(对表设置压缩选项不会影响到该表上的非聚簇索引 因此聚簇索引的压缩需要单独设置)
索引视图
分区表和分区索引中的单个分区
为什么需要数据压缩
首先可能需要讨论的问题就是为什么在存储成本不断降低的今天 微软还要煞费苦心地在SQL Server中实现并且不断改进数据压缩技术呢?
尽管存储成本已经不再是传统意义上的首要考虑因素 但是这并不代表数据库尺寸不是一个问题 因为数据库尺寸除了会影响到存储成本之外 还极大地关联到管理成本和性能问题
首先我们来讨论为什么会有管理成本的问题?因为数据库需要备份 数据库的尺寸越大 那么备份时间就会越长 当然另外一点就是消耗的备份硬件成本也会随之提高(包括需要的备份介质成本和为了满足备份窗口而需要更高级的备份设备带来的采购成本) 还有一种管理成本就是数据库的维护成本 例如我们经常需要完成的DBCC任务 数据库尺寸越大 我们就需要更多的时间来完成这些任务
接着我们再看看性能问题 SQL Server在扫描磁盘读取数据的时候都是按照数据页为单位进行读取的 因此如果一张数据页中包含的数据行数越多 SQL Server在一次数据页IO中获得的数据就会越多 这样也就带来了性能的提升
最后考虑存储的成本 按照原先SQL Server SP 中vardecimal的压缩数据为例 %的空间节省也就意味着 %的存储成本 而按照SQL Server 当前放出的测试数据 采用新的数据压缩技术可以达到 X X的存储率 再加上如果企业要考虑容灾而增加的存储空间 这样节省的存储硬件成本也将是想当可观的
如何使用数据压缩
SQL Server 中的压缩选项可以在创建表或索引时通过Option进行设置 例如 CREATE TABLE TestTable (col int col varchar( )) WITH (DATA_PRESSION = ROW);
如果需要改变一个分区的压缩选项 则可以用以下语句 ALTER TABLE TestTable REBUILD PARTITION = WITH (DATA PRESSION = PAGE);
如果需要为分区表的各个分区设置不同的压缩选项 可以使用以下的语句 (SQL Server 可以对不同的分区使用不同的压缩选项 这一点对于数据仓库应用是非常重要的 因为数据仓库的事实表通常都会有一个或数个热分区 这些分区中的数据经常需要更新 为了避免数据压缩给这些分区上的数据更新带来额外的处理载荷 可以对这些分区关闭压缩选项) CREATE TABLE PartitionedTable (col int col varchar( )) ON PS (col ) WITH ( DATA_PRESSION = ROW ON PARTITIONS( ) DATA_PRESSION = PAGE ON PARTITION( TO ));
如果是为某个索引设置压缩选项的话 可以使用
CREATE INDEX IX_TestTable_Col ON TestTable (Col ) WITH (DATA_PRESSION = ROW);
如果是修改某个索引的压缩选项 可以使用 ALTER INDEX IX_TestTable_Col ON TestTable REBUILD WITH (DATA_PRESSION = ROW);
SQL Server 同时还提供了一个名为sp_estimate_data_pression_savings存储过程帮助DBA估计激活压缩选项后对象尺寸
数据压缩是怎样工作的
对于行压缩 SQL Server 采用以下三种方法来节省存储空间
减少了与记录相关联的元数据开销 此元数据为有关列 列长度和偏移量的信息 在某些情况下 元数据开销可能大于旧的存储格式
它对于数值类型(例如 integer decimal和float)和基于数值的类型(例如 datetime和money)使用可变长度存储格式
它通过使用不存储空字符的可变长度格式来存储定长字符串
对于页面压缩 SQL Server 则是在一张数据页面上依次采用
行压缩
前缀压缩
字典压缩
配置数据压缩功能需要注意的
尽管SQL Server 的数据压缩功能非常有价值 但是仍然需要注意一些问题
数据压缩功能仅在企业版和开发版中可用
数据压缩可以让一张数据页存储更多的数据行 但是并不能改变单行数据最长 字节这一*
在一张已经设置了数据压缩的表上创建聚簇索引时 聚簇索引默认继承原表上的压缩选项
在未设置聚簇索引的表上设置页面压缩时 只有以下情况才会获得页面压缩的实际效果
数据使用BULK INSERT语法添加到表中
数据使用INSERT INTO WITH (TABLOCK)语法添加到表中
执行带有页面压缩选项的ALTER TABLE REBUILD命令
在未设置聚簇索引的表上更改压缩选项 会导致该表上所有非聚簇索引都需要重建 因为这些非聚簇索引指向的数据行地址已经都发生了改变
在改变压缩选项时所需要的临时空间大小与创建索引是所需要的空间是一样的 因此对于分区表 我们可以逐个分区设置压缩选项来减少临时空间的需求压力
由于SQL Server 中数据压缩技术其实是SQL Server SP 中vardecimal技术的一个超集 因此设置了数据压缩后就没有必要保留vardecimal了 当然SQL Server 为了保持向后兼容性 在当前版本中仍然保留了vardecimal 但是SQL Server 的下一个版本及可能就会弃用vardecimal选项 因此做了这些设置的数据库应该尽早改变到数据压缩设置下
lishixin/Article/program/Oracle/201311/17483SQLServer2008中的数据压缩功能
SQL Server SP 为我们带来了vardecimal功能 这项功能使得原来定长的decimal数据在数据文件中以可变长的格式存储 据称这项功能可以为典型的数据仓库节省 %的空间 而SQL Server 在这一基础上又进一步增强了数据压缩功能 SQL Server 现在支持行压缩和页面压缩两种选项 数据压缩选项可以在以下对象上启用
未创建聚簇索引的表
创建聚簇索引的表
非聚簇索引(对表设置压缩选项不会影响到该表上的非聚簇索引 因此聚簇索引的压缩需要单独设置)
索引视图
分区表和分区索引中的单个分区
为什么需要数据压缩
首先可能需要讨论的问题就是为什么在存储成本不断降低的今天 微软还要煞费苦心地在SQL Server中实现并且不断改进数据压缩技术呢?
尽管存储成本已经不再是传统意义上的首要考虑因素 但是这并不代表数据库尺寸不是一个问题 因为数据库尺寸除了会影响到存储成本之外 还极大地关联到管理成本和性能问题
首先我们来讨论为什么会有管理成本的问题?因为数据库需要备份 数据库的尺寸越大 那么备份时间就会越长 当然另外一点就是消耗的备份硬件成本也会随之提高(包括需要的备份介质成本和为了满足备份窗口而需要更高级的备份设备带来的采购成本) 还有一种管理成本就是数据库的维护成本 例如我们经常需要完成的DBCC任务 数据库尺寸越大 我们就需要更多的时间来完成这些任务
接着我们再看看性能问题 SQL Server在扫描磁盘读取数据的时候都是按照数据页为单位进行读取的 因此如果一张数据页中包含的数据行数越多 SQL Server在一次数据页IO中获得的数据就会越多 这样也就带来了性能的提升
最后考虑存储的成本 按照原先SQL Server SP 中vardecimal的压缩数据为例 %的空间节省也就意味着 %的存储成本 而按照SQL Server 当前放出的测试数据 采用新的数据压缩技术可以达到 X X的存储率 再加上如果企业要考虑容灾而增加的存储空间 这样节省的存储硬件成本也将是想当可观的
如何使用数据压缩
SQL Server 中的压缩选项可以在创建表或索引时通过Option进行设置 例如 CREATE TABLE TestTable (col int col varchar( )) WITH (DATA_PRESSION = ROW);
如果需要改变一个分区的压缩选项 则可以用以下语句 ALTER TABLE TestTable REBUILD PARTITION = WITH (DATA PRESSION = PAGE);
如果需要为分区表的各个分区设置不同的压缩选项 可以使用以下的语句 (SQL Server 可以对不同的分区使用不同的压缩选项 这一点对于数据仓库应用是非常重要的 因为数据仓库的事实表通常都会有一个或数个热分区 这些分区中的数据经常需要更新 为了避免数据压缩给这些分区上的数据更新带来额外的处理载荷 可以对这些分区关闭压缩选项) CREATE TABLE PartitionedTable (col int col varchar( )) ON PS (col ) WITH ( DATA_PRESSION = ROW ON PARTITIONS( ) DATA_PRESSION = PAGE ON PARTITION( TO ));
如果是为某个索引设置压缩选项的话 可以使用
CREATE INDEX IX_TestTable_Col ON TestTable (Col ) WITH (DATA_PRESSION = ROW);
如果是修改某个索引的压缩选项 可以使用 ALTER INDEX IX_TestTable_Col ON TestTable REBUILD WITH (DATA_PRESSION = ROW);
SQL Server 同时还提供了一个名为sp_estimate_data_pression_savings存储过程帮助DBA估计激活压缩选项后对象尺寸
数据压缩是怎样工作的
对于行压缩 SQL Server 采用以下三种方法来节省存储空间
减少了与记录相关联的元数据开销 此元数据为有关列 列长度和偏移量的信息 在某些情况下 元数据开销可能大于旧的存储格式
它对于数值类型(例如 integer decimal和float)和基于数值的类型(例如 datetime和money)使用可变长度存储格式
它通过使用不存储空字符的可变长度格式来存储定长字符串
对于页面压缩 SQL Server 则是在一张数据页面上依次采用
行压缩
前缀压缩
字典压缩
配置数据压缩功能需要注意的
尽管SQL Server 的数据压缩功能非常有价值 但是仍然需要注意一些问题
数据压缩功能仅在企业版和开发版中可用
数据压缩可以让一张数据页存储更多的数据行 但是并不能改变单行数据最长 字节这一*
在一张已经设置了数据压缩的表上创建聚簇索引时 聚簇索引默认继承原表上的压缩选项
在未设置聚簇索引的表上设置页面压缩时 只有以下情况才会获得页面压缩的实际效果
数据使用BULK INSERT语法添加到表中
数据使用INSERT INTO WITH (TABLOCK)语法添加到表中
执行带有页面压缩选项的ALTER TABLE REBUILD命令
在未设置聚簇索引的表上更改压缩选项 会导致该表上所有非聚簇索引都需要重建 因为这些非聚簇索引指向的数据行地址已经都发生了改变
在改变压缩选项时所需要的临时空间大小与创建索引是所需要的空间是一样的 因此对于分区表 我们可以逐个分区设置压缩选项来减少临时空间的需求压力
由于SQL Server 中数据压缩技术其实是SQL Server SP 中vardecimal技术的一个超集 因此设置了数据压缩后就没有必要保留vardecimal了 当然SQL Server 为了保持向后兼容性 在当前版本中仍然保留了vardecimal 但是SQL Server 的下一个版本及可能就会弃用vardecimal选项 因此做了这些设置的数据库应该尽早改变到数据压缩设置下
lishixin/Article/program/Oracle/201311/17483sql server 重新生成索引可以减小数据库空间吗
sql server 重新生成索引可以减小数据库空间。
理由分析：
一、重新生成索引：
1、命令： ALTER INDEX 索引名 on 表名 REBUILD
2、解析：重新生成索引将删除该索引并创建一个新索引。此过程中将删除碎片，通过使用指定的或现有的填充因子设置压缩页来回收磁盘空间，并在连续页中对索引行重新排序（根据需要分配新页）。这样可以减少获取所请求数据所需的页读取数，从而提高磁盘性能。
二、重新组织索引：
1、命令：ALTER INDEX 索引名 on 表名 reorganize
2、解析： 重新组织索引是通过对叶页进行物理重新排序，使其与叶节点的逻辑顺序（从左到右）相匹配，从而对表或视图的聚集索引和非聚集索引的叶级别进行碎片整理。使页有序可以提高索引扫描的性能。索引在分配给它的现有页内重新组织，而不会分配新页。如果索引跨多个文件，将一次重新组织一个文件，不会在文件之间迁移页。
重新组织还会压缩索引页。如果还有可用的磁盘空间，将删除此压缩过程中生成的所有空页。压缩基于 sys.indexes 目录视图中的填充因子值。
重新组织进程使用最少的系统资源。而且，重新组织是自动联机执行的。该进程不持有长期阻塞锁，所以不会阻止运行查询或更新。
索引碎片不太多时，可以重新组织索引。
sql server 重新生成索引可以减小数据库空间吗
sql server 重新生成索引可以减小数据库空间。
理由分析：
一、重新生成索引：
1、命令： ALTER INDEX 索引名 on 表名 REBUILD
2、解析：重新生成索引将删除该索引并创建一个新索引。此过程中将删除碎片，通过使用指定的或现有的填充因子设置压缩页来回收磁盘空间，并在连续页中对索引行重新排序（根据需要分配新页）。这样可以减少获取所请求数据所需的页读取数，从而提高磁盘性能。
二、重新组织索引：
1、命令：ALTER INDEX 索引名 on 表名 reorganize
2、解析： 重新组织索引是通过对叶页进行物理重新排序，使其与叶节点的逻辑顺序（从左到右）相匹配，从而对表或视图的聚集索引和非聚集索引的叶级别进行碎片整理。使页有序可以提高索引扫描的性能。索引在分配给它的现有页内重新组织，而不会分配新页。如果索引跨多个文件，将一次重新组织一个文件，不会在文件之间迁移页。
重新组织还会压缩索引页。如果还有可用的磁盘空间，将删除此压缩过程中生成的所有空页。压缩基于 sys.indexes 目录视图中的填充因子值。
重新组织进程使用最少的系统资源。而且，重新组织是自动联机执行的。该进程不持有长期阻塞锁，所以不会阻止运行查询或更新。
索引碎片不太多时，可以重新组织索引。
SQL数据库如何压缩(数据库文件怎么压缩)
1、首先从开始菜单着手，打开开始菜单栏，在菜单栏上找到我们已经安装的SQLserver2008，单击打开它。
2、打开SQLserver2008数据库，来到登录界面，在这里我们只需要输入登录服务器名（电脑IP地址）、登录身份、账号、密码，然后单击登录。
3、成功登录进入SQL数据库，可以看到连接的数据库基本信息，展开数据库节点，单击数据库然后使用鼠标右键，在弹出的菜单中选择附加。
4、接着弹出附加数据库的界面，这里我们只需要单击界面上的添加按钮就可以了。
5、单击添加按钮后，新弹出来一个框，让你选择你要附加的数据文件路径，选择到我们要附加的数据库文件，单击确定按钮。
6、返回到附加数据库的界面，这是我们可以从界面上看到选择的附加数据库文件信息，然后在上方可以修改要附加的数据库名称。
详细讲解SQL Server索引的性能问题[5]
（ ）在选择索引键时 设法选择那些采用小数据类型的列作为键以使每个索引页能够容纳尽可能多的索引键和指针 通过这种方式 可使一个查询必须遍历的索引页面降到最小 此外 尽可能地使用整数为键值 因为它能够提供比任何数据类型都快的访问速度
五 索引的维护
上面讲到 某些不合适的索引影响到SQL Server的性能 随着应用系统的运行 数据不断地发生变化 当数据变化达到某一个程度时将会影响到索引的使用 这时需要用户自己来维护索引 索引的维护包括
重建索引
随着数据行的插入 删除和数据页的 有些索引页可能只包含几页数据 另外应用在执行大块I/O的时候 重建非聚簇索引可以降低分片 维护大块I/O的效率 重建索引实际上是重新组织B 树空间 在下面情况下需要重建索引
（ ）数据和使用模式大幅度变化
（ ）排序的顺序发生改变
（ ）要进行大量插入操作或已经完成
（ ）使用大块I/O的查询的磁盘读次数比预料的要多
（ ）由于大量数据修改 使得数据页和索引页没有充分使用而导致空间的使用超出估算
（ ）dbcc检查出索引有问题
当重建聚簇索引时 这张表的所有非聚簇索引将被重建
lishixin/Article/program/SQLServer/201311/22524