SQL Kiwi (9/17/2011)
Good question, but the answer is totally wrong.
Exactly. I can only imagine the author did test the code before submitting this question, but made a late change to the partition numbers without retesting.
Heheh, coming late to the game I know, but I made the wrong guess about which typo was wrong. Wasn't sure if it was a 'gotcha' or not.
I figured the last option was basically "It doesn't matter what's empty, this will fail." Oops. I caught the boundaries issue but wasn't paying enough attention to the filegroups... >.<
For those who'd like a parition test bed to go goof off with (SQL 2k8):
Use [Test] --Use your own DB here.
create partition function pf1 (int)
range left for values (10000, 30000, 50000);
create partition scheme ps1
partition pf1 to ([PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY]);
CREATE TABLE tester1
CREATE CLUSTERED INDEX idx_tester1 ON tester1 (SampleID) ON ps1( SampleID)
Create Table tester2
CREATE CLUSTERED INDEX idx_tester2 ON tester2 (SampleID) ON ps1( SampleID)
INSERT INTO tester1
VALUES ( 1, 'ValueA'), (11000, 'Value B'), (35000, 'Value C'), (58000, 'Value D')
INSERT INTO tester2
VALUES ( 2, 'Value X'), (13000, 'Value Y'), (36000, 'Value Z')
Took a few tries to remember which view I wanted...
SELECT * FROM sys.partition_functions
SELECT * FROM sys.partition_parameters
SELECT * FROM sys.partition_range_values
SELECT * FROM sys.partition_schemes
SELECT * FROM sys.data_spaces
select * FROM sys.destination_data_spaces
-- Proove that PArtition 4 in second table is empty
SELECT * FROM sys.partitions
WHERE object_id IN ( object_id( 'tester1'), object_id('tester2'))
--Now, here we go.
ALTER TABLE tester1 SWITCH PARTITION 1 TO Tester2 PARTITION 4;
Note that this will give this error:
Msg 4973, Level 16, State 1, Line 2
ALTER TABLE SWITCH statement failed. Range defined by partition 1 in table 'Test.dbo.tester1' is not a subset of range defined by partition 4 in table 'Test.dbo.Tester2'.
So, there's a few items a bit off here, but the general premise is correct, the target of the switch needs to empty. Paul, remember when I mentioned in that other thread that Partitioning seems to be a Level III item? 🙂
- Craig Farrell
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]