為什麼新插入的行會出現在 SQL Server 分區表的兩個文件組中？

我按年份對現有表進行了分區。在為 2020 年的某個日期插入新記錄後，新記錄將顯示為 2020 年分區和主文件組的一部分。我很難確定這是否應該發生，或者我的某些配置是否不正確。

這是插入記錄後分區的樣子。在插入記錄之前，此查詢顯示主文件組中有 3 條記錄，2021 組中有 3 條記錄。

我期待 2020 分區中的新行和主文件組中的行數保持在 3，但也許這些是錯誤的期望。

主文件組是否總是顯示分區的總數，或者行是否應該只顯示在它們所屬的分區中？

我可以展示我是如何設置這一切的，我很樂意這樣做，但這個問題更多的是關於結果應該是什麼樣子。我不希望多個分區中的同一行。

編輯

我剛找到這個連結。它顯示了插入數據的結果，其中新行僅顯示在正確的分區中，而主分區中也不顯示。所以看來我做錯了什麼。

https://www.sqlshack.com/how-to-automate-table-partitioning-in-sql-server/

編輯 2

primary 有所有 4 行的原因可能是它的上邊界為空。由於主要已經存在於表中，因此從未修改過。也許我需要修改它？

編輯 3

有趣的是，這個連結還顯示了主分區的總和。

https://www.mssqltips.com/sqlservertip/2888/how-to-partition-an-existing-sql-server-table/

SSMS 為分​​區生成的腳本

USE [Sandbox]
GO
BEGIN TRANSACTION
CREATE PARTITION FUNCTION [PunchPF1](datetimeoffset(7)) AS RANGE LEFT FOR VALUES (N'2020-01-01T00:00:00-05:00', N'2021-01-01T00:00:00-05:00', N'2022-01-01T00:00:00-05:00')
 
CREATE PARTITION SCHEME [PunchPS1] AS PARTITION [PunchPF1] TO ([Pre2020_Punch], [2020_Punch], [2021_Punch], [2022_Punch])
 
ALTER TABLE [time].[Punch] DROP CONSTRAINT [PK_Punch] WITH ( ONLINE = OFF )
 
ALTER TABLE [time].[Punch] ADD  CONSTRAINT [PK_Punch] PRIMARY KEY NONCLUSTERED 
(
   [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 [PRIMARY]
 
CREATE CLUSTERED INDEX [ClusteredIndex_on_PunchPS1_637515095430656187] ON [time].[Punch]
(
   [PunchTime]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PunchPS1]([PunchTime])
 
DROP INDEX [ClusteredIndex_on_PunchPS1_637515095430656187] ON [time].[Punch]
 
COMMIT TRANSACTION

編輯 4

這是相同的查詢，但包含 IndexName 和 IndexID。

編輯 5

這樣做之後，現在情況看起來好多了：

USE [Sandbox]

--------------------------------------------------------------------------------------------------------------------
-- Drop table, scheme, and function
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'Punch' AND TABLE_SCHEMA = 'time')
   DROP TABLE time.Punch;

IF EXISTS (SELECT 1 FROM sys.partition_schemes WHERE name = 'PunchPS1')
   DROP PARTITION SCHEME PunchPS1

IF EXISTS (SELECT 1 FROM sys.partition_functions WHERE name = 'PunchPF1')
   DROP partition function PunchPF1

--------------------------------------------------------------------------------------------------------------------
-- Files and Filegroups
-- Add files and filegroups manually in SSMS because each SQL Server could have different file locations?
-- How to add a file and filegroup using SQL:
-- https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-file-and-filegroup-options?view=sql-server-ver15
-- To see an example of creating filegroups in SQL, script the Sandbox DB AS CREATE...

--------------------------------------------------------------------------------------------------------------------
-- Partition Function
CREATE PARTITION FUNCTION [PunchPF1](datetimeoffset(7))
AS RANGE RIGHT FOR VALUES (N'2020-01-01T00:00:00-05:00', N'2021-01-01T00:00:00-05:00', N'2022-01-01T00:00:00-05:00')

-------------------------------------------------------------------------------------------------------------------- 
-- Partition Scheme
CREATE PARTITION SCHEME [PunchPS1]
AS PARTITION [PunchPF1] TO ([Pre2020_Punch], [2020_Punch], [2021_Punch], [2022_Punch])

--------------------------------------------------------------------------------------------------------------------
-- Table
CREATE TABLE [time].[Punch](
   [Id] [bigint] IDENTITY(1,1) NOT NULL,
   [EmployeeId] [bigint] NOT NULL,
   [PunchTime] [datetimeoffset](7) NOT NULL
) ON [PunchPS1] (PunchTime)

--------------------------------------------------------------------------------------------------------------------
-- Add some data
INSERT INTO [time].[Punch] ([EmployeeId],[PunchTime]) VALUES (10,'2020-12-18 16:40:20')
INSERT INTO [time].[Punch] ([EmployeeId],[PunchTime]) VALUES (10,'2020-12-20 16:40:20')
INSERT INTO [time].[Punch] ([EmployeeId],[PunchTime]) VALUES (10,'2020-12-22 16:40:20')
INSERT INTO [time].[Punch] ([EmployeeId],[PunchTime]) VALUES (10,'2021-3-18 16:40:20')

如此處所見，邊界看起來不錯，我使用的是 RANGE RIGHT，並且行似乎顯示在正確的分區中。

我現在唯一的問題是沒有聚集索引是否不好？

您有一個索引和一個堆（未索引的表），如 edit4 中的 indexid （分別為 3 和 0 ）所示，因此該行當然會顯示兩次。名為 PK_Punch 的索引似乎不是分區對齊的（因為它在 PartitionScheme 中為 NULL）——這可能是也可能不是問題。

引用自：https://dba.stackexchange.com/questions/287186