SQLSERVER将一个文件组的数据移动到另一个文件组

SQLSERVER将一个文件组的数据移动到另一个文件组 有经验的大侠可以直接忽视这篇文章~ 这个问题有经验的人都知道怎麽做，因为我们公司的数据量不大没有这个需求，也不知道怎麽做实验 今天求助了QQ群里的菠萝大侠，终于知道怎麽做了 我自己会把不明白的问题一

SQLSERVER将一个文件组的数据移动到另一个文件组

有经验的大侠可以直接忽视这篇文章~

这个问题有经验的人都知道怎麽做，因为我们公司的数据量不大没有这个需求，也不知道怎麽做实验

今天求助了QQ群里的菠萝大侠，终于知道怎麽做了

我自己会把不明白的问题一定会问到底的，之前在论坛问过这个弱弱的问题，但是由于太弱了，人家随便答复了一下

然后斑竹就标记为答案了~

 

移动数据：

1、有表分区

2、没有表分区

我这里只讨论没有表分区的情况，表分区的情况还不是很明白

---

例子

比如：你有三个文件组，其中一个是主文件组

测试脚本：

<span> 1</span> <span>USE</span><span> master
</span><span> 2</span> <span>GO</span>
<span> 3</span> 
<span> 4</span> 
<span> 5</span> <span>IF</span> <span>EXISTS</span>(<span>SELECT</span> <span>*</span> <span>FROM</span> sys.<span>[</span><span>databases</span><span>]</span> <span>WHERE</span> <span>[</span><span>database_id</span><span>]</span><span>=</span><span>DB_ID</span>(<span>'</span><span>Test</span><span>'</span><span>))
</span><span> 6</span> <span>DROP</span> <span>DATABASE</span> <span>[</span><span>Test</span><span>]</span>
<span> 7</span> 
<span> 8</span> <span>--</span><span>1.创建数据库</span>
<span> 9</span> <span>CREATE</span> <span>DATABASE</span> <span>[</span><span>Test</span><span>]</span>
<span>10</span> <span>GO</span>
<span>11</span> 
<span>12</span> <span>USE</span> <span>[</span><span>Test</span><span>]</span>
<span>13</span> <span>GO</span>
<span>14</span> 
<span>15</span> 
<span>16</span> <span>--</span><span>2.创建文件组</span>
<span>17</span> <span>ALTER</span> <span>DATABASE</span> <span>[</span><span>Test</span><span>]</span>
<span>18</span> <span>ADD</span> FILEGROUP <span>[</span><span>FG_Test_Id_01</span><span>]</span>
<span>19</span> 
<span>20</span> <span>ALTER</span> <span>DATABASE</span> <span>[</span><span>Test</span><span>]</span>
<span>21</span> <span>ADD</span> FILEGROUP <span>[</span><span>FG_Test_Id_02</span><span>]</span>
<span>22</span> 
<span>23</span> 
<span>24</span> 
<span>25</span> <span>--</span><span>3.创建文件</span>
<span>26</span> <span>ALTER</span> <span>DATABASE</span> <span>[</span><span>Test</span><span>]</span>
<span>27</span> <span>ADD</span> <span>FILE</span>
<span>28</span> (NAME <span>=</span> N<span>'</span><span>FG_TestUnique_Id_01_data</span><span>'</span>,FILENAME <span>=</span> N<span>'</span><span>E:\FG_TestUnique_Id_01_data.ndf</span><span>'</span>,SIZE <span>=</span> 1MB, FILEGROWTH <span>=</span><span> 1MB )
</span><span>29</span> <span>TO</span> FILEGROUP <span>[</span><span>FG_Test_Id_01</span><span>]</span><span>;
</span><span>30</span> 
<span>31</span> <span>ALTER</span> <span>DATABASE</span> <span>[</span><span>Test</span><span>]</span>
<span>32</span> <span>ADD</span> <span>FILE</span>
<span>33</span> (NAME <span>=</span> N<span>'</span><span>FG_TestUnique_Id_02_data</span><span>'</span>,FILENAME <span>=</span> N<span>'</span><span>E:\FG_TestUnique_Id_02_data.ndf</span><span>'</span>,SIZE <span>=</span> 1MB, FILEGROWTH <span>=</span><span> 1MB )
</span><span>34</span> <span>TO</span> FILEGROUP <span>[</span><span>FG_Test_Id_02</span><span>]</span><span>;
</span><span>35</span> 
<span>36</span> 
<span>37</span> <span>--</span><span>4.创建表，这个表的数据存放在[FG_Test_Id_01] 文件组上</span>
<span>38</span> <span>CREATE</span> <span>TABLE</span> aa(id <span>INT</span> ,cname <span>NVARCHAR</span>(<span>4000</span>)) <span>ON</span> <span>[</span><span>FG_Test_Id_01</span><span>]</span> 
<span>39</span> <span>GO</span>
<span>40</span> 
<span>41</span> 
<span>42</span> <span>--</span><span>5.插入数据</span>
<span>43</span> <span>INSERT</span> <span>INTO</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>aa</span><span>]</span>
<span>44</span> <span>SELECT</span> <span>1</span>,<span>REPLICATE</span>(<span>'</span><span>s</span><span>'</span>,<span>3000</span><span>)
</span><span>45</span> <span>GO</span> <span>500</span>
<span>46</span> 
<span>47</span> 
<span>48</span> <span>--</span><span>6.查询数据</span>
<span>49</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>aa</span><span>]</span>
<span>50</span> 
<span>51</span> 
<span>52</span> <span>--</span><span>7.创建聚集索引在[FG_Test_Id_02]文件组上</span>
<span>53</span> <span>CREATE</span> <span>CLUSTERED</span> <span>INDEX</span> PK_ID <span>ON</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>aa</span><span>]</span>(<span>[</span><span>id</span><span>]</span>) <span>WITH</span>(ONLINE<span>=</span><span>ON</span>) <span>ON</span> <span>[</span><span>FG_Test_Id_02</span><span>]</span>
<span>54</span> <span>GO</span>
<span>55</span> 
<span>56</span> 
<span>57</span> <span>--</span><span>8.我们查看一下文件组的逻辑文件名</span>
<span>58</span> <span>EXEC</span> <span>[</span><span>sys</span><span>]</span>.<span>[</span><span>sp_helpdb</span><span>]</span> <span>@dbname</span> <span>=</span> TEST <span>--</span><span> sysname</span>
<span>59</span> 
<span>60</span> 
<span>61</span> 
<span>62</span> <span>--</span><span>9.收缩一下FG_Test_Id_01文件组文件</span>
<span>63</span> <span>DBCC</span> SHRINKFILE(FG_TestUnique_Id_01_data,<span>1</span><span>)
</span><span>64</span> 
<span>65</span> 
<span>66</span> <span>--</span><span>10.你可以选择drop掉聚集索引，也可以选择不drop掉聚集索引</span>
<span>67</span> <span>DROP</span> <span>INDEX</span> PK_ID <span>ON</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>aa</span><span>]</span>
<span>68</span> 
<span>69</span> <span>USE</span><span> master
</span><span>70</span> <span>GO</span>
<span>71</span> <span>DROP</span> <span>DATABASE</span> <span>[</span><span>Test</span><span>]</span>

