SQLServerCentral / Discuss Content Posted by Steve Jones / Article Discussions / Article Discussions by Author / Heaps / Latest Posts

RE: Heaps

UMG Developer — Fri, 06 May 2011 15:02:25 GMT

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!

RE: Heaps

Steve Jones - SSC Editor — Fri, 06 May 2011 10:35:17 GMT

[quote][b]Tom.Thomson (5/6/2011)[/b][hr]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.[/quote]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.

RE: Heaps

Rose Bud — Fri, 06 May 2011 10:25:57 GMT

[quote][b]Tom.Thomson (5/6/2011)[/b] ...drank probably more than I should have when that tired, and started looking at SQLSC...[/quote]So, are you guilty of PUI (Posting Under the Influence)? Nice apology and explanation, BTW, IMO.

RE: Heaps

L' Eomot Inversé — Fri, 06 May 2011 09:58:11 GMT

[quote][b]Steve Jones - SSC Editor (5/6/2011)[/b][hr][quote][b]Tom.Thomson (5/5/2011)[/b][hr]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 [url=http://msdn.microsoft.com/en-us/library/ms188270%28v=SQL.100%29.aspx][i]The Bol Heap Structure page[/i][/url]) 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.[/quote]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.[/quote]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.

RE: Heaps

Steve Jones - SSC Editor — Fri, 06 May 2011 09:07:18 GMT

[quote][b]Tom.Thomson (5/5/2011)[/b][hr]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 [url=http://msdn.microsoft.com/en-us/library/ms188270%28v=SQL.100%29.aspx][i]The Bol Heap Structure page[/i][/url]) 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.[/quote]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.

RE: Heaps

SQLBalls — Fri, 06 May 2011 07:00:02 GMT

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 FG3Physical FG1 FG2 FG3You 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 sayCREATE 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 valuesour 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.

RE: Heaps

james.e.steele 14953 — Thu, 05 May 2011 20:40:49 GMT

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?

RE: Heaps

Evil Kraig F — Thu, 05 May 2011 11:55:28 GMT

RE: Heaps

L' Eomot Inversé — Thu, 05 May 2011 11:53:00 GMT

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 [url=http://msdn.microsoft.com/en-us/library/ms188270%28v=SQL.100%29.aspx][i]The Bol Heap Structure page[/i][/url]) 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.

RE: Heaps

Peter Trast — Wed, 04 May 2011 13:50:24 GMT

[quote][b]wware (5/4/2011)[/b][hr][quote][b]Steve Jones - SSC Editor (5/4/2011)[/b][hr][quote][b]Chris Houghton (5/4/2011)[/b][hr]Nice technical question Steve. Are some of these recent questions resulting from you studying for the MCM Certification?[/quote]They are. I'm trying to pick items that I think are interesting or surprised me.[/quote]If you keep this up, pretty soon we'll all be ready for the MCM exam....not![/quote]That's the best part! He is supplementing my MCM reading. I might have to submit some questions now, too ;-)

RE: Heaps

Rose Bud — Wed, 04 May 2011 13:11:28 GMT

[quote][b]Steve Jones - SSC Editor (5/4/2011)[/b][hr][quote][b]Chris Houghton (5/4/2011)[/b][hr]Nice technical question Steve. Are some of these recent questions resulting from you studying for the MCM Certification?[/quote]They are. I'm trying to pick items that I think are interesting or surprised me.[/quote]If you keep this up, pretty soon we'll all be ready for the MCM exam....not!

RE: Heaps

jlennartz — Wed, 04 May 2011 10:48:09 GMT

Thanks for the excellect questions recently Steve. Straight forward but makes one think.

RE: Heaps

Steve Jones - SSC Editor — Wed, 04 May 2011 10:14:27 GMT

[quote][b]Chris Houghton (5/4/2011)[/b][hr]Nice technical question Steve. Are some of these recent questions resulting from you studying for the MCM Certification?[/quote]They are. I'm trying to pick items that I think are interesting or surprised me.

RE: Heaps

Peter Trast — Wed, 04 May 2011 09:19:14 GMT

[quote][b]SanDroid (5/4/2011)[/b][hr][quote][b]Koen Verbeeck (5/3/2011)[/b][hr]Nice question! You really had me in doubt, as 4 seemed a too easy answer :-)[/quote]I felt the same way, but knew the answer was 4. I hope that is why over 40% are getting the wrong answer.[/quote]LOL... made me pause, too! It's great to challenge assumptions. Another great question, thanks!

RE: Heaps

SanDroid — Wed, 04 May 2011 09:15:56 GMT

[quote][b]Koen Verbeeck (5/3/2011)[/b][hr]Nice question! You really had me in doubt, as 4 seemed a too easy answer :-)[/quote]I felt the same way, but knew the answer was 4. I hope that is why over 40% are getting the wrong answer.

RE: Heaps

Chris Houghton — Wed, 04 May 2011 09:08:09 GMT

Nice technical question Steve. Are some of these recent questions resulting from you studying for the MCM Certification?

RE: Heaps

Chris Umbaugh — Wed, 04 May 2011 08:46:14 GMT

Good question. Thanks Steve.

RE: Heaps

Steve Jones - SSC Editor — Wed, 04 May 2011 08:12:47 GMT

4 heap structures. If you read the reference, it states this.

RE: Heaps

SQLRNNR — Wed, 04 May 2011 07:58:35 GMT

Good question

RE: Heaps

Incindium — Wed, 04 May 2011 07:54:30 GMT

So the clustered index bit of the question was just a decoy huh? Either way there will be 4 structures whether it is 4 heaps or 4 b-tree's right?

RE: Heaps

jeff.mason — Wed, 04 May 2011 07:47:40 GMT

I knew it was either 1 or 4, but I wasn't sure whether the heap would span the partitions or not. So I took a stab at 1 and was wrong. I was just too sure that there was a trick there and dug in too deep, because my gut was telling me 4.

RE: Heaps

Surii — Wed, 04 May 2011 07:45:45 GMT

Good question Steve.

RE: Heaps

Steve Jones - SSC Editor — Wed, 04 May 2011 07:21:59 GMT

Glad it went over well. This one was hard to write without trying to trick people. I wanted it to make you think, but be solvable without misleading anyone.

RE: Heaps

SQLBalls — Wed, 04 May 2011 06:49:56 GMT

Good question, I know instinctively that each object get's a new set of allocation Units per partition, but I normally do this with clustered indexes, and not heaps.I went out and did this on a test db real quick to validate what already had known, it's questions like this that help us validate book knowledge by looking in all the little knooks and cranies.

RE: Heaps

Duncan Pryde — Wed, 04 May 2011 05:27:05 GMT

Thanks for the question. I dithered a little as well, before selecting the "obvious" answer of 4. A good, non-trick question.

RE: Heaps

mohammed moinudheen — Wed, 04 May 2011 02:24:28 GMT

Thank you Steve. :-)

RE: Heaps

scottm30 — Wed, 04 May 2011 00:38:20 GMT

Good question Steve! 4 seemed too obvious to me as well.

RE: Heaps

Koen Verbeeck — Tue, 03 May 2011 23:36:04 GMT

Nice question! You really had me in doubt, as 4 seemed a too easy answer :-)

Heaps

Steve Jones - SSC Editor — Tue, 03 May 2011 22:44:12 GMT

Comments posted to this topic are about the item [B]Heaps[/B]