November 24, 2015 at 11:36 am
I found this example code on the Net.
Unfortunately it bombs out on the last line of code which is the following code.
What is wrong with the code?
Does anyone have an example of partitioning on a table based on a datetime column?
Thanks.
Msg 4972, Level 16, State 1, Line 2
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'PrismData.dbo.orders_stage_swapIn' allows values that are not allowed by check constraints or partition function on target table 'PrismData.dbo.orders'.
);
/* Create your first partitioned table!
Make sure the data types match. */
-- TRUNCATE TABLE dbo.orders
Create Table dbo.orders
(
order_id int Identity(1,1) Not Null
, orderDate smalldatetime Not Null
Constraint PK_orders Primary Key Clustered
(
orderDate
, order_id
)
) On test_monthlyDateRange_ps(orderDate);
Go
/* Create some records to play with. */
Insert Into dbo.orders
Select '2007-12-31' Union All
Select '2008-01-02' Union All
Select '2008-01-03' Union All
Select '2008-01-04' Union All
Select '2008-02-01' Union All
Select '2008-02-02' Union All
Select '2008-03-01' Union All
Select '2008-03-02';
/* The $partition function can be used to interrogate partition data.
Let's use it to see where those records are physically located. */
Select $partition.test_monthlyDateRange_pf(orderDate)
As 'partition_number'
, *
From dbo.orders;
/* By default, all new indexes are created on the partition.
Let's create an aligned index */
Create NonClustered Index IX_orders_aligned
On dbo.orders(order_id)
On test_monthlyDateRange_ps(orderDate);
/* Now let's create an un-aligned index.
We'll need to specify the filegroup. */
Create NonClustered Index IX_orders_nonaligned
On dbo.orders(order_id)
On [Primary]; -- can be any filegroup
/* Review your indexes */
Execute sp_helpindex orders;
--------------------------
-- Swap Out A Partition --
--------------------------
---- SELECT *
--FROM ORDERS
/* We need to drop our un-aligned index; otherwise we'll
get an error when we attempt to do the switch. */
Drop Index IX_orders_nonaligned On dbo.orders;
-- SELECT * FROM dbo.orders_stage_swapOut
Create Table dbo.orders_stage_swapOut
(
order_id int Not Null
, orderDate smalldatetime Not Null
Constraint PK_orders_stage_swapOut Primary Key Clustered
(
orderDate
, order_id
)
) On [Primary];
Go
/* Create the table to hold the data you're swapping in.
The table structures must match identically; however,
DO NOT partition this table. */
-- SELECT * FROM dbo.orders_stage_swapIn
Create Table dbo.orders_stage_swapIn
(
order_id int Not Null
, orderDate smalldatetime Not Null
Constraint PK_orders_stage_swapIn Primary Key Clustered
(
orderDate
, order_id
)
) On [Primary];
Go
/* Populate the table you're swapping in. */
Insert Into dbo.orders_stage_swapIn
Select -5, '2008-02-02' Union All
Select -4, '2008-02-03' Union All
Select -3, '2008-02-04' Union All
Select -2, '2008-02-05' Union All
Select -1, '2008-02-06';
/* Create any indexes on your table to match the
indexes on your partitioned table. */
Create NonClustered Index IX_orders_stage_swapIn
On dbo.orders_stage_swapIn(order_id);
/* Add a check constraint for the
partition to be swapped in.
This step is required. */
Alter Table dbo.orders_stage_swapIn
With Check
Add Constraint orders_stage_swapIn_orderDateCK
Check (orderDate >= '2008-02-01'
And orderDate < '2008-03-01');
/* Swap out the old partition. */
Alter Table dbo.orders
Switch Partition 3 To dbo.orders_stage_swapOut;
Go
Alter Table dbo.orders_stage_swapIn
Switch To dbo.orders Partition 3;
Go
][/code]
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 24, 2015 at 11:44 am
Listed below is the entire script.
I suspect that it is flawed.
CREATE PARTITION FUNCTION Test_monthlyDateRange_pf (smalldatetime)
AS RANGE FOR VALUES
('2008-01-01', '2008-02-01','2008-03-01')
Create Partition Scheme test_monthlyDateRange_ps
As Partition test_monthlyDateRange_pf
All To ([Primary]);
/* Create your first partitioned table!
Make sure the data types match. */
-- TRUNCATE TABLE dbo.orders
Create Table dbo.orders
(
order_id int Identity(1,1) Not Null
, orderDate smalldatetime Not Null
Constraint PK_orders Primary Key Clustered
(
orderDate
, order_id
)
) On test_monthlyDateRange_ps(orderDate);
Go
/* Create some records to play with. */
Insert Into dbo.orders
Select '2007-12-31' Union All
Select '2008-01-02' Union All
Select '2008-01-03' Union All
Select '2008-01-04' Union All
Select '2008-02-01' Union All
Select '2008-02-02' Union All
Select '2008-03-01' Union All
Select '2008-03-02';
/* The $partition function can be used to interrogate partition data.
Let's use it to see where those records are physically located. */
Select $partition.test_monthlyDateRange_pf(orderDate)
As 'partition_number'
, *
From dbo.orders;
/* By default, all new indexes are created on the partition.
Let's create an aligned index */
Create NonClustered Index IX_orders_aligned
On dbo.orders(order_id)
On test_monthlyDateRange_ps(orderDate);
/* Now let's create an un-aligned index.
We'll need to specify the filegroup. */
Create NonClustered Index IX_orders_nonaligned
On dbo.orders(order_id)
On [Primary]; -- can be any filegroup
/* Review your indexes */
Execute sp_helpindex orders;
--------------------------
-- Swap Out A Partition --
--------------------------
---- SELECT *
--FROM ORDERS
/* We need to drop our un-aligned index; otherwise we'll
get an error when we attempt to do the switch. */
Drop Index IX_orders_nonaligned On dbo.orders;
-- SELECT * FROM dbo.orders_stage_swapOut
Create Table dbo.orders_stage_swapOut
(
order_id int Not Null
, orderDate smalldatetime Not Null
Constraint PK_orders_stage_swapOut Primary Key Clustered
(
orderDate
, order_id
)
) On [Primary];
Go
/* Create the table to hold the data you're swapping in.
The table structures must match identically; however,
DO NOT partition this table. */
-- SELECT * FROM dbo.orders_stage_swapIn
Create Table dbo.orders_stage_swapIn
(
order_id int Not Null
, orderDate smalldatetime Not Null
Constraint PK_orders_stage_swapIn Primary Key Clustered
(
orderDate
, order_id
)
) On [Primary];
Go
/* Populate the table you're swapping in. */
Insert Into dbo.orders_stage_swapIn
Select -5, '2008-02-02' Union All
Select -4, '2008-02-03' Union All
Select -3, '2008-02-04' Union All
Select -2, '2008-02-05' Union All
Select -1, '2008-02-06';
/* Create any indexes on your table to match the
indexes on your partitioned table. */
Create NonClustered Index IX_orders_stage_swapIn
On dbo.orders_stage_swapIn(order_id);
/* Add a check constraint for the
partition to be swapped in.
This step is required. */
Alter Table dbo.orders_stage_swapIn
With Check
Add Constraint orders_stage_swapIn_orderDateCK
Check (orderDate >= '2008-02-01'
And orderDate < '2008-03-01');
/* Swap out the old partition. */
Alter Table dbo.orders
Switch Partition 3 To dbo.orders_stage_swapOut;
Go
-- SELECT * FROM orders_stage_swapOut
-- TRUNCATE TABLE orders_stage_swapOut
--SELECT *
--FROM orders_stage_swapOut
Alter Table dbo.orders_stage_swapIn
Switch To dbo.orders Partition 3;
Go
/* You should have 2 records in here. */
Select * From dbo.orders_stage_swapOut;
/* You should have 5 records here. */
Select *
From dbo.orders
Where orderDate >= '2008-02-01'
And orderDate < '2008-03-01';
/* There should be no records in this table. */
Select * From dbo.orders_stage_swapIn;
Select $partition.test_monthlyDateRange_pf(orderDate)
As 'partition_number'
, *
From dbo.orders;
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 24, 2015 at 12:20 pm
The short answer: The constraint on the swap_in table is not correctly aligned with partition 3.
The long answer:
The constraint allows for a date equal to '2008-02-01' (really '2008-02-01 00:00:00'), while that belongs to partition 2 (it is the boundary value), not partition 3. If you're using RANGE LEFT, you're specifying the upper boundary for each partition (and that boundary value is included in the partition for which it is the upper boundary). Because of that, you'll want to specify the boundaries as the maximum allowed value for that data type for the desired partitions.
In most cases, I doubt you want midnight of February 1st to be included with January's data (and so on for the rest of the months), although since SMALLDATETIME rounds 23:59:59 to midnight the next day, you also might not care.
You could change the partition function so it matches what you expect (based on how the constraint is specified, that would mean defining the function so midnight of February 1st is included with the rest of February), or if you just want the code to run for an example in this particular case, then you could change the constraint to match the partition (get rid of the '=' in the '>=2008-02-01'; that will get it to work, but if you really wanted the constraint to match partition 3, then you'd add '=' to the less than comparison for March 1st as well).
Cheers!
November 24, 2015 at 12:22 pm
Generally, when partitioning on dates, RANGE RIGHT is easier to work with.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 24, 2015 at 12:25 pm
GilaMonster (11/24/2015)
Generally, when partitioning on dates, RANGE RIGHT is easier to work with.
+1
Should've thought to mention that 🙂
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply