2

I partitioned an existing table by year. After inserting a new record for a date in 2020, the new record shows as part of the 2020 partition and in the primary filegroup. I'm having a hard time finding if that's what is supposed to happen, or if I have something configured incorrectly.

This is what the partitions look like after inserting the record. Before inserting the record, this query showed 3 records in the primary filegroup, and 3 records in the 2021 group.

enter image description here

I was expecting a new row in the 2020 partition and for the number of rows in the primary filegroup to remain at 3, but perhaps those are wrong expectations.

Does the primary filegroup always show the total of the partitions, or should rows only show up as being in just the partition in which they belong?

I can show how I set this all up, and I'd be happy to, but this question is more about what the results should look like. I don't want the same row in multiple partitions.

Edit

I just found this link. It shows the results of inserting data where new rows only show up in the correct partition and not also in primary. So it appears I've done something incorrectly.

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

Edit 2

The reason primary has all 4 rows may be that its upper boundary is null. Since primary already existed on the table, that was never modified. Perhaps I need to modify it?

Edit 3

Interesting that this link also shows the sum of the partitions within the primary.

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

Script that SSMS generated for partitioning

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

Edit 4

Here is the same query, but with IndexName and IndexID included.

enter image description here

Edit 5

Things look better now after doing this:

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')

As seen here, the boundaries look good, I'm using RANGE RIGHT, and the rows seem to show in the correct partition.

enter image description here

The only question I have now is if it's bad to have no clustered index?

8
  • I suspect you want a RANGE RIGHT function instead of LEFT. Add DDL for your partition function and scheme to your question (as code, not images).
    – Dan Guzman
    Mar 17 at 14:02
  • I did this through SSMS, but I added the script that it generated. It would be nice if it's just a range left vs range right thing.
    – Bob Horn
    Mar 17 at 14:06
  • Why not make (EmployeeID, PunchTime) the clustered primary key, and remove the ID column? Mar 17 at 19:09
  • An employee can have two punches with the same time. I could look into changing that, but that's currently the case.
    – Bob Horn
    Mar 17 at 19:19
  • This was an excellent read: dbafromthecold.com/2018/02/21/indexing-and-partitioning. Two important notes from it: (1) SQL needs the partitioning key to be explicitly defined in all unique indexes on partitioned tables. This is so that SQL can determine the uniqueness of that index by checking one partition. And (2) create a unique clustered index on your partitioning key (with something like an identity integer column if the key isn’t unique by itself).
    – Bob Horn
    Mar 17 at 19:30
7

You have an index and a heap (the unindexed table) as shown by indexid (3 & 0 respectively) in edit4 so of course the row shows up twice. The index called PK_Punch does not seem to be partition aligned (as it is NULL in PartitionScheme) - which may or may not be a problem.

4
  • Ok, so I just set this up incorrectly in SSMS. If I try this again, what should the end result be? A clustered index that is part of the partition scheme, plus partitions for each year? Should the clustered index contain all of the rows, and each partition has rows by year?
    – Bob Horn
    Mar 17 at 17:37
  • And perhaps I should drop the table and recreate it with the partition scheme: ON PunchPS1?
    – Bob Horn
    Mar 17 at 17:38
  • Yes "normally" people use a clustered index with the partition scheme and in this case I would expect 1 partition per year - as the clustered index will be partitioned the rows will be divided between the partitions. If you add a non-clustered index you can either partition align it ("normal") in which case it will also store the rows into year partitions or not (in which case all the rows are together). Mar 17 at 17:41
  • Thank you for the help. I just added Edit 5 with a question at the very end, if you have time.
    – Bob Horn
    Mar 17 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.