上面的脚本虽然简单，但是隐藏了非常多的知识点

知识点1：创建了两个文件组，现在数据库有三个文件组，包括主文件组，当你不指定任何参数的时候默认创建出来的数据文件是1MB大小

知识点2：插入数据，因为表是创建在[FG_Test_Id_01]文件组上，所以数据都会放在E:\FG_TestUnique_Id_01_data.ndf

<span>1</span> <span>CREATE</span> <span>TABLE</span> aa(id <span>INT</span> ,cname <span>NVARCHAR</span>(<span>4000</span>)) <span>ON</span> <span>[</span><span>FG_Test_Id_01</span><span>]</span> 
<span>2</span> <span>GO</span>

 

知识点3：创建聚集索引，其实这句话里面包含了几个动作，在E:\FG_TestUnique_Id_02_data.ndf文件上分配页面，并把aa表的数据

放进去E:\FG_TestUnique_Id_02_data.ndf文件，其实这里聚集索引成为了移动数据的中介，我在

SQLSERVER聚集索引与非聚集索引的再次研究（上）文章写到：聚集索引叶子节点就是数据，我们把聚集索引（一定要是聚集索引，非聚集索引不是）

建立在E:\FG_TestUnique_Id_02_data.ndf文件上实际上就是把数据页面和聚集索引页面移动到E:\FG_TestUnique_Id_02_data.ndf文件里

