September 17, 2011 at 1:56 pm
vk-kirov (9/16/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.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 19, 2011 at 5:35 am
Good question, thanks. I always like thinking through these scenarios.
http://brittcluff.blogspot.com/
September 19, 2011 at 6:17 am
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 19, 2011 at 1:00 pm
SQL Kiwi (9/17/2011)
vk-kirov (9/16/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.
GO
create partition function pf1 (int)
as
range left for values (10000, 30000, 50000);
create partition scheme ps1
as
partition pf1 to ([PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY]);
CREATE TABLE tester1
(SampleID INT,
SomeValue VARCHAR(20)
)
CREATE CLUSTERED INDEX idx_tester1 ON tester1 (SampleID) ON ps1( SampleID)
Create Table tester2
(SampleID INT,
SomeValue VARCHAR(20)
)
CREATE CLUSTERED INDEX idx_tester2 ON tester2 (SampleID) ON ps1( SampleID)
GO
INSERT INTO tester1
VALUES ( 1, 'ValueA'), (11000, 'Value B'), (35000, 'Value C'), (58000, 'Value D')
GO
INSERT INTO tester2
VALUES ( 2, 'Value X'), (13000, 'Value Y'), (36000, 'Value Z')
GO
/*
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'))
GO
--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? 🙂
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]
Twitter: @AnyWayDBA
September 19, 2011 at 1:12 pm
Evil Kraig F (9/19/2011)
Paul, remember when I mentioned in that other thread that Partitioning seems to be a Level III item? 🙂
Indeed. Perhaps we need more partitioning questions?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 19, 2011 at 1:23 pm
SQL Kiwi (9/19/2011)
Evil Kraig F (9/19/2011)
Paul, remember when I mentioned in that other thread that Partitioning seems to be a Level III item? 🙂Indeed. Perhaps we need more partitioning questions?
I think we do. But with ultraclear explanations (and correct answers). All this question has taught me is that my understanding of this topic is even smaller than I thought it was.
Tom
September 19, 2011 at 2:52 pm
Tom.Thomson (9/19/2011)
SQL Kiwi (9/19/2011)
Evil Kraig F (9/19/2011)
Paul, remember when I mentioned in that other thread that Partitioning seems to be a Level III item? 🙂Indeed. Perhaps we need more partitioning questions?
I think we do. But with ultraclear explanations (and correct answers). All this question has taught me is that my understanding of this topic is even smaller than I thought it was.
Tom, it's most likely the original author meant to swap Partition 1 to Partition 1, in particular to test the assumption he did, and somehow or another the question got 'fancified'. Everything works if it's Partition 1 from the first table to Parition 1 in the second, if the second table's partition 1 is empty... Just not to Partition 4, for any number of reasons. 🙂
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]
Twitter: @AnyWayDBA
September 20, 2011 at 2:19 am
codebyo (9/17/2011)
I'd love to see an article showing a real world application of switching partitions.I understand it would be great for historical data that can be archived by a date field, or year but I've never made any experiences with it before.
Great question. Thank you.
Best regards,
Partitioning and switching is excellent for archiving and for loading new data into a datawarehouse, and if you are going to use the new COLUMNSTORE index in Denali, it's the only way of adding new data to the table unless you drop the index.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
September 20, 2011 at 2:52 am
hakan.winther (9/20/2011)
codebyo (9/17/2011)
I'd love to see an article showing a real world application of switching partitions.Partitioning and switching is excellent for archiving and for loading new data into a datawarehouse, and if you are going to use the new COLUMNSTORE index in Denali, it's the only way of adding new data to the table unless you drop the index.
There are a number of important scenarios covered in the Data Loading Performance Guide (a TechNet article): http://msdn.microsoft.com/en-us/library/dd425070.aspx
On the column store thing, you can also add a new table to a local partitioned view - a similar idea to 'true' partitioning admittedly, but with some important differences.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply