DRAG DROP
You have a SQL Server 2012 database named DB1. DB1 contains four filegroups named
FG1, FG2, FG3, and FG4. You execute the following code:
Two million rows are added to dbo.Sales.
You need to move the data from the first partition to a new table named SalesHistory and,
starting on December 31, 2012, repartition dbo.Sales to support new sales data for three
months.
Which code segment should you execute?
To answer, move the appropriate code segments from the list of code segments to the
answer area and arrange them in the correct order.
Answer: See the explanation
Explanation:
Box 1:
Box 2: ALTER TABLE Sales SWITCH 1 to SalesHistory;
Box 3:Box 4:
Box 5:
Box 6:
Note:
* Box 1 – Box 2:
/ You need to move the data from the first partition to a new table named SalesHistory.
/ First create the new table, then move the contents of the first partition.
*( Box 3 Box 4) Drop the partition scheme and then the partition function and the recreate
them (box 5-box6). First recreate the partition function.
/You need, starting on December 31, 2012, repartition dbo.Sales to support new sales data
for three months.
/ A partition function can be dropped only if there are no partition schemes currently using
the partition function. If there are partition schemes using the partition function, DROP
PARTITION FUNCTION returns an error.
create table dbo.salesHistory
(
Date_key int NOT NULL,
Customer_key int,
Amount money)
on PS1(date_Key);
ALTER TABLE dbo.sales SWITCH PARTITION 1 TO salesHistory PARTITION 1
ALTER PARTITION FUNCTION PF1() MERGE RANGE (20120331)
ALTER PARTITION SCHEME PS1 NEXT USED FG1
ALTER PARTITION FUNCTION PF1() SPLIT RANGE (20121231)
create table dbo.salesHistory
(
Date_key int NOT NULL,
Customer_key int,
Amount money)
on PS1(date_Key); — this seems to be wrong (see Errormessage below) must be “on FG1”
Msg 4911, Level 16, State 2, Line 125
Cannot specify a partitioned table without partition number in ALTER TABLE SWITCH statement. The table ‘partitiontest.dbo.SalesHistory’ is partitioned.
It is impossible to DROP PS1 (nor PF1) while it is used by tables Sales and SalesHistory
Yes, it is not possible to drop a partition scheme which is in use:
Msg 7717, Level 16, State 1, Line 116
The partition scheme “PS1” is currently being used to partition one or more tables.
Many options in the exhibit are incorrect
– PF1 and PS1 cannot be dropped because the are in use by Sales table
– the ALTER PARTITION FUNCTION … SPLIT RANGE is correct but we first need to set the NEXT USED file group in partition scheme using ALTER PARTITION SCHEME PS1 NEXT USED . Without this statement, the ALTER PARTITION will fail.
So the correct solution is:
CREATE TABLE SalesHistory
(date_key INT NOT NULL, customer_KEY int,
amount MONEY)
ON ps1(date_key)
ALTER TABLE Sales SWITCH PARTITION 1 TO SalesHistory PARTITION 1
ALTER PARTITION SCHEME PS1 NEXT USED FG4
ALTER PARTITION FUNCTION pf1() SPLIT RANGE (20121231)
ALTER PARTITION FUNCTION pf1() MERGE RANGE (20120331) –>this is options, as it’s not clear if we need do drop 1st partition…