March 18, 2010 at 9:49 pm
Hi
Question:
Is it possible to partition on a non primary key column and still switch partition?
Scenario:
I am stuck with a scenario where in i need to partition on a datetime column but is outside the primary key. The partition scheme i create does not go well with the filegroup for the pkey/table since it is 'outside'. I need to SWITCH the data to archive but it ignores the partitions and moves the data of ALL the partitions. It either tells the table is not partitioned or the index is not partitioned. No matter what it says it moves ALL the data out. Any suggestions to overcome this would be appreciated.
TIA
March 19, 2010 at 7:11 am
Hmm As far as I know it is not even possible to create a partitioned table with a partitioning column that is not included in the partitioning key.
If you run the following code:
create partition function pfYear(datetime)
as
range right for values ('20050101','20060101','20070101','20080101','20090101','20100101')
create partition scheme psYear
as
partition pfYear to ([primary],[primary],[primary],[primary],[primary],[primary],[primary],[primary])
CREATE TABLE [dbo].[RegPart](
[Registration_ID] [int] NOT NULL,
[FirstName] [varchar](80) NOT NULL,
[LastName] [varchar](80) NOT NULL,
[DateJoined] [datetime] NOT NULL,
[DateLeft] [datetime] NULL
,
CONSTRAINT [PK_RegPart] PRIMARY KEY
(
[LastName] ASC,
[FirstName] ASC,
registration_ID
)
) ON psyear(DateJoined)
You get an error like this:
Column 'DateJoined' is partitioning column of the index 'PK_RegPart'. Partition columns for a unique index must be a subset of the index key.
So, what are you really doing ?
Perhaps you could post some code ?
/SG
March 19, 2010 at 7:44 am
Stefan_G (3/19/2010)
Hmm As far as I know it is not even possible to create a partitioned table with a partitioning column that is not included in the partitioning key.
It sure is - if by 'partitioning key', you mean 'primary key'.
The restriction you are thinking of is that a partitioned UNIQUE index must include the partitioning column in its key.
UNIQUE indexes do not have to be partitioned, even if the table is.
If they are partitioned by the same function as the table, they are said to be aligned.
Using your example, the following results in a partitioned heap, and an unpartitioned primary key:
CREATE TABLE [dbo].[RegPart]
(
[Registration_ID] [int] NOT NULL,
[FirstName] [varchar](80) NOT NULL,
[LastName] [varchar](80) NOT NULL,
[DateJoined] [datetime] NOT NULL,
[DateLeft] [datetime] NULL,
) ON psyear(DateJoined);
ALTER TABLE dbo.RegPart
ADD CONSTRAINT [PK_RegPart] PRIMARY KEY NONCLUSTERED
( [LastName] ASC,
[FirstName] ASC,
registration_ID)
ON [PRIMARY];
It does not have to be a heap, of course.
I agree that some sample structures and data from 'gk' would help at this point.
Paul
March 19, 2010 at 8:18 am
gk-411903 (3/18/2010)
Is it possible to partition on a non primary key column and still switch partition?
In case you are unable to provide a test rig, please read the following, since the requirements are quite detailed and complex - too much for a forum post.
March 19, 2010 at 4:29 pm
[font="Courier New"]--XXXXXXXXXX Main table Creation --XXXXXXXXXXXXXX
USE [PRTNTST]
GO
DROP PARTITION SCHEME my_psch2
GO
DROP PARTITION FUNCTION my_pfn2
GO
CREATE PARTITION FUNCTION my_pfn2(datetime2)
AS RANGE RIGHT FOR VALUES
( '20100401' --Before Apr 2010
, '20100501' --May 2010
, '20100601' --Jun 2010
, '20100701' --Jul 2010
)
GO
Command(s) completed successfully.
CREATE PARTITION SCHEME my_psch2 AS PARTITION my_pfn2 ALL TO
(
[PRIMARY]
)
GO
Partition scheme 'my_psch2' has been created successfully.
'PRIMARY' is marked as the next used filegroup in partition scheme 'my_psch2'.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
DROP TABLE [dbo].[AGENT_DATA2]
GO
CREATE TABLE [dbo].[AGENT_DATA2](
[T_KEY] [bigint] NOT NULL,
[T_RANGE] [varchar](10) NOT NULL,
[D_KEY] [bigint] NOT NULL,
[REF_KEY] [bigint] NOT NULL,
[MED_REF_KEY] [bigint] NOT NULL,
[REF_COUNT] [bigint] NOT NULL,
[ROW_CREATED_TIME] [datetime2](7) NULL,
[LUD_TIME] [datetime2](7) NULL
) ON [PRIMARY]
GO
Command(s) completed successfully.
ALTER TABLE [dbo].[AGENT_DATA2] ADD
CONSTRAINT [PK_AGENT2] PRIMARY KEY NONCLUSTERED
(
[T_KEY] ASC,
[D_KEY] ASC,
[REF_KEY] ASC,
[MED_REF_KEY] ASC
)
ON [PRIMARY]
GO
Command(s) completed successfully.
SET ANSI_PADDING OFF
GO
--XXXXXXXXXX Arch table Creation --XXXXXXXXXXXXXX
USE [PRTNTST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
DROP TABLE [dbo].[AGENT_DATA2_ARCH]
GO
CREATE TABLE [dbo].[AGENT_DATA2_ARCH](
[T_KEY] [bigint] NOT NULL,
[T_RANGE] [varchar](10) NOT NULL,
[D_KEY] [bigint] NOT NULL,
[REF_KEY] [bigint] NOT NULL,
[MED_REF_KEY] [bigint] NOT NULL,
[REF_COUNT] [bigint] NOT NULL,
[ROW_CREATED_TIME] [datetime2](7) NULL,
[LUD_TIME] [datetime2](7) NULL
) ON [PRIMARY]
GO
Command(s) completed successfully.
ALTER TABLE [dbo].[AGENT_DATA2_ARCH] ADD
CONSTRAINT [PK_AGENT2_ARCH] PRIMARY KEY NONCLUSTERED
(
[T_KEY] ASC,
[D_KEY] ASC,
[REF_KEY] ASC,
[MED_REF_KEY] ASC
)
ON [PRIMARY]
GO
Command(s) completed successfully.
SET ANSI_PADDING OFF
GO
----XXXXXXXXXXXXX Populate Data ----XXXXXXXXXXXXXX
Run script to populate data in 3 partitions
USE PRTNTST
GO
SELECT $Partition.my_pfn2(ROW_CREATED_TIME) AS [Partition Number]
, COUNT(*) AS [Total Records]
FROM dbo.AGENT_DATA2
GROUP BY $Partition.my_pfn2(ROW_CREATED_TIME)
ORDER BY 1
GO
Partition Number Total Records
---------------- -------------
1 50000
2 50000
3 50000
(3 row(s) affected)
SELECT COUNT(*) FROM dbo.AGENT_DATA2_ARCH
GO
-----------
0
(1 row(s) affected)
--XXXXXXXXXXX Switch data ---XXXXXXXXXXXXXX
ALTER TABLE dbo.AGENT_DATA2 SWITCH PARTITION 1
TO dbo.AGENT_DATA2_ARCH PARTITION 1
GO
Warning: The specified partition 1 for the table 'PRTNTST.dbo.AGENT_DATA2'
was ignored in ALTER TABLE SWITCH statement because the table is not partitioned.
Warning: The specified partition 1 for the table 'PRTNTST.dbo.AGENT_DATA2_ARCH'
was ignored in ALTER TABLE SWITCH statement because the table is not partitioned.
SELECT $Partition.my_pfn2(ROW_CREATED_TIME) AS [Partition Number]
, COUNT(*) AS [Total Records]
FROM dbo.AGENT_DATA2
GROUP BY $Partition.my_pfn2(ROW_CREATED_TIME)
ORDER BY 1
GO
Partition Number Total Records
---------------- -------------
(0 row(s) affected)
SELECT $Partition.my_pfn2(ROW_CREATED_TIME) AS [Partition Number]
, COUNT(*) AS [Total Records]
FROM dbo.AGENT_DATA2_ARCH
GROUP BY $Partition.my_pfn2(ROW_CREATED_TIME)
ORDER BY 1
GO
Partition Number Total Records
---------------- -------------
1 50000
2 50000
3 50000
(3 row(s) affected)[/font]
March 19, 2010 at 6:27 pm
[font="Courier New"]I had to include the Partitioned column as part of the Primary Key the following changes to make the SWITCH work. I forgot to include the Partition Scheme as part of the Pkey in my earlier posting. So i suppose it is mandatory.
CREATE TABLE [dbo].[AGENT_DATA2](
[T_KEY] [bigint] NOT NULL,
[T_RANGE] [varchar](10) NOT NULL,
[D_KEY] [bigint] NOT NULL,
[REF_KEY] [bigint] NOT NULL,
[MED_REF_KEY] [bigint] NOT NULL,
[REF_COUNT] [bigint] NOT NULL,
[ROW_CREATED_TIME] [datetime2](7) NOT NULL,
[LUD_TIME] [datetime2](7) NULL
) ON my_psch2(ROW_CREATED_TIME)
GO[/font]
[font="Courier New"]Command(s) completed successfully.[/font]
[font="Courier New"]ALTER TABLE [dbo].[AGENT_DATA2] ADD
CONSTRAINT [PK_AGENT2] PRIMARY KEY CLUSTERED
(
[T_KEY] ASC
,[D_KEY] ASC
,[REF_KEY] ASC
,[MED_REF_KEY] ASC
,[ROW_CREATED_TIME] ASC
)
ON my_psch2(ROW_CREATED_TIME)
GO[/font]
I don't think there is any workaround for this. If anyone knows please let me know.
March 19, 2010 at 9:46 pm
Did you read the BOL links?
March 20, 2010 at 1:41 pm
I still dont really understand what the problem is.
In you first script you never created any partitioned tables at all. Of course the switch command did not work in that case since it only applies to partitioned tables.
In the second script you create a partitioned table with a clustered primary key. This table should work perfectly with the switch command.
If I understand you correctly you would like to avoid having ROW_CREATED_TIME in the primary key.
The only way to do this is to create an unpartitioned non-clustered primary key.
But if you do this you cannot use the switch command because it can only be used if the table and all indexes are partitioned the same way.
So, I believe the workaround is to include ROW_CREATED_TIME in the primary key just as in your second script.
/SG
March 20, 2010 at 2:04 pm
Now I have had to go actually run those scripts 😉
As written, both AGENT_DATA2 and AGENT_DATA2_ARCH are created as non-partitioned. Both are created on PRIMARY, and so are the PRIMARY KEYS.
Changing the table definitions to:
CREATE TABLE [dbo].[AGENT_DATA2](
[T_KEY] [bigint] NOT NULL,
[T_RANGE] [varchar](10) NOT NULL,
[D_KEY] [bigint] NOT NULL,
[REF_KEY] [bigint] NOT NULL,
[MED_REF_KEY] [bigint] NOT NULL,
[REF_COUNT] [bigint] NOT NULL,
[ROW_CREATED_TIME] [datetime2](7) NULL,
[LUD_TIME] [datetime2](7) NULL
) ON my_psch2([ROW_CREATED_TIME])
CREATE TABLE [dbo].[AGENT_DATA2_ARCH](
[T_KEY] [bigint] NOT NULL,
[T_RANGE] [varchar](10) NOT NULL,
[D_KEY] [bigint] NOT NULL,
[REF_KEY] [bigint] NOT NULL,
[MED_REF_KEY] [bigint] NOT NULL,
[REF_COUNT] [bigint] NOT NULL,
[ROW_CREATED_TIME] [datetime2](7) NULL,
[LUD_TIME] [datetime2](7) NULL
) ON my_psch2 ([ROW_CREATED_TIME])
...and leaving the PK definitions as they are, result in two partitioned heaps with an un-partitioned primary key each. The SWITCH statement fails with this error:
.Net SqlClient Data Provider: Msg 7733, Level 16, State 4, Line 1
'ALTER TABLE SWITCH' statement failed. The table 'tempdb.dbo.AGENT_DATA2' is partitioned while index 'PK_AGENT2' is not partitioned.
...since indexes must be aligned to do a SWITCH. Solution: Drop the PKs before the SWITCH and re-create them afterward.
The ROW_CREATED_TIME column cannot be a key for the PK since it is NULLable. If you can change this to NOT NULL, and are happy to include it as a key in the PK, then that would work too, as shown next:
DROP TABLE [dbo].[AGENT_DATA2]
GO
CREATE TABLE [dbo].[AGENT_DATA2](
[T_KEY] [bigint] NOT NULL,
[T_RANGE] [varchar](10) NOT NULL,
[D_KEY] [bigint] NOT NULL,
[REF_KEY] [bigint] NOT NULL,
[MED_REF_KEY] [bigint] NOT NULL,
[REF_COUNT] [bigint] NOT NULL,
[ROW_CREATED_TIME] [datetime2](7) NOT NULL,
[LUD_TIME] [datetime2](7) NULL
) ON my_psch2([ROW_CREATED_TIME])
GO
ALTER TABLE [dbo].[AGENT_DATA2] ADD
CONSTRAINT [PK_AGENT2] PRIMARY KEY NONCLUSTERED
(
[T_KEY] ASC,
[D_KEY] ASC,
[REF_KEY] ASC,
[MED_REF_KEY] ASC,
[ROW_CREATED_TIME] ASC
)
ON my_psch2([ROW_CREATED_TIME])
GO
DROP TABLE [dbo].[AGENT_DATA2_ARCH]
GO
CREATE TABLE [dbo].[AGENT_DATA2_ARCH](
[T_KEY] [bigint] NOT NULL,
[T_RANGE] [varchar](10) NOT NULL,
[D_KEY] [bigint] NOT NULL,
[REF_KEY] [bigint] NOT NULL,
[MED_REF_KEY] [bigint] NOT NULL,
[REF_COUNT] [bigint] NOT NULL,
[ROW_CREATED_TIME] [datetime2](7) NOT NULL,
[LUD_TIME] [datetime2](7) NULL
) ON my_psch2 ([ROW_CREATED_TIME])
GO
ALTER TABLE [dbo].[AGENT_DATA2_ARCH] ADD
CONSTRAINT [PK_AGENT2_ARCH] PRIMARY KEY NONCLUSTERED
(
[T_KEY] ASC,
[D_KEY] ASC,
[REF_KEY] ASC,
[MED_REF_KEY] ASC,
[ROW_CREATED_TIME] ASC
)
ON my_psch2 ([ROW_CREATED_TIME])
GO
ALTER TABLE dbo.AGENT_DATA2 SWITCH PARTITION 1
TO dbo.AGENT_DATA2_ARCH PARTITION 1
GO
Paul
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply