Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Partition


SQL Partition

Author
Message
Srikanth Vijay
Srikanth Vijay
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 40
Comments posted to this topic are about the item SQL Partition

Always Think Positive
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6129 Visits: 25280
Nice question

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Danny Ocean
Danny Ocean
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1432 Visits: 1549
good question

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com
nagesh210
nagesh210
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 35
This quite interesting topic for DW area. Also when the Partition 1 and 4 requires switching? It should fail as partition values are different.
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6283 Visits: 7190
Interesting question.
However, if both tables share the same partiton scheme, which use the same patition function, the partition numbers should be the same

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3524 Visits: 4408
Good question, but the answer is totally wrong. The ALTER TABLE statement fails at least for two reasons (and both of these reasons are available at the link given in the explanation):

1)
General Requirements for Switching Partitions
Both the source and the target of the ALTER TABLE...SWITCH statement must reside in the same filegroup, and their large-value columns must be stored in the same filegroup.


Thus, after running the ALTER TABLE statement we'll see the following error message:
ALTER TABLE SWITCH statement failed. Partition 1 of table 'test_db.dbo.trx' is in filegroup 'fg1' and partition 4 of table 'test_db.dbo.trx_hy' is in filegroup 'fg4'.


2) Let's assume that both partitions reside in the same filegroup.
Constraint Requirements
If you are switching a partition of a partitioned table to another partitioned table, the boundary values of the source partition must fit within the boundary values of the target partition.

Let's create the tables on the following partition scheme:
create partition scheme ps1
as
partition pf1 to ([primary], [primary], [primary], [primary]);



After running the ALTER TABLE statement we'll see the following error message:
ALTER TABLE SWITCH statement failed. Range defined by partition 1 in table 'test_db.dbo.trx' is not a subset of range defined by partition 4 in table 'test_db.dbo.trx_hy'.


So, the correct answer should be "The statement always fails" :-)
Kwex
Kwex
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 240
Nice question! A reminder on PARTITIONS :-)
M&M
M&M
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2797 Visits: 3900
Good question and thanks for your findings vk-kirov

M&M
CoolCodeShare
CoolCodeShare
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1238 Visits: 311
Very nice question Srikant :-)
Stuart Davies
Stuart Davies
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4708 Visits: 4599
I learnt something new today - thanks

-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search