Partitioned Views – Notes on SQLhttps://sqlrambling.net
Random articles from a puzzled DBAMon, 22 May 2017 16:41:02 +0000en
hourly
1 http://wordpress.com/https://sqlramblingdotnet.files.wordpress.com/2020/05/braindonor.jpeg?w=32Partitioned Views – Notes on SQLhttps://sqlrambling.net
3232Partitioned Viewshttps://sqlrambling.net/2015/12/17/partitioned-views/
https://sqlrambling.net/2015/12/17/partitioned-views/#respondThu, 17 Dec 2015 09:56:21 +0000http://sqlrambling.net/?p=763Continue reading]]>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’.

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.

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.

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.

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).

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.