That's a Switch!

Question

That's a Switch!

sknox

SSChampion

Points: 12388
More actions
June 28, 2017 at 11:20 pm

#402229

Comments posted to this topic are about the item That's a Switch!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Stewart "Arturius" Campbell SSC Guru Points: 72499 More actions · Answer 1

Nice question, thanks Mr Knox.
Have built numerous such operations on partitioned tables, never thought to use it on non-partitioned tables.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

HappyGeek SSCoach Points: 18726 More actions · Answer 2

HappyGeek

SSCoach

Points: 18726

June 29, 2017 at 12:26 am

#1948675

Very good question, thank you indeed!

...

paul s-306273 SSChampion Points: 10727 More actions · Answer 3

Two thirds right, but assumed the data wouldn't get switched.

TomThomson SSC Guru Points: 104773 More actions · Answer 4

Definitely an interesting question. But the references in the explanation provide not a clue about the answer, as far as I can tell.

Tom

George Vobr SSCrazy Eights Points: 9917 More actions · Answer 5

Fortunately, I found in the documentation an example with ALTER TABLE ... SWITCH into non-partitioned table ... 😉
Thanks Sknox for an interesting question and also for the very good comprehensive explanation.

sknox SSChampion Points: 12388 More actions · Answer 6

paul s-306273 - Thursday, June 29, 2017 3:38 AM
Two thirds right, but assumed the data wouldn't get switched.

This question came about because I was scripting a bunch of data moves for partitioned tables, and a pair of non-partitioned tables slipped into the script. I noticed right after hitting "Execute" (isn't that always the case?)

Since you don't get a row count with a partition switch, all I saw were the warnings, and I had to check whether the data moved or not.

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 7

Good question. Thanks.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

RohitK25 SSC Enthusiast Points: 198 More actions · Answer 8

Never thought of that. This question cleared my doubts.
Good One!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

This is a really fun find. I had never considered switching a non-partitioned table. There are some interesting uses for switch with non-partitioned tables.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLWinther Right there with Babe Points: 769 More actions · Answer 10

I knew all tables have at least 1 partition, but I didn't think it should be possible to switch to partition 5 as it doesn't exists.