In this post I would like to show you how to create simple local horizontal partitioned view. The big advantage of this technique is to split huge table into smaller parts you can work with. There must be fulfilled some prerequisites you can find here on MSDN in detail. In this post we will try just to access data from created partitioned view. Here are important prerequisites, must be fulfilled for creating portioned view in short summary. More detail you can find in MSDN https://bit.ly/2WTrQmO in view sectio..
-
Tables in partitioned view are joined with UNION ALL operator
-
The same table cannot be repeated multiple times
-
Columns of each table has to be in same ordinal position
-
Partitioned column has to be part of primary key
-
Partitioned column has to be constrained based on partitioning conditions (for example date period)
-
All Columns of table (partition) has to be obtained in select of partitioned view
Script to create 3 tables partitions with booking_date partition column.
CREATE TABLE [dbo].[Partition__2019_10_01__2019_11_01] (id INT NOT NULL , booking_date DATE NOT NULL, data SYSNAME) ALTER TABLE [dbo].[Partition__2019_10_01__2019_11_01] WITH CHECK ADD CONSTRAINT [CH__Partition1] CHECK (([booking_date]>='2019-10-01' AND [booking_date]<'2019-11-01')) ALTER TABLE [dbo].[Partition__2019_10_01__2019_11_01] ADD PRIMARY KEY CLUSTERED ( [id] ASC, [booking_date] ASC) GO CREATE TABLE [dbo].[Partition__2019_11_01__2019_12_01] (id INT NOT NULL, booking_date DATE NOT NULL,data SYSNAME) ALTER TABLE [dbo].[Partition__2019_11_01__2019_12_01] WITH CHECK ADD CONSTRAINT [CH__Partition2] CHECK (([booking_date]>='2019-11-01' AND [booking_date]<'2019-12-01')) ALTER TABLE [dbo].[Partition__2019_11_01__2019_12_01] ADD PRIMARY KEY CLUSTERED ( [id] ASC, [booking_date] ASC ) GO CREATE TABLE [dbo].[Partition__2019_12_01__2020_01_01](id INT NOT NULL, booking_date DATE NOT NULL, data SYSNAME) ALTER TABLE [dbo].[Partition__2019_12_01__2020_01_01] WITH CHECK ADD CONSTRAINT [CH__Partition3] CHECK (([booking_date]>='2019-12-01' AND [booking_date]<'2020-01-01')) ALTER TABLE [dbo].[Partition__2019_12_01__2020_01_01] ADD PRIMARY KEY CLUSTERED ( [id] ASC, [booking_date] ASC) GO CREATE VIEW [dbo].[PartitionedView] AS SELECT * FROM [dbo].[Partition__2019_10_01__2019_11_01] UNION ALL SELECT * FROM [dbo].[Partition__2019_11_01__2019_12_01] UNION ALL SELECT * FROM [dbo].[Partition__2019_12_01__2020_01_01] GO
Let’s put some data to all these partitions. Since your partitioned view is designed for data modification. I will mention prerequisites in next post.
INSERT INTO [dbo].[PartitionedView] SELECT 1 ,'2019-11-05','TestData1' INSERT INTO [dbo].[PartitionedView] SELECT 2 ,'2019-11-06','TestData2' INSERT INTO [dbo].[PartitionedView] SELECT 3 ,'2019-11-07','TestData2' INSERT INTO [dbo].[PartitionedView] SELECT 4 ,'2019-11-08','TestData2' INSERT INTO [dbo].[PartitionedView] SELECT 5 ,'2019-12-05','TestData3'
Here is execution plan if you select data from one month (partition). You can see that we touched only partition, the predicate corresponds to the check constraint of the selected partition. Be aware that it is important to have partition column in predicate of query to work only with requested partition.
SELECT * FROM [dbo].[PartitionedView] WHERE booking_date >= '2019-11-01' AND booking_date <'2019-12-01'
Let’s suppose that we would like to get concrete record based on id attribute in our case. Using following query, we get data using index seek but we touched each partitioning partitioned view. Because we didn’t use partition column, used in check constraint of partition.
SELECT * FROM [dbo].[PartitionedView] WHERE id = 4
To get requested record effectively let’s change above mentioned query to:
SELECT * FROM [dbo].[PartitionedView] WHERE booking_date >= '2019-11-01' AND booking_date <'2019-12-01' AND id=4
Next time I would like to share some other knowledges regarding partitioned views. STAY TUNED!
