filegroup server sql 修改_sql server中filegroup与partition解析

0.参考文献：

1.基础知识

一直对于表分区和filegroup的概念不是很清晰，今天通过具体的实例来学习什么事filegroup和partition，以及他们的作用。

1.1通过文件组来管理文件的特性

对于用户角度来说，需对创建的对象指定存储的文件组只有三种数据对象：表,索引和大对象(LOB)

使用文件组可以隔离用户和文件，使得用户针对文件组来建立表和索引，而不是实际磁盘中的文件。也就是可以指定将表和索引存储在不同的文件上面。

使用文件组来管理文件可以使得同一文件组内的不同文件分布在不同的硬盘中，极大的提高了IO性能.

SQL SERVER会根据每个文件设置的初始大小和增长量会自动分配新加入的空间，假设在同一文件组中的文件A设置的大小为文件B的两倍，新增一个数据占用三页(Page),则按比例将2页分配到文件A中，1页分配到文件B中.

1.2文件的分类

首要文件:这个文件是必须有的，而且只能有一个。这个文件额外存放了其他文件的位置等信息.扩展名为.mdf

次要文件:可以建任意多个，用于不同目的存放.扩展名为.ndf，用于存放数据，而不是日志。

日志文件：存放日志,扩展名为.ldf

在SQL SERVER 2008之后，还新增了文件流数据文件和全文索引文件.

我们可以通过sys.database_files这个视图查看数据库中的文件情况:

select * from sys.database_files

1.3创建filegroup，并将索引创建在指定的filegroup中

可以通过TSQL语句来创建文件组，也可以通过SSMS来创建文件组，这个在后面会提到。这里不再重复。下面我们重点来介绍如何将索引创建在指定的filegroup中，而不跟数据放在一起。首先来看我创建好的filegroup，已经这些filegroup所对应的files，如下图所示：

然后我们通过如下TSQL语句来测试

View Code

useTESTDB--step1.插入数据

select * into OrderDetail fromAdventureWorks2008R2.Sales.SalesOrderDetail--step2:查看表的索引信息，发现所有页都在pagefid=1上面，并且indexid都为0.因为没有创建聚集索引之前是堆表

dbcc ind ( TESTDB, [dbo.OrderDetail], -1)--step3:在分区上创建聚集索引，聚集索引不要放在IndexStorage这个filegroup当中，因为聚集索引就是数据本身。--如果将聚集索引on IndexStorage的话，那么所有数据都将会在IndexStorage这个filegroup所对应的文件上

create clustered index idx_c_SSalesOrderDetailID onOrderDetail(SalesOrderDetailID)--step4:此时发现原先indexid=0的都变成了index=1

dbcc ind ( TESTDB, [dbo.OrderDetail], -1)--step5:在IndexStorage这个file group上面创建非聚集索引

CREATE NONCLUSTERED INDEX idx_nc_SalesOrderID ON dbo.OrderDetail(SalesOrderID) onIndexStorageCREATE NONCLUSTERED INDEX idx_nc_CarrierTrackingNumber ON dbo.OrderDetail(CarrierTrackingNumber) onIndexStorageCREATE NONCLUSTERED INDEX idx_nc_UnitPrice ON dbo.OrderDetail(UnitPrice) onIndexStorage--step6:再次查看页信息我们发现只有indexid=1的pagefid=1，也就是说聚集索引都在TESTDB.MDF这个文件上，--而indexid=2,3,4所对应的pagefid=3，表明已经将索引建立到IndexStorage这个filegroup上面去了，对应的是IndexStorage.ndf这个文件。

dbcc ind ( TESTDB, [dbo.OrderDetail], -1)--step7:创建复合索引，

CREATE NONCLUSTERED INDEX idx_nc_com ONdbo.OrderDetail(SalesOrderID,CarrierTrackingNumber,UnitPrice)--step8:默认情况下会使用Primary这个filegroup，filefid=1.

dbcc ind ( TESTDB, [dbo.OrderDetail], -1)

总结：

在分区上创建聚集索引，聚集索引不要放在IndexStorage这个filegroup当中，因为聚集索引就是数据