May 4, 2011 at 9:19 am
SanDroid (5/4/2011)
Koen Verbeeck (5/3/2011)
Nice question! You really had me in doubt, as 4 seemed a too easy answer 🙂I felt the same way, but knew the answer was 4.
I hope that is why over 40% are getting the wrong answer.
LOL... made me pause, too! It's great to challenge assumptions. Another great question, thanks!
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
May 4, 2011 at 10:14 am
Chris Houghton (5/4/2011)
Nice technical question Steve. Are some of these recent questions resulting from you studying for the MCM Certification?
They are. I'm trying to pick items that I think are interesting or surprised me.
May 4, 2011 at 10:48 am
Thanks for the excellect questions recently Steve. Straight forward but makes one think.
May 4, 2011 at 1:11 pm
Steve Jones - SSC Editor (5/4/2011)
Chris Houghton (5/4/2011)
Nice technical question Steve. Are some of these recent questions resulting from you studying for the MCM Certification?They are. I'm trying to pick items that I think are interesting or surprised me.
If you keep this up, pretty soon we'll all be ready for the MCM exam....not!
May 4, 2011 at 1:50 pm
wware (5/4/2011)
Steve Jones - SSC Editor (5/4/2011)
Chris Houghton (5/4/2011)
Nice technical question Steve. Are some of these recent questions resulting from you studying for the MCM Certification?They are. I'm trying to pick items that I think are interesting or surprised me.
If you keep this up, pretty soon we'll all be ready for the MCM exam....not!
That's the best part! He is supplementing my MCM reading. I might have to submit some questions now, too 😉
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
May 5, 2011 at 11:53 am
I am very sure that the "correct" answer is utterly wrong.
The MS documentation makes an absolutely clear distinction between "heap" and "heap structure". For example the sentence "For example, if a heap has four partitions, there are four heap structures; one in each partition" (from The Bol Heap Structure page) clearly indicates that a "heap" can have four partitions, with one "heap structure" in each, and several other sentences on the same page make the same clear and unambiguous distinction. Even the explanation given for the answer talks about "heap structures" instead of "heaps", and the reference to BoL given in the explanation is to the very page that clearly indicates that a heap can consist of several heap structures, one for each partition of the heap.
So the question isn't about SQL Server, it's about how the English of that BoL page is interpreted - and the "correct" answer seems to me to require a perverse interpretation of that English.
Tom
May 5, 2011 at 11:55 am
Tom.Thomson (5/5/2011)
I am very sure that the "correct" answer is utterly wrong.The MS documentation makes an absolutely clear distinction between "heap" and "heap structure". For example the sentence "For example, if a heap has four partitions, there are four heap structures; one in each partition" (from The Bol Heap Structure page) clearly indicates that a "heap" can have four partitions, with one "heap structure" in each, and several other sentences on the same page make the same clear and unambiguous distinction. Even the explanation given for the answer talks about "heap structures" instead of "heaps", and the reference to BoL given in the explanation is to the very page that clearly indicates that a heap can consist of several heap structures, one for each partition of the heap.
So the question isn't about SQL Server, it's about how the English of that BoL page is interpreted - and the "correct" answer seems to me to require a perverse interpretation of that English.
Huh?
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
May 5, 2011 at 8:40 pm
Total newbie here, and a little confused. If the master table has a heap structrure, and you create four partitions that end up with their own heap structure, what happened to the heap structure of the master table? Or is it just split as the table is partitioned?
May 6, 2011 at 7:00 am
Hi Jamie,
A Heap is just a Table without a Clustered Index.
Think of it like a filing cabinet, with a heap, you just put all new documents to be filed in the order that you receive them.
Adding a Clustered Index you would sort the files in a particular way, by Last name, Company name, something like that.
This is a high level overview of Partitioning for more info go to the link Steve provided.
In 2005 Wide Table Partitioning was introduced. A partition will allow you to spread a table out over multiple logical Filegroup, you can associate those logical file groups with a separate Physical File Group but you don't have to.
Logical FG1 FG2 FG3
Physical FG1 FG2 FG3
You would create a Partition Function that has a range of values, you group from the left or the right, for example an Integer with Left breaks at 2000, 4000, and anything above 4000. A function has to have a particular data type, but there are more options than just numbers.
Next you create a Scheme that associates the Function with a particular set of Filegroup.
so we'd say values 0-2000 go to FG1, 2001-4000 go to FG2, and 4000 and higher go to FG3.
You then create your table and specify that the table is
ON PARTITIONSCHEMENAME(TABLE COLUMN THAT MATCHES THE FUNCTION)
So we'd say
CREATE TABLE dbo.HEAP1(
myID int IDENTITY(1,1)
,myData CHAR(500) DEFAULT 'a'
) ON demoPartitionScheme(myID)
You could also rebuild a heap, 2008 and up, specifying to rebuild it on the Partition Scheme.
At that point it sorts your data, per the partition Scheme.
So let's take our table and insert 6000 values
our break down will find 2000 rows on FG1, 2000 rows on FG2, 2000 Rows on FG3.
If you look at the table in SSMS, you see only 1 table. That is because it is 1 table structure. However under the covers you have 3 file groups, 3 B-Tree Structures, and 3 sets of Allocation Units for each Partition, per each object (the object in this case is our table dbo.heap1).
If you wanted to find record number 3459, when the query hit's SQL when it is building the query plan, it is smart enough to know that a partitioned table is being queried. SQL will look at the Function and the Scheme and see what Filegroup the range of that information should be found, and it will query the B-Tree for FG2.
I tried to keep this pretty straight forwards, there are a lot of "it depends" that could apply here. Hope this helps.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
May 6, 2011 at 9:07 am
Tom.Thomson (5/5/2011)
I am very sure that the "correct" answer is utterly wrong.The MS documentation makes an absolutely clear distinction between "heap" and "heap structure". For example the sentence "For example, if a heap has four partitions, there are four heap structures; one in each partition" (from The Bol Heap Structure page) clearly indicates that a "heap" can have four partitions, with one "heap structure" in each, and several other sentences on the same page make the same clear and unambiguous distinction. Even the explanation given for the answer talks about "heap structures" instead of "heaps", and the reference to BoL given in the explanation is to the very page that clearly indicates that a heap can consist of several heap structures, one for each partition of the heap.
So the question isn't about SQL Server, it's about how the English of that BoL page is interpreted - and the "correct" answer seems to me to require a perverse interpretation of that English.
I'm not sure I agree. The question says 4 partitions, how many heap structures. The answer is 4, the reference says one heap structure per partition.
May 6, 2011 at 9:58 am
Steve Jones - SSC Editor (5/6/2011)
Tom.Thomson (5/5/2011)
I am very sure that the "correct" answer is utterly wrong.The MS documentation makes an absolutely clear distinction between "heap" and "heap structure". For example the sentence "For example, if a heap has four partitions, there are four heap structures; one in each partition" (from The Bol Heap Structure page) clearly indicates that a "heap" can have four partitions, with one "heap structure" in each, and several other sentences on the same page make the same clear and unambiguous distinction. Even the explanation given for the answer talks about "heap structures" instead of "heaps", and the reference to BoL given in the explanation is to the very page that clearly indicates that a heap can consist of several heap structures, one for each partition of the heap.
So the question isn't about SQL Server, it's about how the English of that BoL page is interpreted - and the "correct" answer seems to me to require a perverse interpretation of that English.
I'm not sure I agree. The question says 4 partitions, how many heap structures. The answer is 4, the reference says one heap structure per partition.
You're right :blush:. Sorry, brain wasn't functioning - I looked at it and thought "how many heaps" and answered that instead of what was actually asked; and then didn't check the question again :blush::blush: when I got the wrong answer, so wrote a silly comment :blush::blush::blush:. Checked it again now when I saw your reply. My only excuse (which is no excuse really) is that I got off a plane in the small hours of the morning after a long flight, had to go to a reunion lunch without first catching up on sleep and drank probably more than I should have when that tired, and started looking at SQLSC when I got back from that instead of going to bed and sleeping.
Tom
May 6, 2011 at 10:25 am
Tom.Thomson (5/6/2011)
...drank probably more than I should have when that tired, and started looking at SQLSC...
So, are you guilty of PUI (Posting Under the Influence)? Nice apology and explanation, BTW, IMO.
May 6, 2011 at 10:35 am
Tom.Thomson (5/6/2011)
You're right :blush:. Sorry, brain wasn't functioning - I looked at it and thought "how many heaps" and answered that instead of what was actually asked; and then didn't check the question again :blush::blush: when I got the wrong answer, so wrote a silly comment :blush::blush::blush:. Checked it again now when I saw your reply. My only excuse (which is no excuse really) is that I got off a plane in the small hours of the morning after a long flight, had to go to a reunion lunch without first catching up on sleep and drank probably more than I should have when that tired, and started looking at SQLSC when I got back from that instead of going to bed and sleeping.
No worries, Tom. I've made more than a few mistakes posting when I wasn't thinking clearly.
I respect your thoughts on these, so wanted to be sure I wasn't missing something.
May 6, 2011 at 3:02 pm
Nice question, I had to really do some research to see if I was missing anything as I thought 4 was just too obvious. Thank!
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply