Partitioned Views Flashcards Preview

70-464 Certification > Partitioned Views > Flashcards

Flashcards in Partitioned Views Deck (13)
Loading flashcards...
1

What is a partitioned View?

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

2

What version of SQL Server is table partitioning available?

Enterprise

3

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

By using a partitioned view

4

How is the data partitioned between tables?

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

5

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.

6

How is the view set up with the partitioned tables?

By using UNION ALL:

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

7

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.

8

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.

9

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

A Local Partitioned View

10

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

A Distributed Partitioned View

11

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.

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?

SP_EXECUTESQL

13

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

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