Partitioned views have been around since before SQL Server 2005 (whereupon partitioned tables were introduced) but this does not mean that they aren’t without their uses now.
What is a Partitioned View?
Where data has been partitioned horizontally (across several tables), a partitioned view can show a unified view of these tables and can also make any data selections only from the required table, without accessing all tables that the view comprises of. The tables can be in one database, several databases on the same server or on databases on linked servers.
Creating the Test Environment
For this, I have three databases:
TestDB and SecondTestDB, which are on the ‘main’ server.
ThirdTestdb which is on a linked server, named ‘ARCHIVEWAREHOUSE’.
Listing 1: Tables for ‘main’ server
USE TestDB;
GO
CREATE TABLE dbo.Warehouse1(
WarehouseID TINYINT NOT NULL,
PartCode VARCHAR(20) NOT NULL,
Quantity INT,
CONSTRAINT PK_Warehouse1 PRIMARY KEY CLUSTERED
(
WarehouseID ASC,
PartCode ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [Primary]
) ON [Primary];
GO
CREATE TABLE dbo.Warehouse2(
WarehouseID TINYINT NOT NULL,
PartCode VARCHAR(20) NOT NULL,
Quantity INT,
CONSTRAINT PK_Warehouse2 PRIMARY KEY CLUSTERED
(
WarehouseID ASC,
PartCode ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [Primary]
) ON [Primary];
GO
USE SecondTestDB;
GO
CREATE TABLE dbo.Warehouse1(
WarehouseID TINYINT NOT NULL,
PartCode VARCHAR(20) NOT NULL,
Quantity INT,
CONSTRAINT PK_Warehouse1 PRIMARY KEY CLUSTERED
(
WarehouseID ASC,
PartCode ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [Primary]
) ON [Primary];
GO
CREATE TABLE dbo.Warehouse2(
WarehouseID TINYINT NOT NULL,
PartCode VARCHAR(20) NOT NULL,
Quantity INT,
CONSTRAINT PK_Warehouse2 PRIMARY KEY CLUSTERED
(
WarehouseID ASC,
PartCode ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [Primary]
) ON [Primary];
GO
Listing 2: Tables for the linked server
USE ThirdTestDB;
GO
CREATE TABLE dbo.Warehouse1(
WarehouseID TINYINT NOT NULL,
PartCode VARCHAR(20) NOT NULL,
Quantity INT,
CONSTRAINT PK_Warehouse1 PRIMARY KEY CLUSTERED
(
WarehouseID ASC,
PartCode ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [Primary]
) ON [Primary];
GO
CREATE TABLE dbo.Warehouse2(
WarehouseID TINYINT NOT NULL,
PartCode VARCHAR(20) NOT NULL,
Quantity INT,
CONSTRAINT PK_Warehouse2 PRIMARY KEY CLUSTERED
(
WarehouseID ASC,
PartCode ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [Primary]
) ON [Primary];
GO
Listing 3: Create test data on ‘main’ server
USE TestDB;
GO
INSERT INTO dbo.Warehouse1
( WarehouseID,
PartCode,
Quantity )
VALUES ( 1, 'WIDG-001', 10),
( 1, 'WIDG-005', 21),
( 1, 'WIDG-007', 0),
( 1, 'WIDG-008', 6),
( 1, 'WIDG-009', 9);
INSERT INTO dbo.Warehouse2
( WarehouseID,
PartCode,
Quantity )
VALUES ( 2, 'WIDG-001', 104),
( 2, 'WIDG-015', 211),
( 2, 'WIDG-017', 90),
( 2, 'WIDG-048', 78),
( 2, 'WIDG-079', 7);
GO
USE SecondTestDB;
GO
INSERT INTO dbo.Warehouse1
( WarehouseID,
PartCode,
Quantity )
VALUES ( 3, 'WIDG-001', 5),
( 3, 'WIDG-006', 77),
( 3, 'WIDG-007', 67),
( 3, 'WIDG-008', 9),
( 3, 'WIDG-009', 24);
INSERT INTO dbo.Warehouse2
( WarehouseID,
PartCode,
Quantity )
VALUES ( 4, 'WIDG-003', 22),
( 4, 'WIDG-014', 85),
( 4, 'WIDG-017', 45),
( 4, 'WIDG-048', 6),
( 4, 'WIDG-079', 89);
GO
Listing 4: Create Test Data on Linked Server
USE ThirdTestDB;
GO
INSERT INTO dbo.Warehouse1
( WarehouseID,
PartCode,
Quantity )
VALUES ( 5, 'WIDG-122', 10),
( 5, 'WIDG-135', 21),
( 5, 'WIDG-155', 0),
( 5, 'WIDG-157', 6),
( 5, 'WIDG-177', 9);
INSERT INTO dbo.Warehouse2
( WarehouseID,
PartCode,
Quantity )
VALUES ( 6, 'WIDG-255', 104),
( 6, 'WIDG-257', 211),
( 6, 'WIDG-266', 90),
( 6, 'WIDG-298', 78),
( 6, 'WIDG-299', 7);
GO
Six tables spread across three databases, all with identical structure. The data is spread across these tables, with the value of ‘WarehouseID’ controlling which table the row is stored within. At this point there is no control to prevent a row being inserted into the ‘wrong’ table, but that will be developed as the example continues.
Listing 5: Create View on ‘main’ server
USE TestDB;
GO
CREATE VIEW StockLevels AS (
SELECT WarehouseID,
PartCode,
Quantity
FROM dbo.Warehouse1
UNION ALL
SELECT WarehouseID,
PartCode,
Quantity
FROM dbo.Warehouse2
UNION ALL
SELECT WarehouseID,
PartCode,
Quantity
FROM SecondTestDB.dbo.Warehouse1
UNION ALL
SELECT WarehouseID,
PartCode,
Quantity
FROM SecondTestDB.dbo.Warehouse2
UNION ALL
SELECT WarehouseID,
PartCode,
Quantity
FROM ARCHIVEWAREHOUSE.ThirdTestDB.dbo.Warehouse1
UNION ALL
SELECT WarehouseID,
PartCode,
Quantity
FROM ARCHIVEWAREHOUSE.ThirdTestDB.dbo.Warehouse2
);
GO
Using the view
Setting STATISTICS IO ON and showing the actual execution plan, shows that in its current structure the view will access all tables, regardless of whether or not a filter is applied.
Figure 1: SELECT results without a filter
Figure 2: Statistics and query plan without a filter
Figure 3: SELECT results with a filter
Figure 4: Statistics and query plan with a filter
With this configuration, this is just a normal view and makes no distinction between the various tables it is accessing. Figure 2 and Figure 4 show no difference in the tables accessed, with or without the filter being applied. At this stage this is not a partitioned view.
To change this to a partitioned view actually requires changes to the source tables.
Creating the partitioned view
With the data structure used here, the column ‘WarehouseID’ is being used to signify which table the various rows should be in. To ensure that this actually works a constraint needs to be applied to the column. Once the constraint is used, if the view is filtered using the column that has the constraint then it will only use the appropriate table, based upon the filter value.
Listing 6: Create the constraint on the ‘main’ server
USE TestDB;
GO
ALTER TABLE dbo.Warehouse1
ADD CONSTRAINT C_Warehouse1 CHECK (WarehouseID = 1);
GO
ALTER TABLE dbo.Warehouse2
ADD CONSTRAINT C_Warehouse2 CHECK (WarehouseID = 2);
GO
USE SecondTestDB;
GO
ALTER TABLE dbo.Warehouse1
ADD CONSTRAINT C_Warehouse1 CHECK (WarehouseID = 3);
GO
ALTER TABLE dbo.Warehouse2
ADD CONSTRAINT C_Warehouse2 CHECK (WarehouseID = 4);
GO
Listing 7: Create the constraint on the linked server
USE ThirdTestDB;
GO
ALTER TABLE dbo.Warehouse1
ADD CONSTRAINT C_Warehouse1 CHECK (WarehouseID = 5);
GO
ALTER TABLE dbo.Warehouse2
ADD CONSTRAINT C_Warehouse2 CHECK (WarehouseID = 6);
GO
Repeating the queries used earlier shows that the view focuses resources on the required table only.
Figure 5: Statistics and query plan with a filter and constraints created
The work required by the query has now been reduced substantially.
Of course, to some degree it must still be accessing the other table – to check the metadata if nothing else. This can easily be proved by taking ‘SecondTestDB’ offline and querying the view again.
Figure 6: Querying the view with a database offline
Even though no rows are required from the table in the database ‘SecondTestDB’ an error has been raised from the view, showing that tables it does not need to select from still needs to be available.
Adding a table with a different structure
Because this is a view, it is possible to add a table to this that does not have an identical structure. However, it must have the same column for the constraint, otherwise the view will access all of the tables that it has been constructed from, ignoring the constraint control created by Listings 6 and 7.
Listing 7: Adding a table with a different format to TestDB
USE [TestDB]
GO
CREATE TABLE [dbo].[Warehouse3](
[WarehouseName] VARCHAR(10) NOT NULL,
[WarehouseID] [tinyint] NOT NULL,
[ProductCode_1] [varchar](20) NOT NULL,
[ProductCode_2] [varchar](20) NOT NULL,
[Quantity_In] [int] NOT NULL,
[Quantity_Out] INT NOT NULL,
CONSTRAINT [PK_Warehouse3] PRIMARY KEY CLUSTERED
(
[WarehouseName] ASC,
[ProductCode_1] ASC,
[ProductCode_2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Warehouse3] WITH CHECK ADD CONSTRAINT [C_Warehouse3] CHECK (([WarehouseID]=(7))) ---WarehouseID has to be present for the partitiion functionality
GO
Listing 8: Alter View
USE TestDB;
GO
ALTER VIEW StockLevels AS (
SELECT WarehouseID,
PartCode,
Quantity
FROM dbo.Warehouse1
UNION ALL
SELECT WarehouseID,
PartCode,
Quantity
FROM dbo.Warehouse2
UNION ALL
SELECT WarehouseID,
PartCode,
Quantity
FROM SecondTestDB.dbo.Warehouse1
UNION ALL
SELECT WarehouseID,
PartCode,
Quantity
FROM SecondTestDB.dbo.Warehouse2
UNION ALL
SELECT WarehouseID,
PartCode,
Quantity
FROM ARCHIVEWAREHOUSE.ThirdTestDB.dbo.Warehouse1
UNION ALL
SELECT WarehouseID,
PartCode,
Quantity
FROM ARCHIVEWAREHOUSE.ThirdTestDB.dbo.Warehouse2
UNION ALL
SELECT [WarehouseID],
ltrim(RTRIM(ProductCode_1)) + ltrim(RTRIM(ProductCode_2)) AS [PartCode],
[Quantity_In] - [Quantity_Out] AS [Quantity]
FROM dbo.Warehouse3
);
GO
Listing 9: Add test data to ‘Warehouse3’ table
USE [TestDB]
GO
INSERT INTO [dbo].[Warehouse3]
([WarehouseName],[WarehouseID]
,[ProductCode_1]
,[ProductCode_2]
,[Quantity_In]
,[Quantity_Out])
VALUES
('Seven'
,7
,'ABCD'
,'EFGH'
,10
,3)
GO
With the addition of the ‘Warehouse3’ table, the partitioning behaviour still works, because the column ‘WarehouseID’ and the associated constraint are in the new table. The SELECT within the view has transformed the appearance of the data from that table to match the other tables.
Figure 7: View results with different structured source table
So, the tables don’t have to be identical, when selecting data – as long as the tables have the same constraint and can be structured within the view to match the other tables (because of the ‘UNION ALL’).
Updating data via the view
To simplify this exercise, the remote server will be removed from the view, as well as the table that has a different structure (as detailed in Listing 7).
Listing 10: Alter view to one server with identical table structures
USE TestDB;
GO
ALTER VIEW StockLevels AS (
SELECT WarehouseID,
PartCode,
Quantity
FROM dbo.Warehouse1
UNION ALL
SELECT WarehouseID,
PartCode,
Quantity
FROM dbo.Warehouse2
UNION ALL
SELECT WarehouseID,
PartCode,
Quantity
FROM SecondTestDB.dbo.Warehouse1
UNION ALL
SELECT WarehouseID,
PartCode,
Quantity
FROM SecondTestDB.dbo.Warehouse2
);
GO
This view now accesses the ‘Warehouse1’ and ‘Warehouse2’ tables from ‘TestDB’ and ‘SecondTestDB’. Twenty rows of data across four tables.
Figure 8: Altered view results
Inserting a row of data via the view means that we aren’t concerned with selecting the ‘correct’ table (based upon the WarehouseID constraint). The view will insert into the correct table, based upon the value assigned to the column with the constraint.
Listing 11: Insert via the view
USE [TestDB]
GO
INSERT INTO [dbo].[StockLevels]
([WarehouseID]
,[PartCode]
,[Quantity])
VALUES
(2
,'WIDG_1234'
,33)
GO
Figure 9: SELECT to show the inserted row
As shown in Figure 9, the row of data has been inserted into the table ‘Warehouse2’ of ‘TestDB’, which is the table with the constraint that checks for ‘WarehouseID’ having a value of 2.
Listing 12: UPDATE via the view
UPDATE [dbo].[StockLevels]
SET Quantity = 30
WHERE WarehouseID = 2
AND Partcode = 'WIDG-079';
Figure 10: SELECT to show the updated view
UPDATE and DELETE work in the same way.
Behind the scenes, one large difference between using SELECT on a partitioned view and INSERT/UPDATE/DELETE, is that the query accesses all of the tables – as shown in the query plan.
Figure 11: Query plan for a DELETE from a partitioned view
The savings in processing when using SELECT are not evident when using INSERT/UPDATE/DELETE, although it removes the decision-making about which source table requires the action.
Tags: Examples, Partitioned Views, SQL
Recent Comments