删除和创建ms sql的分区文件
今天测试ms sql 的表分区的时候，不小心搞错了分区的条件。然后我想重新做一次，操作流程如下(按顺序)
1：删除SCHEME
DROP PARTITION SCHEME TestSPScheme
2: 删除FUNCTION
DROP PARTITION FUNCTION TestSPFunction
3:查询分区的DATABAE FILE,并删除
SELECT * FROM sys.database_files
ALTER DATABASE MOD_HK REMOVE FILE MOD_HKFirstPart
删除后，一定要备份下log,否则 MOD_HKFirstPart只是写了标记
state_desc size
OFFLINE 640
OFFLINE 640
执行备份日志后就清除了OFFLINE 的了。
backup log MOD_HK TO DISK='NUL:'
4：查询新增的GROUP ,并执行删除命令
查询GROUP: SELECT * FROM sys.filegroups
删除GROUP:
ALTER DATABASE MOD_HK
REMOVE FILEGROUP FGSP1
附上完整创建分区的例子:
1:创建基础表
declare @i int
set @i =0
while @i < 50000
begin
set @i = @i + 1
insert marcus (mcode,name,qty,Amount,createon,createby)
values('mcode_'+CONVERT(varchar(8),@i),'name_'+CONVERT(varchar(8),@i),@i,@i*100,DATEADD(DAY,@i,GETDATE()),'marcus')
end
2:创建数据库文件组及组，我这里创建了3个
ALTER DATABASE MOD_HK
ADD FILEGROUP FGSP1
GO
ALTER DATABASE MOD_HK
ADD FILE
(
NAME = 'MOD_HKFirstPart',
FILENAME = 'D:\SPPartition\FirstPart\MOD_HKFirstPart.ndf',
SIZE = 5120 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 5120 KB
) TO FILEGROUP FGSP1
GO
ALTER DATABASE MOD_HK
ADD FILEGROUP FGSP2
GO
ALTER DATABASE MOD_HK
ADD FILE
(
NAME = 'MOD_HKSecondPart',
FILENAME = 'D:\SPPartition\SecondPart\MOD_HKSecondPart.ndf',
SIZE = 5120 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 5120 KB
) TO FILEGROUP FGSP2
GO
ALTER DATABASE MOD_HK
ADD FILEGROUP FGSP3
GO
ALTER DATABASE MOD_HK
ADD FILE
(
NAME = 'MOD_HKThirdPart',
FILENAME = 'D:\SPPartition\ThirdPart\MOD_HKThirdPart.ndf',
SIZE = 5120 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 5120 KB
) TO FILEGROUP FGSP3
GO
3：查看文件组情况
SELECT * FROM sys.filegroups
显示情况
name data_space_id type type_desc is_default filegroup_guid log_filegroup_id is_read_only
PRIMARY 1 FG ROWS_FILEGROUP 1 NULL NULL 0
FGSP1 2 FG ROWS_FILEGROUP 0 64DDC2BD-23A0-4C98-9091-214DAB3C91A8 NULL 0
FGSP2 3 FG ROWS_FILEGROUP 0 32853D4B-B6A8-4844-8775-E2761CCD0CE1 NULL 0
FGSP3 4 FG ROWS_FILEGROUP 0 80DF9C6A-DF01-4AFF-ABFD-3E051E261CFF NULL 0
4:查看文件组文件情况
SELECT * FROM sys.database_files
file_id file_guid type type_desc data_space_id name physical_name state state_desc size max_size growth is_media_read_only is_read_only is_sparse is_percent_growth is_name_reserved create_lsn drop_lsn read_only_lsn read_write_lsn differential_base_lsn differential_base_guid differential_base_time redo_start_lsn redo_start_fork_guid redo_target_lsn redo_target_fork_guid backup_lsn
1 A5D905EF-8934-4256-B1A9-167F60DCB23E 0 ROWS 1 MOD_HK D:\MySoftware\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MOD_HK.mdf 0 ONLINE 1920 -1 128 0 0 0 0 0 NULL NULL NULL NULL 100000000042400082 C92A9BE2-9855-4BC4-99FB-46A2B797D501 2013-10-08 03:20:04.547 NULL NULL NULL NULL NULL
2 0FFB8F77-729F-4711-A5C8-4D9C8CACE4B0 1 LOG 0 MOD_HK_log D:\MySoftware\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MOD_HK.ldf 0 ONLINE 7928 268435456 10 0 0 0 1 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
3 AED349BD-D83E-425F-9372-7E212A3429BE 0 ROWS 2 MOD_HKFirstPart D:\SPPartition\FirstPart\MOD_HKFirstPart.ndf 0 ONLINE 640 -1 640 0 0 0 0 0 209000000059000001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
4 0CC39126-5E43-4A16-95DC-E71A646758AD 0 ROWS 3 MOD_HKSecondPart D:\SPPartition\SecondPart\MOD_HKSecondPart.ndf 0 ONLINE 640 -1 640 0 0 0 0 0 209000000063800001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
5 01B55786-ED50-4786-AEDC-A6CD539C6E12 0 ROWS 4 MOD_HKThirdPart D:\SPPartition\ThirdPart\MOD_HKThirdPart.ndf 0 ONLINE 640 -1 640 0 0 0 0 0 209000000068600001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
5：插入分区的函数和分区的架构
--删除时，须先删除TestSPScheme
-- drop PARTITION SCHEME TestSPScheme
--drop PARTITION FUNCTION TestSPFunction
CREATE PARTITION FUNCTION TestSPFunction (Datetime)
AS RANGE RIGHT FOR VALUES ( '2059-08-21 11:32:49.073', '2119-12-25 11:33:09.417','2100-02-27 11:33:03.030')
GO
CREATE PARTITION SCHEME TestSPScheme
AS PARTITION TestSPFunction
TO ([PRIMARY], FGSP1, FGSP2,FGSP3 )
GO
5：创建分区
ALTER TABLE marcus add CONSTRAINT [PK_createon] PRIMARY KEY CLUSTERED (createon)
ON TestSPScheme(createon)
注意这里如果你的表是设置了簇集主键时，需要先删除。
6：查看数据对应的所在分区
SELECT *, $PARTITION.TestSPFunction(createon) FROM marcus
