隨記

Partitioned Table

查詢 Copilot

你可以使用以下SQL查询来查看现有数据库中定义的分区函数和分区方案：

查询分区函数：

SELECT name AS PartitionFunctionName
FROM sys.partition_functions;

查询分区方案：

SELECT name AS PartitionSchemeName
FROM sys.partition_schemes;

查询分区函数的详细信息：

SELECT 
    pf.name AS PartitionFunctionName,
    ps.name AS PartitionSchemeName,
    pfr.boundary_id,
    pfr.value AS BoundaryValue
FROM 
    sys.partition_functions AS pf
    JOIN sys.partition_range_values AS pfr ON pf.function_id = pfr.function_id
    JOIN sys.partition_schemes AS ps ON pf.function_id = ps.function_id
ORDER BY 
    pf.name, pfr.boundary_id;

查询使用分区的表：

SELECT 
    t.name AS TableName,
    i.name AS IndexName,
    ps.name AS PartitionSchemeName,
    pf.name AS PartitionFunctionName
FROM 
    sys.tables AS t
    JOIN sys.indexes AS i ON t.object_id = i.object_id
    JOIN sys.partition_schemes AS ps ON i.data_space_id = ps.data_space_id
    JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
WHERE 
    i.data_space_id IN (SELECT data_space_id FROM sys.partition_schemes);

查詢partialNumber

With PartitionNumber

SELECT 
    t.name AS TableName,
    i.name AS IndexName,
    ps.name AS PartitionSchemeName,
    pf.name AS PartitionFunctionName,
    p.partition_number AS PartitionNumber
FROM 
    sys.tables AS t
    JOIN sys.indexes AS i ON t.object_id = i.object_id
    JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id
    JOIN sys.partition_schemes AS ps ON i.data_space_id = ps.data_space_id
    JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
WHERE 
    i.data_space_id IN (SELECT data_space_id FROM sys.partition_schemes);

find count

SELECT 
    t.name AS TableName,
    i.name AS IndexName,
    p.partition_number AS PartitionNumber,
    ps.row_count AS wCount
FROM 
    sys.tables AS t
    JOIN sys.schemas AS s ON t.schema_id = s.schema_id and s.name = 'dbo'
    JOIN sys.indexes AS i ON t.object_id = i.object_id
    JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id
    JOIN sys.dm_db_partition_stats AS ps ON p.partition_id = ps.partition_id
WHERE 
    t.name = 'TestApiLog'

ORDER BY 
    p.partition_number;

find BoundaryValue

-- 1
   SELECT OBJECT_NAME(P.object_id) AS TableName,I.name AS IndexName,R.value AS BoundaryValue
,P.partition_number,S.name AS PartitionSchema,F.type_desc,P.row_count
FROM sys.dm_db_partition_stats AS P
JOIN sys.indexes AS I ON I.object_id = P.object_id AND I.index_id = P.index_id  
JOIN sys.partition_schemes AS S ON I.data_space_id = S.data_space_id 
JOIN sys.partition_functions AS F ON S.function_id = F.function_id
LEFT JOIN sys.partition_range_values AS R ON F.function_id = R.function_id AND R.boundary_id = P.partition_number
WHERE OBJECT_NAME(P.object_id) = 'TestApiLog'
ORDER BY TableName,IndexName,partition_number

-- 2
SELECT 
    s.name AS SchemaName,
    t.name AS TableName,
    i.name AS IndexName,
    p.partition_number AS PartitionNumber,
    ps.row_count AS newrowCount,
    prv.value AS BoundaryValue
FROM 
    sys.tables AS t
    JOIN sys.schemas AS s ON t.schema_id = s.schema_id and s.name = 'dbo'
    JOIN sys.indexes AS i ON t.object_id = i.object_id
    JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id
    JOIN sys.dm_db_partition_stats AS ps ON p.partition_id = ps.partition_id
    JOIN sys.partition_schemes AS pscheme ON i.data_space_id = pscheme.data_space_id
    JOIN sys.partition_functions AS pf ON pscheme.function_id = pf.function_id
    JOIN sys.partition_range_values AS prv ON pf.function_id = prv.function_id AND p.partition_number = prv.boundary_id + 1
WHERE 
    t.name = 'TestApiLog'
ORDER BY 
    p.partition_number;

創建 Copilot

表分区设置只会影响特定的表，而不会自动影响数据库内的所有表。每个表需要单独配置分区。以下是一些关键点：

分区函数和分区方案：这些是数据库级别的对象，但它们只会影响使用它们的表。

-- ex
CREATE PARTITION FUNCTION myPartitionFunction (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
 -- ex2
 CREATE PARTITION FUNCTION [partitionFunction](datetimeoffset(3)) AS RANGE RIGHT FOR VALUES 
 (N'06/01/2023 00:00:00 +00:00', N'07/01/2023 00:00:00 +00:00', N'08/01/2023 00:00:00 +00:00'
 , N'09/01/2023 00:00:00 +00:00', N'10/01/2023 00:00:00 +00:00', N'11/01/2023 00:00:00 +00:00', N'12/01/2023 00:00:00 +00:00'
 , N'01/01/2024 00:00:00 +00:00', N'02/01/2024 00:00:00 +00:00', N'03/01/2024 00:00:00 +00:00', N'04/01/2024 00:00:00 +00:00')

 -- SCHEME 1
 CREATE PARTITION SCHEME TestPartitionScheme
AS PARTITION TestPartitionFunction  ALL TO ( [PRIMARY] );

 -- SCHEME 2

CREATE PARTITION SCHEME myPartitionScheme
AS PARTITION myPartitionFunction
TO (filegroup1, filegroup2, filegroup3, filegroup4);

 Alter PARTITION FUNCTION  () SPLIT RANGE
 ( N'02/01/2024 00:00:00 +00:00')
 --    自動產生分區
 DECLARE @M_LAST DATETIMEOffSet(7)
 DECLARE @I_CNT INT = 0
 SELECT TOP 1 @M_LAST = CAST(v.value AS DATETIMEOffSet(7))
 FROM sys.partition_range_values AS V 
 LEFT JOIN sys.partition_functions AS F ON V.function_id = F.function_id 
 WHERE f.name = 'partitionFunction'
 ORDER BY value DESC

 WHILE @I_CNT < 30 --自動產生30天的分區
 BEGIN
     SET @M_LAST = DATEADD(M,1,@M_LAST)
     SET @I_CNT = @I_CNT + 1
     ALTER PARTITION SCHEME S_Day NEXT USED [Primary]        
     ALTER PARTITION FUNCTION partitionFunction() SPLIT RANGE(@M_LAST)                                
 END

分区表：在创建或修改表时，可以指定使用分区方案。这个设置只影响特定的表。

CREATE TABLE myPartitionedTable (
    id int,
    data_column varchar(255)
)
ON myPartitionScheme(id);

ALTER TABLE [dbo].[TestApiLog]
 ADD CONSTRAINT PK_TestApiLog PRIMARY KEY (ApiLogId ASC,CreatedDate asc) ON [Test2Scheme]([CreatedDate])

独立配置：每个表需要单独配置分区。如果你有多个表需要分区，每个表都需要指定分区方案。

因此，表分区设置不会自动应用到数据库内的所有表。你需要为每个需要分区的表单独配置分区方案。如果有更多问题或需要进一步的帮助，请随时告诉我！

刪除

Delete SQL TRUNCATE TABLE

TRUNCATE TABLE 資料表名稱 WITH(Partitions(sys.partitions的partition_number欄位))