Partitioned Views

Question 1

What is a partitioned View?

Answer 1

They are views that allow the data in a large table to be split into smaller member tables.

Question 2

What version of SQL Server is table partitioning available?

Answer 2

Enterprise

Question 3

What can be done in Standard edition for partitioning a table?

Answer 3

By using a partitioned view

Question 4

How is the data partitioned between tables?

Answer 4

It is based on ranges of data values in one of the columns.

Question 5

What is required for the ranges for a column for the view to be able to select specific tables only?

Answer 5

A check constraint is required on the partitioning column.

Question 6

How is the view set up with the partitioned tables?

Answer 6

By using UNION ALL:

CREATE VIEW VIEWNAME AS
SELECT * FROM TABLE1PART1
UNION ALL
SELECT * FROM TABLE1PART2
UNION ALL 
SELECT * FROM TABLE1PART3

Question 7

How can you perform updates on a partitioned view?

Answer 7

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.

Question 8

What happens if you don’t have check constraints on the partitioned columns?

Answer 8

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.

Question 9

What is it called when all of the member tables referenced by a partitioned view are on the same server?

Answer 9

A Local Partitioned View

Question 10

What is it called when all of the member tables referenced by a partitioned view are on multiple servers?

Answer 10

A Distributed Partitioned View

Question 11

What is the benefit of a Distributed Partitioned View over a Local Partitioned View?

Answer 11

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.

Question 12

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?

Answer 12

SP_EXECUTESQL

Question 13

Is BULK IMPORTING supported using BCP or BULK INSERT or SELECT * FROM OPENROWSET(BULK…)?

Answer 13

No, the only way to insert multiple records in to a partitioned view is by using an INSERT statement.