August 27, 2013 at 6:27 am
While I wouldn't go quite as far as Jason and say the question is incorrect, and I do with Hugo that the explanation is misleading and awful, but that the question and answer considered without the explanation is technically correct, I do think that the question itself is a bad one. There is a term "unindexed table" but it's very rarely rarely used, so rarely that an unindexed table is called a heap far more often than it's called an unindexed table, the answer "a heap" is correct.
If I were feeling pedantic today, I would point out that a table is only called Heap if the developer makes that in its name in the create table for it; that is a matter of grammar and syntax - omitting the indefinite article does change the meaning in English.
By the way, the term "unindexed table" is so rare that there may even be fewer than 300 occurrences on the web. If you try to check that and find 25 times as many just remember that usually "unindexed table column" means an unindexed column in a table, not a column in a table with no indexes, so the term doesn't occur in that phrase.
Tom
August 27, 2013 at 7:06 am
Nice easy question... I always wondered why you can have a Primary Key on a heap and its still a heap if there is no clustered index on the PK.
Maybe this?
August 27, 2013 at 7:10 am
Koen Verbeeck (8/27/2013)
Too easy question, but nice discussion. 🙂
Isn't that always the case? 🙂
That said, around here I believe the statement is... "That table can't be a heap, because we don't allow them in our databases." 🙂
August 27, 2013 at 7:16 am
L' Eomot Inversé (8/27/2013)
While I wouldn't go quite as far as Jason and say the question is incorrect, and I do with Hugo that the explanation is misleading and awful, but that the question and answer considered without the explanation is technically correct, I do think that the question itself is a bad one. There is a term "unindexed table" but it's very rarely rarely used, so rarely that an unindexed table is called a heap far more often than it's called an unindexed table, the answer "a heap" is correct.If I were feeling pedantic today, I would point out that a table is only called Heap if the developer makes that in its name in the create table for it; that is a matter of grammar and syntax - omitting the indefinite article does change the meaning in English.
+1
Never ever hear anyone call a heap an un-indexed table before today. Hear plenty of people call Heaps un-indexed tables or structures. Of course never really saw un-indexed spelled unindexed much before today either.
August 27, 2013 at 7:17 am
PHYData DBA (8/27/2013)
Nice easy question... I always wondered why you can have a Primary Key on a heap and its still a heap if there is no clustered index on the PK.Maybe this?
The presence or absence of a clustered index determines the storage structure for the data - clustered index (B-tree) or heap (unorganized).
The presence or absence of a PRIMARY KEY constraint determines whether you have an actual relational table, or only something that looks that way but in fact violated Codd's rules.
The two are orthogonal. The link you include is unrelated to what "heap" in a relational database means.
August 27, 2013 at 7:20 am
Hugo Kornelis (8/27/2013)
PHYData DBA (8/27/2013)
The two are orthogonal. The link you include is unrelated to what "heap" in a relational database means.
Orthogonal.... That is one heap of a word Hugo... BTW- any sources for the rest of that post or should I just quote you? 😛
August 27, 2013 at 7:34 am
Hugo Kornelis (8/27/2013)
PHYData DBA (8/27/2013)
Nice easy question... I always wondered why you can have a Primary Key on a heap and its still a heap if there is no clustered index on the PK.Maybe this?
The presence or absence of a clustered index determines the storage structure for the data - clustered index (B-tree) or heap (unorganized).
The presence or absence of a PRIMARY KEY constraint determines whether you have an actual relational table, or only something that looks that way but in fact violated Codd's rules.
The two are orthogonal.
FYI - your use of orthogonal is unclear. Did you mean on of these definitions?
a: non-redundant, non-overlapping, or irrelevant.
b: can be used without consideration as to how its use will affect something else
c: being independent (not relative to something else)
:hehe:
August 27, 2013 at 7:49 am
I also agree with SQLRNNR on this one.
August 27, 2013 at 8:06 am
PHYData DBA (8/27/2013)
Hugo Kornelis (8/27/2013)
PHYData DBA (8/27/2013)
The two are orthogonal. The link you include is unrelated to what "heap" in a relational database means.Orthogonal.... That is one heap of a word Hugo... BTW- any sources for the rest of that post or should I just quote you? 😛
For the definition of "heap" (within the context of SQL Server) as a table witout clustered index: http://technet.microsoft.com/en-us/library/ms188270%28v=sql.105%29.aspx. That page also shows the bare basics of the actual structure used for heaps (but forwarding pointers are not covered there).
For a "table" without PRIMARY KEY constraint (or UNIQUE constraint) violating Codd's 12 rules: http://en.wikipedia.org/wiki/Codd%27s_12_rules. See rule 2, the guaranteed access rule.
For the two being orthogonal - no direct sources, but it's very easy to cough up the code to show that (in SQL Server) one can create a heap with a PRIMARY KEY (simply specifiy NONCLUSTERED with the PRIMARY KEY definition to override the default); or a "table" without PRIMARY KEY or UNIQUE constraint but with a clustered index (create table, then explicitly CREATE NONUNIQUE CLUSTERED INDEX).
PHYData DBA (8/27/2013)
FYI - your use of orthogonal is unclear. Did you mean on of these definitions?a: non-redundant, non-overlapping, or irrelevant.
b: can be used without consideration as to how its use will affect something else
c: being independent (not relative to something else)
:hehe:
Hmmm, yeah, I see the confusion. In this case, both statements are relevant, but they are neither redundant, nor overlapping. Either one can be used without consideration as to how it will affect the other - which is a logical consequence of the two being independent of each other.
Does this help? :Whistling:
August 27, 2013 at 8:09 am
ssimmons 2102 (8/27/2013)
I also agree with SQLRNNR on this one.
Me too.
August 27, 2013 at 8:12 am
SQLRNNR (8/27/2013)
For me, the wording of the question implies that a requirement of a heap be that it is completely devoid of indexes.
Then you're not thinking in set-based terms.
The question:
"What is a table called, if it does not have either a Clustered or a nonclustered index?"
translates to
SELECT Description
FROM TableTerminology
WHERE HasClusteredIndex = 0
AND HasNonClusteredIndex = 0;
With the dataset:
HasClusteredIndex HasNonClusteredIndex Description
0 0 Heap
0 1 Heap
1 0 Clustered Table
1 1 Clustered Table
the answer is clear.
😛
August 27, 2013 at 8:18 am
sknox (8/27/2013)
SQLRNNR (8/27/2013)
For me, the wording of the question implies that a requirement of a heap be that it is completely devoid of indexes.
Then you're not thinking in set-based terms.
The question:
"What is a table called, if it does not have either a Clustered or a nonclustered index?"
translates to
SELECT Description
FROM TableTerminology
WHERE HasClusteredIndex = 0
AND HasNonClusteredIndex = 0;
With the dataset:
HasClusteredIndex HasNonClusteredIndex Description
0 0 Heap
0 1 Heap
1 0 Clustered Table
1 1 Clustered Table
the answer is clear.
😛
My vote for answer of the day. Thanks for the laugh.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
August 27, 2013 at 8:47 am
sknox (8/27/2013)
SQLRNNR (8/27/2013)
For me, the wording of the question implies that a requirement of a heap be that it is completely devoid of indexes.
Then you're not thinking in set-based terms.
The question:
"What is a table called, if it does not have either a Clustered or a nonclustered index?"
translates to
SELECT Description
FROM TableTerminology
WHERE HasClusteredIndex = 0
AND HasNonClusteredIndex = 0;
With the dataset:
HasClusteredIndex HasNonClusteredIndex Description
0 0 Heap
0 1 Heap
1 0 Clustered Table
1 1 Clustered Table
the answer is clear.
😛
Haha - got me there :-D.
Funny how re-reading the question after a couple of hours sleep can make the wording of the question less "disagreeable".:cool:
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
August 27, 2013 at 9:17 am
Hugo Kornelis (8/27/2013)
.....PHYData DBA (8/27/2013)
FYI - your use of orthogonal is unclear. Did you mean on of these definitions?a: non-redundant, non-overlapping, or irrelevant.
b: can be used without consideration as to how its use will affect something else
c: being independent (not relative to something else)
:hehe:
Hmmm, yeah, I see the confusion. In this case, both statements are relevant, but they are neither redundant, nor overlapping. Either one can be used without consideration as to how it will affect the other - which is a logical consequence of the two being independent of each other.
Does this help? :Whistling:
So does this paragraph mean C? Did you mean to say that you where using definition C?
Lot of words just to say C.... 😎
August 27, 2013 at 9:24 am
PHYData DBA (8/27/2013)
Lot of words just to say C.... 😎
I think I managed to include almost all the words of each of the three definitions.
And that was no coincidence! :hehe:
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply