SQLSERVER moves data from one file group to another

If SQLSERVER moves data from one file group to another, experienced heroes can ignore this article ~ People with experience in this issue know how to do this, because our company's data volume is not big, and we don't know how to do this experiment. Today, I asked for help from the pineapple heroes in the QQ group, I finally know how to do it.

If SQLSERVER moves data from one file group to another, experienced heroes can ignore this article ~ People with experience in this issue know how to do this, because our company's data volume is not big, and we don't know how to do this experiment. Today, I asked for help from the pineapple heroes in the QQ group, I finally know how to do it.

SQLSERVER moves data from one file group to another

Experienced heroes can ignore this article ~

People with experience in this issue know how to do this, because our company has a small amount of data and we don't know how to do this experiment.

Today, I asked for help from the pineapple warrior in the QQ group and finally learned how to do it.

I will definitely ask questions I don't understand. I have asked this weak question in the Forum before. However, because it is too weak, people will reply casually.

Then bamboo marks it as the answer ~

Mobile Data:

1. Table partitions

2. No table partitions

I will only discuss the absence of table partitions. The table partitions are not very clear.

Example

For example, you have three file groups, one of which is the main file group.

Test script:

1 USE master 2 GO 3 4 5 if exists (SELECT * FROM sys. [databases] WHERE [database_id] = DB_ID ('test') 6 drop database [Test] 7 8 -- 1. create database 9 create database [Test] 10 GO11 12 USE [Test] 13 GO14 15 16 -- 2. create File Group 17 alter database [Test] 18 add filegroup [FG_Test_Id_01] 19 20 alter database [Test] 21 add filegroup [FG_Test_Id_02] 22 23 24 25 -- 3. create File 26 alter database [Test] 27 ADD FILE28 (NAME = n' FG _ TestUnique_Id_01_data ', FILENAME = n' E: \ FG_TestUnique_Id_01_data.ndf', SIZE = 1 MB, FILEGROWTH = 1 MB) 29 to filegroup [FG_Test_Id_01]; 30 31 alter database [Test] 32 ADD FILE33 (NAME = n'fg _ TestUnique_Id_02_data ', FILENAME = n'e: \ FG_TestUnique_Id_02_data.ndf ', SIZE = 1 MB, FILEGROWTH = 1 MB) 34 to filegroup [FG_Test_Id_02]; 35 36 37 -- 4. CREATE a TABLE. The TABLE data is stored in the file group [FG_Test_Id_01]. 38 create table aa (id INT, cname NVARCHAR (4000) ON [FG_Test_Id_01] 39 GO40 41 42 -- 5. INSERT data 43 insert into [dbo]. [aa] 44 SELECT 1, REPLICATE ('s ', 3000) 45 GO 50046 47 48 -- 6. query Data 49 SELECT * FROM [dbo]. [aa] 50 51 52 -- 7. CREATE a clustered index on the [FG_Test_Id_02] File Group 53 create clustered index PK_ID ON [dbo]. [aa] ([id]) WITH (ONLINE = ON) ON [FG_Test_Id_02] 54 GO55 56 57 -- 8. let's take a look at the logical file name of the file group 58 EXEC [sys]. [sp_helpdb] @ dbname = TEST -- sysname59 60 61 62 -- 9. shrink the FG_Test_Id_01 File Group 63 dbcc shrinkfile (FG_TestUnique_Id_01_data, 1) 64 65 66 -- 10. you can choose to drop the clustered INDEX, or choose not to drop the clustered INDEX 67 drop index PK_ID ON [dbo]. [aa] 68 69 USE master70 GO71 drop database [Test]

Although the above script is simple, it hides many knowledge points.

Knowledge Point 1: Two file groups have been created. The database now has three file groups, including the primary file group. When you do not specify any parameters, the data file created by default is 1 MB.

Knowledge Point 2: Insert data. Because the table is created in the [FG_Test_Id_01] File Group, data is stored in E: \ FG_TestUnique_Id_01_data.ndf.

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

Knowledge Point 3: Create a clustered index. In fact, this sentence contains several actions. In the E: \ FG_TestUnique_Id_02_data.ndf file, allocate the page and put the data in the aa table.

Put it in the E: \ FG_TestUnique_Id_02_data.ndf file. In fact, the clustered index becomes the intermediary of mobile data.

Another study of SQLSERVER clustered index and non-clustered index (I) The article writes: clustered index leaf node is data, we will aggregate index (must be clustered index, non-clustered index is not)

Built on the E: \ FG_TestUnique_Id_02_data.ndf fileData PageAndClustered index pageMove to the E: \ FG_TestUnique_Id_02_data.ndf File

Because SQL server does not have the syntax of ALTER TABLE aa (id INT, cname NVARCHAR (4000) ON [FG_Test_Id_01 ],

That is to say, once you create a table and the table already has data, if you want to move the table data, you can only move the table data through the clustered index intermediary.

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

The data has been moved to the E: \ FG_TestUnique_Id_02_data.ndf file.

Knowledge Point 4: Why should I add WITH (ONLINE = ON )?? If your application cannot be stopped, add WITH (ONLINE = ON) to create an index ONLINE.

For details, see create index (Transact-SQL)

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

Knowledge Point 5: Why do I need to contract the [FG_Test_Id_01] file group file E: \ FG_TestUnique_Id_01_data.ndf

Because the data has been moved to the E: \ FG_TestUnique_Id_02_data.ndf file, since it has been moved to the E: \ FG_TestUnique_Id_02_data.ndf File

Why is the E: \ FG_TestUnique_Id_01_data.ndf file still showing the size of 5 MB ??

Let's take a look at this article: how can we insert and delete data in clustered index tables?

I deleted the data, but SQLSERVER didn't completely release the space. In fact, moving the data to another file/file group is equivalent to deleting the data.

But SQLSERVER does not release these spaces, so I need to shrink the FG_Test_Id_01 file group.

1 -- 9. compress the FG_Test_Id_01 file group file 2 dbcc shrinkfile (FG_TestUnique_Id_01_data, 1)

Knowledge Point 6: Dbcc shrinkfile (FG_TestUnique_Id_01_data, 1)

The unit of SHRINKFILE is MB, and the preceding statement is to shrink to 1 MB. In fact, if the E: \ FG_TestUnique_Id_01_data.ndf file contains data

And it takes 2 MB space, so you can only contract to 2 MB using dbcc shrinkfile (FG_TestUnique_Id_01_data, 1 ).

The data size will not be reduced to 1 MB, because it takes up space and you cannot contract it. If you don't believe it, you can test it.

Dbcc shrinkfile (Transact-SQL)

Initial size and dbcc shrinkfile of user database

Summary

Although mobile data operations are relatively simple, there are a lot of knowledge points, some people only know how to do it, and I don't know why I think it is so bad.

Root search is my feature o (feature _ blank) o haha

Note:: Moving data can only move one table to one table. If a clustered index already exists in the table, drop it first.

Check the file group in which the table data is moved.

1 USE [Northwind]2 GO3 EXEC [sys].[sp_help] @objname = N'[dbo].[Categories]' -- nvarchar(776)

If anything is wrong, you are welcome to make a brick o

Supplement:

Table Partitioning Method

There are about three

First, create a new data file, file group, partition scheme, and partition function.

For example, three new data files and file groups are created, and the partition scheme and partition function are used for the three new data files and file groups.

Old table: the following three methods are applicable to no partitions or partitions:

Method 1: Create an intermediate table (new table). The intermediate table is created in the new partition scheme. Then, use the insert into new table select * from old table method.

Drop old table

Method 2: Create an intermediate table. The intermediate table is created in the new partition scheme and then switched to, because the new partition scheme has three partitions.

Then, switch to can only switch all the data in the table to one of the partitions.

USE Sales go alter table old table switch partition 1 TO new table partition 1 GO

Or

USE Sales go alter table old table switch partition 1 TO new table partition 2GO

Or

USE Sales go alter table old table switch partition 1 TO new table partition 3GO

The preceding three statements will move all the data in the old table to a PARTITION in the new table. The number of the PARTITION to which the PARTITION is migrated is specified by the last number.

Drop the old table

Method 3: Drop the clustered index of the old table (if any), create a clustered index on the old table, and specify the new partition scheme when creating the clustered index.

This will move the table data to the new partition scheme, that is, in the new data file. This method is the same as moving data without table partitions.

References:Http://blog.csdn.net/smallfools/article/details/4930810

Delete a file or delete a file group

Delete an invalid file group (SQL Server)

--Transact-SQLUSE master;GOALTER DATABASE AdventureWorks2012REMOVE FILE test1dat3 ;ALTER DATABASE AdventureWorks2012REMOVE FILE test1dat4 ;GO--Transact-SQLUSE master;GOALTER DATABASE AdventureWorks2012REMOVE FILEGROUP Test1FG1 ;GO