November 11, 2015 at 12:54 pm
I found this article and it was very helpful.
Partitioning Example
I'm not using Staging Tables in this case.
Users will be entering data directly into the partitioned Table.
I should the code be altered to excluded the Staging swap_in Table?
I'm not clear on the swap out table either.
Is it needed?
Is it just a temporary table to do the switch?
Please advise.
Thank you
By Michelle Ufford | November 3, 2008 | T-SQL Scripts
The following code will walk you through the process of creating a partitioned table:
--------------------------------
-- Create A Partitioned Table --
--------------------------------
/* Create a partition function. */
Create Partition Function [test_monthlyDateRange_pf] (smalldatetime)
As Range Right For Values
('2008-01-01', '2008-02-01', '2008-03-01');
Go
/* Associate the partition function with a partition scheme. */
Create Partition Scheme test_monthlyDateRange_ps
As Partition test_monthlyDateRange_pf
All To ([Primary]);
Go
/* Create your first partitioned table!
Make sure the data types match. */
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;
Using the previous code as a building block, let’s try swapping partitions:
--------------------------
-- Swap Out A Partition --
--------------------------
/* 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;
/* Create the table to hold the data you're swapping out.
The table structures must match identically; however,
DO NOT partition this table. */
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. */
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');
Go
/* Swap out the old partition. */
Alter Table dbo.orders
Switch Partition 3 To dbo.orders_stage_swapOut;
Go
/* Swap in the new partition. */
Alter Table dbo.orders_stage_swapIn
Switch To dbo.orders Partition 3;
Go
---------------------
-- Check your data --
---------------------
/* 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;
/* Clean-up time!
Drop Table dbo.orders
Drop Table dbo.orders_stage_swapOut
Drop Table dbo.orders_stage_swapIn
Drop Partition Scheme test_monthlyDateRange_ps
Drop Partition Function [test_monthlyDateRange_pf]
*/
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 11, 2015 at 1:42 pm
I am a lot more clear after reading the following articles:
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/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply