Flashcards in Partitioned Views Deck (13)
What is a partitioned View?
They are views that allow the data in a large table to be split into smaller member tables.
What version of SQL Server is table partitioning available?
What can be done in Standard edition for partitioning a table?
By using a partitioned view
How is the data partitioned between tables?
It is based on ranges of data values in one of the columns.
What is required for the ranges for a column for the view to be able to select specific tables only?
A check constraint is required on the partitioning column.
How is the view set up with the partitioned tables?
By using UNION ALL:
CREATE VIEW VIEWNAME AS
SELECT * FROM TABLE1PART1
SELECT * FROM TABLE1PART2
SELECT * FROM TABLE1PART3
How can you perform updates on a partitioned view?
The partitioning column MUST be part of the primary key of the base table. If not, then you must use an INSTEAD OF trigger to perform updates.
What happens if you don't have check constraints on the partitioned columns?
The query optimizer cannot guarantee that records don't exist in each of the partitioned tables so it results in table scans of each one.
What is it called when all of the member tables referenced by a partitioned view are on the same server?
A Local Partitioned View
What is it called when all of the member tables referenced by a partitioned view are on multiple servers?
A Distributed Partitioned View
What is the benefit of a Distributed Partitioned View over a Local Partitioned View?
Distributed Partitioned Views can be used to spread the database processing load of a system across a group of servers.
They also make it easier to maintain the member tables independently.
What can you use to create Insert statements with execution plans that have a significant chance of being reused in systems with many concurrent users?