因为SQLSERVER是没有 ALTER TABLE aa(id INT ,cname NVARCHAR(4000)) ON [FG_Test_Id_01] 这种语法上

就是说你一旦建表并且表中已经有数据之后，如果你要移动表数据，只能通过聚集索引这个中介来移动表数据

<span>1</span> <span>CREATE</span> <span>CLUSTERED</span> <span>INDEX</span> PK_ID <span>ON</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>aa</span><span>]</span>(<span>[</span><span>id</span><span>]</span>) <span>WITH</span>(ONLINE<span>=</span><span>ON</span>) <span>ON</span> <span>[</span><span>FG_Test_Id_02</span><span>]</span>
<span>2</span> <span>GO</span>

从下图可以看出数据都已经移动到E:\FG_TestUnique_Id_02_data.ndf文件上

知识点4：为什麽要加上WITH(ONLINE=ON)？？如果你的应用是不能停机的话，加上WITH(ONLINE=ON)就可以在线的创建索引

详情参考：CREATE INDEX (Transact-SQL)

<span>1</span> <span>CREATE</span> <span>CLUSTERED</span> <span>INDEX</span> PK_ID <span>ON</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>aa</span><span>]</span>(<span>[</span><span>id</span><span>]</span>) <span>WITH</span>(ONLINE<span>=</span><span>ON</span>) <span>ON</span> <span>[</span><span>FG_Test_Id_02</span><span>]</span>
<span>2</span> <span>GO</span>

 

知识点5：为什麽要收缩[FG_Test_Id_01]文件组文件E:\FG_TestUnique_Id_01_data.ndf

因为数据已经移动到E:\FG_TestUnique_Id_02_data.ndf文件上了，既然已经移到E:\FG_TestUnique_Id_02_data.ndf文件上

为什麽E:\FG_TestUnique_Id_01_data.ndf文件还显示5MB大小？？

大家可以看一下这篇文章：聚集索引表插入数据和删除数据的方式是怎样的

我delete了数据，SQLSERVER却没有完全释放空间，其实这里移动数据到别的文件/文件组相当于delete了数据了

但是SQLSERVER并没有释放这些空间，所以我需要收缩一下FG_Test_Id_01文件组文件

<span>1</span> <span>--</span><span>9.收缩一下FG_Test_Id_01文件组文件</span>
<span>2</span> <span>DBCC</span> SHRINKFILE(FG_TestUnique_Id_01_data,<span>1</span>)

 

 

知识点6：DBCC SHRINKFILE(FG_TestUnique_Id_01_data,1)

SHRINKFILE的单位是MB，上面的语句就是收缩到1MB大小，其实这里如果E:\FG_TestUnique_Id_01_data.ndf文件有数据的话

并且需要占用2MB大小的空间，那么您使用DBCC SHRINKFILE(FG_TestUnique_Id_01_data,1)这句话只能收缩到2MB大小

