August 7, 2022 at 3:17 pm
I have 1 table which has a partition on Date column. However this table has a unique constraints on it's ID. Here's how the table creation script looks like.
CREATE TABLE [TestDB].[dbo].[tblDS2](
[GSN_ID] [varchar](50) NOT NULL,
[DS2_Lot_ID] [varchar](50) NULL,
[DS2_SubLot_ID] [varchar](50) NULL,
[DS2_X] [smallint] NULL,
[DS2_Y] [smallint] NULL,
[DS2_Bin] [varchar](10) NULL,
[DS2_Date] [smalldatetime] NULL,
[GsnBeforeFix] [varchar](50) NULL,
[Die_ID] [bigint] NULL,
[FileID] [int] NULL,
[TNR_Date] [smalldatetime] NULL,
CONSTRAINT [PK_Arc_tblDS2] PRIMARY KEY NONCLUSTERED
(
[GSN_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)ON [partition_01] ([TNR_Date])
So now when I want to truncate a partition from a certain date I'm getting the below error
TRUNCATE TABLE statement failed. Index 'PK_Arc_tblDS2' is not partitioned, but table 'tblDS2' uses partition function 'partition_01'. Index and table must use an equivalent partition function.
I understand this is due to the index and table are not aligned. Thus the proper way is to aligned this unique constraints with the partition function. So I drop the contraints and recreate using this script.
ALTER TABLE [dbo].[tblDS2] ADD CONSTRAINT [PK_Arc_tblDS2] PRIMARY KEY NONCLUSTERED
(
[GSN_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, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [partition_01] ([TNR_Date])
GO
However I'm got the below error msg instead.
Column 'TNR_Date' is partitioning column of the index 'PK_Arc_tblDS2'. Partition columns for a unique index must be a subset of the index key.
Some googling around mention that I should include TNR_Date column into the constraint, so I tried but failed as well.
ALTER TABLE [dbo].[tblDS2] ADD CONSTRAINT [PK_Arc_tblDS2] PRIMARY KEY NONCLUSTERED
(
[GSN_ID] ASC
,[TNR_Date] 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, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [partition_01] ([TNR_Date])
GO
>Cannot define PRIMARY KEY constraint on nullable column in table 'tblDS2'.<<
Since TNR_Date is a nullable column I understand why I'm getting this error. Is there any other way to go around other than altering that column to NOT NULL?
August 7, 2022 at 4:42 pm
instead of a Primary Key you can define it as a Unique Index.
one of your issues is how you ensure that GSN_ID remains unique throughout the table. - a update/insert trigger may help on this.
what is the size of that table (number of rows) that justifies it being partitioned?
August 8, 2022 at 2:53 am
instead of a Primary Key you can define it as a Unique Index.
one of your issues is how you ensure that GSN_ID remains unique throughout the table. - a update/insert trigger may help on this.
what is the size of that table (number of rows) that justifies it being partitioned?
May I know will there be any impact should there's no primary key define on the table and unique index is use instead?
By trigger are you referring to creating a trigger to run checks on GSN_ID everytime an insert happen so there won't be any duplicated entry?
As of current the table has around 400m rows, it is mainly use as an archive table where data from the main table is move into archive via an insert job.
August 8, 2022 at 6:46 am
as it would be a unique index I don't believe there would be negative impact on that table (at least not significant).
for the trigger - yes the idea is that it would prevent duplicate GSN_ID being entered - but as on your case this comes from another table and assuming that same uniqueness is implemented there then it would not be an issue on this archive table and you would not need such a trigger.
only question now - you stated "mainly use as an archive table" - are there any updates of this table or only inserts/deletes? and if there are updates does either GSN_ID or TNR_DATE get updated?
and... you didn't gave us the clustered index ddl - is that just on the TNR_DATE?
Depending on how you access this table it may be possible to have just the clustered index - on (TNR_DATE, GSN_ID) - but this really depends on how its queried.
August 8, 2022 at 2:05 pm
Create a unique clustered index on ( TNR_DATE, GSN_ID ). You can still have a nonclustered PK on GSN_ID alone.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 8, 2022 at 2:42 pm
If you can't solve the problem with indexes or by using a partitioned heap, you can always switch the partition to an empty table and then truncate it. The table needs to be on the same partition scheme with the same indexes, but I believe you can truncate the entire table regardless of the index alignment. This was how did truncelete before truncate partition was supported.
August 8, 2022 at 4:43 pm
ScottPletcher wrote:Create a unique clustered index on ( TNR_DATE, GSN_ID ). You can still have a nonclustered PK on GSN_ID alone.
and how will the OP create a non clustered PK partition aligned without adding the TNR_DATE to it?
I have to admit I haven't actually tried it, but since the clustered index contains TNR_DATE, don't all nonclustered automatically have TNR_DATE in them, since SQL will add it even if you don't? Of course it wouldn't be a key in the index, which might cause problems and indeed force you to add it to the keys, rather unfortunately.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 8, 2022 at 9:30 pm
@Ed B - in order to truncate a partitioned table , the table and indexes must be aligned (partitioned on the same partition function).- see https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-ver15
@scottpletcher - while for a non unique index we don't need to specify the partition column, for UNIQUE (PK or Index) OR Clustered indexes, the column must be specifically specified on the index columns - see https://docs.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-ver15
August 9, 2022 at 1:28 am
@Ed B - in order to truncate a partitioned table , the table and indexes must be aligned (partitioned on the same partition function).- see https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-ver15
I tested it before posting. The link does say "To truncate a partitioned table, the table and indexes must be aligned (partitioned on the same partition function).", but it is under the WITH PARTITIONS section, not in the general RESTRICTIONS section. They should probably update it though as it is not correct.
This is the test, it uses an existing partition function that based on an integer YYYYMMDD column. Truncate table with partition fails, Truncate table succeeds. If you needed to you could always drop the index on the side table, which would be undesirable as it's a clustered index, that's why I tested it first.
DROP TABLE IF EXISTS [dbo].[tblDS2]
GO
CREATE TABLE [dbo].[tblDS2](
[GSN_ID] [varchar](50) NOT NULL,
[DS2_Lot_ID] [varchar](50) NULL,
[DS2_SubLot_ID] [varchar](50) NULL,
[DS2_X] [smallint] NULL,
[DS2_Y] [smallint] NULL,
[DS2_Bin] [varchar](10) NULL,
[DS2_Date] [smalldatetime] NULL,
[GsnBeforeFix] [varchar](50) NULL,
[Die_ID] [bigint] NULL,
[FileID] [int] NULL,
[TNR_Date] INT NULL,
CONSTRAINT [PK_Arc_tblDS2] PRIMARY KEY NONCLUSTERED
(
[GSN_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)ON PartitionSchemeFacts ([TNR_Date])
INSERT dbo.tblDS2 VALUES
('ABC', 'DEF', 'HIJ',1,2,'A', '2021-01-01', 'A', 12345, 1, 20210101),
('DEF', 'DEF', 'HIJ',1,2,'A', '2021-01-01', 'A', 12345, 1, 20211231),
('GHI', 'DEF', 'HIJ',1,2,'A', '2021-01-01', 'A', 12345, 1, 20220101),
('JKL', 'DEF', 'HIJ',1,2,'A', '2021-01-01', 'A', 12345, 1, 20100101),
('MNO', 'DEF', 'HIJ',1,2,'A', '2021-01-01', 'A', 12345, 1, 20230101),
('PQR', 'DEF', 'HIJ',1,2,'A', '2021-01-01', 'A', 12345, 1, NULL)
-- All partitions
TRUNCATE TABLE [dbo].[tblDS2] WITH (PARTITIONS (1 TO 147))
GO
-- All partitions
TRUNCATE TABLE [dbo].[tblDS2] WITH (PARTITIONS (1 ))
/*
TRUNCATE TABLE statement failed. Index 'PK_Arc_tblDS2' is not partitioned,
but table 'tblDS2' uses partition function 'PartitionFunctionFacts'.
Index and table must use an equivalent partition function.
*/
-- First partition
TRUNCATE TABLE [dbo].[tblDS2]
/*
Commands completed successfully.
*/
GO
DROP TABLE IF EXISTS [dbo].[tblDS2]
August 15, 2022 at 6:36 am
Hi All,
Thank you so much for your replies. Just to add on, does Microsoft mention anything about having a primary constraint and table partition together? As far as I know primary constraints is to maintain the column uniqueness it's all good as long as the partition key itself is having the same column as the primary key. If the partition key is not the same, then based on the logic the constraint should include the partition key as well to ensure it's aligned to the table. However if my partition key is a date column so we won't be getting uniqueness any more right?
Sorry if what I'm saying doesn't make sense, I'm still trying to understand how partitioning works, the current partitioning is done by another team member who has left the organization so I can't really give an answer on why they're using table partitioning.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply