sql server 分区命令

---------------------分区步骤---------------------------
//1、创建文件组
alter database demo add filegroup group1
alter database demo add filegroup group2
alter database demo add filegroup group3
alter database demo add filegroup group4
alter database demo add filegroup group5
alter database demo add filegroup group6

//2、创建文件
 alter database demo add file 
(name=N'Id1',filename=N'F:\Work\数据库\data\g1.ndf',size=5Mb,filegrowth=5mb)
to filegroup group1
alter database demo add file 
(name=N'Id2',filename=N'F:\Work\数据库\data\g2.ndf',size=5Mb,filegrowth=5mb)
to filegroup group2
alter database demo add file 
(name=N'Id3',filename=N'F:\Work\数据库\data\g3.ndf',size=5Mb,filegrowth=5mb)
to filegroup group3
alter database demo add file 
(name=N'Id4',filename=N'F:\Work\数据库\data\g4.ndf',size=5Mb,filegrowth=5mb)
to filegroup group4
alter database demo add file 
(name=N'Id5',filename=N'F:\Work\数据库\data\g5.ndf',size=5Mb,filegrowth=5mb)
to filegroup group5
alter database demo add file 
(name=N'Id6',filename=N'F:\Work\数据库\data\g6.ndf',size=5Mb,filegrowth=5mb)
to filegroup group6

//3、创建分区函数和分区模板
USE [demo]
GO
BEGIN TRANSACTION
//分区函数
CREATE PARTITION FUNCTION [fun60w](bigint) AS RANGE LEFT FOR VALUES (N'100000', N'200000', N'300000', N'400000', N'500000', N'600000')

//分区模板
CREATE PARTITION SCHEME [schema60w] AS PARTITION [fun60w] TO ([group1], [group2], [group3], [group4], [group5], [group6], [PRIMARY])

ALTER TABLE [dbo].[pack_code] DROP CONSTRAINT [PK__pack_cod__3213E83FD7DE9E30]

ALTER TABLE [dbo].[pack_code] ADD  CONSTRAINT [PK__pack_cod__3213E83FD7DE9E30] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [schema60w]([id])

COMMIT TRANSACTION

----------------sql server查询信息------------------------------
//查询分区函数
SELECT * FROM sys.partition_functions       --分区函数
SELECT * FROM sys.partition_range_values

//查询分区数量
SELECT
    CONVERT (VARCHAR(50), ps.name) AS partition_scheme,
    p.partition_number,
    CONVERT (VARCHAR(10), ds2.name) AS filegroup,
--     CONVERT (
--         VARCHAR (19),
--         isnull(v. VALUE, ''),
--         120
--     ) AS range_boundary,
    str(p. ROWS, 9) AS ROWS
FROM
    sys.indexes i
JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
JOIN sys.destination_data_spaces dds ON ps.data_space_id = dds.partition_scheme_id
JOIN sys.data_spaces ds2 ON dds.data_space_id = ds2.data_space_id
JOIN sys.partitions p ON dds.destination_id = p.partition_number
AND p.object_id = i.object_id
AND p.index_id = i.index_id
JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
-- LEFT JOIN sys.Partition_Range_values v ON pf.function_id = v.function_id
-- AND v.boundary_id = p.partition_number - pf.boundary_value_on_right
WHERE
    i.object_id = object_id('pack_code') --分区表名
AND i.index_id IN (0, 1)
ORDER BY
    p.partition_number

引用，可直接访问原帖

SQL Server表分区_sql server 表分区_IDonng、的博客-CSDN博客

SQL Server表分区删除详情(分区删除)