并不会收缩到1MB大小的，因为这些数据需要占用空间，你怎麽收缩都收缩不了的，不信的话您们可以测试一下

DBCC SHRINKFILE (Transact-SQL)

user database的initial size和dbcc shrinkfile

---

总结

虽然移动数据的动作比较简单，但是知识点挺多的，有些人只知道怎麽做，不知道为什么我觉得这样不好

寻根问底是我的特性o(∩_∩)o 哈哈

 

注意：移动数据只能一张表一张表的移动，如果表里预先已经有聚集索引，需要先drop掉

移动之前查一下表中的数据在哪个文件组中

<span>1</span> <span>USE</span> <span>[</span><span>Northwind</span><span>]</span>
<span>2</span> <span>GO</span>
<span>3</span> <span>EXEC</span> <span>[</span><span>sys</span><span>]</span>.<span>[</span><span>sp_help</span><span>]</span> <span>@objname</span> <span>=</span> N<span>'</span><span>[dbo].[Categories]</span><span>'</span> <span>--</span><span> nvarchar(776)</span>

 

如有不对的地方，欢迎大家拍砖o(∩_∩)o

 

2014-1-19 补充：

有表分区的方法

大概有三种

先创建新的数据文件，文件组，分区方案，分区函数

例如创建三个新的数据文件和文件组，分区方案和分区函数对于这三个新的数据文件和文件组

旧表：无论已经分区或者还没有分区都适用下面三种方法：

方法一：建立中间表（新表），中间表建立在新的分区方案上，然后用insert into 新表 select * from 旧表的方法，插入完毕之后

drop掉旧表

 

方法二：建立中间表，中间表建立在新的分区方案上，然后用switch to，因为新分区方案有三个分区

那么，switch to只能够将表的全部数据切换到其中一个分区

<span>USE</span><span> Sales    
</span><span>GO</span>    
<span>ALTER</span> <span>TABLE</span> 旧表 SWITCH PARTITION <span>1</span> <span>TO</span> 新表 PARTITION <span>1</span> 
<span>GO</span>

或者

<span>USE</span><span> Sales    
</span><span>GO</span>    
<span>ALTER</span> <span>TABLE</span> 旧表 SWITCH PARTITION <span>1</span> <span>TO</span> 新表 PARTITION <span>2</span>
<span>GO</span>

或者

<span>USE</span><span> Sales    
</span><span>GO</span>    
<span>ALTER</span> <span>TABLE</span> 旧表 SWITCH PARTITION <span>1</span> <span>TO</span> 新表 PARTITION <span>3</span>
<span>GO</span>

上面的三条语句就会把旧表的所有数据移动到新表的某个分区，至于移动到哪个分区由最后那个数字来指定PARTITION 要移动到的分区的数字

完成后drop掉旧表

 

方法三：drop掉旧表的聚集索引（如果有），然后在旧表上创建一个聚集索引，创建聚集索引的时候指定新的分区方案

这样就会把表数据移动到新分区方案，即新的数据文件里，这个方法跟没有表分区的数据移动是一样的

 

参考文章：http://blog.csdn.net/smallfools/article/details/4930810

 

删除文件和删除文件组

 

删除失效文件组 (SQL Server)

 

<span>--</span><span>Transact-SQL</span>
<span>USE</span><span> master;
</span><span>GO</span>
<span>ALTER</span> <span>DATABASE</span><span> AdventureWorks2012
REMOVE </span><span>FILE</span><span> test1dat3 ;
</span><span>ALTER</span> <span>DATABASE</span><span> AdventureWorks2012
REMOVE </span><span>FILE</span><span> test1dat4 ;
</span><span>GO</span>


<span>--</span><span>Transact-SQL</span>
<span>USE</span><span> master;
</span><span>GO</span>
<span>ALTER</span> <span>DATABASE</span><span> AdventureWorks2012
REMOVE FILEGROUP Test1FG1 ;
</span><span>GO</span>