sqlserver 表分区-字段id分区案例

1，创建文件组

USE [master]
GO
ALTER DATABASE [test] ADD FILEGROUP [Group1]
GO
ALTER DATABASE [test] ADD FILEGROUP [Group2]
GO
ALTER DATABASE [test] ADD FILEGROUP [Group3]
GO
ALTER DATABASE [test] ADD FILEGROUP [Group4]
GO

USE [master]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N'datafile1', FILENAME = N'D:\SQLData\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test\datafile1.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [Group1]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N'datafile2', FILENAME = N'D:\SQLData\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test\datafile2.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [Group2]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N'datafile3', FILENAME = N'D:\SQLData\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test\datafile3.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [Group3]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N'datafile4', FILENAME = N'D:\SQLData\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test\datafile4.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [Group4]
GO

2,创建分区函数

USE [test]
GO
CREATE PARTITION FUNCTION [Pt_Range](BIGINT) AS RANGE RIGHT FOR VALUES (1000000, 2000000, 3000000)
GO

03,创建分区方案,分区方案对应的文件组数是分区函数指定的数量+1
CREATE PARTITION SCHEME Ps_Range
AS PARTITION Pt_Range
TO (Group1, Group2, Group3, Group4);

04,创建表,指定的分区列的数据类型一定要和分区函数指定的列类型一致。
CREATE TABLE [dbo].[News](
    [id] [bigint] NOT NULL,
    [status] [int] NULL,
 CONSTRAINT [PK_News] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Ps_Range](id)
) ON [Ps_Range](id)

5，插入测试数据
DECLARE @id INT 
SET @id=1
WHILE @id<5001000
BEGIN
   INSERT INTO News VALUES(@id,@id%2)
   SET @id=@id+1

END

6,查看表的分区
SELECT * FROM sys.partitions WHERE [object_id]=OBJECT_ID('news')

SELECT index_id,partition_number,avg_fragment_size_in_pages,page_count,alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats(Db_id(),Object_id('news'),NULL,NULL,NULL)