This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a full demonstration of the concept of Partitioned view in SQL Server.
Using Partitioned view, you can implement Table Partitioning in the Non-Enterprise Edition of SQL Server because Table Partition is the feature of SQL Server Enterprise Edition.
Many small level companies are searching for this kind of solution, and the concept of Partitioned view is also giving a good performance.
We can also say that this is a manual table partitioning where you must create separate physical tables basis on your require partition ranges and require to apply proper CHECK constraint for validating data ranges.
After this, using one view, you can access all table and accordingly query execution plan also access only require table which reduces the unnecessary load on all or whole table.
Please check the below demonstration and try it your self.
Create a SEQUENCE object:
|
1 2 3 4 |
CREATE SEQUENCE dbo.seq_ProductHistory START WITH 1 INCREMENT BY 1 ; GO |
Create four physical tables:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
CREATE TABLE dbo.tbl_ProductHistory_Q1 ( ProductID INT ,ProductName VARCHAR(20) ,SoldDate DATE ,ProductSoldQuarter TINYINT CHECK (ProductSoldQuarter = 1) ,CONSTRAINT pk_tbl_ProductHistory_Q1_ProductID_ProductSoldQuarter PRIMARY KEY(ProductID, ProductSoldQuarter) ) GO CREATE TABLE dbo.tbl_ProductHistory_Q2 ( ProductID INT ,ProductName VARCHAR(20) ,SoldDate DATE ,ProductSoldQuarter TINYINT CHECK (ProductSoldQuarter = 2) ,CONSTRAINT pk_tbl_ProductHistory_Q2_ProductID_ProductSoldQuarter PRIMARY KEY(ProductID, ProductSoldQuarter) ) GO CREATE TABLE dbo.tbl_ProductHistory_Q3 ( ProductID INT ,ProductName VARCHAR(20) ,SoldDate DATE ,ProductSoldQuarter TINYINT CHECK (ProductSoldQuarter = 3) ,CONSTRAINT pk_tbl_ProductHistory_Q3_ProductID_ProductSoldQuarter PRIMARY KEY(ProductID, ProductSoldQuarter) ) GO CREATE TABLE dbo.tbl_ProductHistory_Q4 ( ProductID INT ,ProductName VARCHAR(20) ,SoldDate DATE ,ProductSoldQuarter TINYINT CHECK (ProductSoldQuarter = 4) ,CONSTRAINT pk_tbl_ProductHistory_Q4_ProductID_ProductSoldQuarter PRIMARY KEY(ProductID, ProductSoldQuarter) ) GO |
Create a Partitioned View:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE VIEW dbo.vw_ProductHistory WITH SCHEMABINDING AS SELECT ProductID, ProductName, SoldDate, ProductSoldQuarter FROM dbo.tbl_ProductHistory_Q1 UNION ALL SELECT ProductID, ProductName, SoldDate, ProductSoldQuarter FROM dbo.tbl_ProductHistory_Q2 UNION ALL SELECT ProductID, ProductName, SoldDate, ProductSoldQuarter FROM dbo.tbl_ProductHistory_Q3 UNION ALL SELECT ProductID, ProductName, SoldDate, ProductSoldQuarter FROM dbo.tbl_ProductHistory_Q4 GO |
Use view and Insert data into four tables:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
INSERT INTO dbo.vw_ProductHistory VALUES (NEXT VALUE FOR dbo.seq_ProductHistory, 'Computer', '2017-01-08', 1) GO INSERT INTO dbo.vw_ProductHistory VALUES (NEXT VALUE FOR dbo.seq_ProductHistory, 'Laptop', '2017-04-14', 2) GO INSERT INTO dbo.vw_ProductHistory VALUES (NEXT VALUE FOR dbo.seq_ProductHistory, 'Pendrive', '2017-08-16', 3) GO INSERT INTO dbo.vw_ProductHistory VALUES (NEXT VALUE FOR dbo.seq_ProductHistory, 'Mobile', '2017-12-26', 4) GO |
SELECT all records from all four tables via view:
Check the execution plan, where you can see four different access on physical tables.
|
1 |
SELECT *FROM dbo.vw_ProductHistory |
Result:
|
1 2 3 4 5 6 |
ProductID ProductName SoldDate ProductSoldQuarter ----------- -------------------- ---------- ------------------ 1 Computer 2017-01-08 1 2 Laptop 2017-04-14 2 3 Pendrive 2017-08-16 3 4 Mobile 2017-12-26 4 |
Check the execution plan: It is accessing all four physical tables
SELECT few records:
Check the execution plan, where you can find only access of Quarter-4 table because we are selecting data only for it.
|
1 2 |
SELECT *FROM dbo.vw_ProductHistory WHERE ProductSoldQuarter = 4 |
Result:
|
1 2 3 |
ProductID ProductName SoldDate ProductSoldQuarter ----------- -------------------- ---------- ------------------ 4 Mobile 2017-12-26 4 |
Check the execution plan: It is accessing only Quarter-4 table.
