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.
I think that you cannot drop a partition scheme that is being used, therefore the answer should look like this:
1. CREATE TABLE SalesHistory ….
2. ALTER TABLE Sales SWITCH 1 TO SalesHistory;
3. ALTER PARTITION FUNCTION PF1 MERGE RANGE (20120331);
and maybe step 3 is not required because the last range of partition function has no upper boundary.
In typical sliding window scenario there are 5 steps:
1.) Create SalesHistory on FG1
2.) ALTER TABLE Sales SWITCH 1 TO SalesHistory;
3.) ALTER PARTITION FUNCTION PF1 MERGE RANGE (20120331);
4.) ALTER PARTITION SCHEME PS1 NEXT USED FG1
5.) ALTER PARTITION FUNCTION PF1 () SPLIT RANGE (20121231)
I think block 4 is missing here.
–1
create table dbo.salesHistory
(
Date_key int NOT NULL,
Customer_key int,
Amount money)
on FG1;
GO
–2
ALTER TABLE Sales SWITCH PARTITION 1 TO SalesHistory
–3
alter partition function PF1() MERGE RANGE (20120331)
–4
ALTER PARTITION SCHEME PS1 NEXT USED FG1
–5
alter partition function PF1() SPLIT RANGE (20121231)
A partition scheme can be dropped only if there are no tables or indexes currently using the partition scheme. If there are tables or indexes using the partition scheme, DROP PARTITION SCHEME returns an error. DROP PARTITION SCHEME does not remove the filegroups themselves.
How can you pretend to an answer a question where the question itself doesn’t make any sense .. .when i look at december 2012 and i see the partition function with date spreading from 31 03 2012 to 03-09-2012.. .. if maybe the dates where mixed up then some of your explanation could makes sense
What are you saying?! The date ranges are from 20120331 to 20120930 [YYYYMMDD].
Since the Partition Function specifies a ‘RANGE LEFT’ in its definition, the upper bound of the first date (2012-03-31) is included in the first partition; hence, the use of:
ALTER PARTITION FUNCTION PF1 MERGE RANGE (20120331) in the solution.
The last date date range (20120930) has no upper bounds, hence the need to define a new partition for 20121231:
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);
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)
?