August 27, 2013 at 10:11 am
Love the discussion. I got it by process of elimination.
Not all gray hairs are Dinosaurs!
August 27, 2013 at 10:18 am
I really wasn't expecting this much discussion around this question! But thanks for the humor and insights. Makes for enjoyable reading in between "real" work.
August 27, 2013 at 10:23 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?
No, definitely not. A Heap in Sql-Server is neither of the heap structures described on that page, it has no key-based tree structure. If a heap has a primary key, that primary key is not a cluster key and the index supporting the primary key will be a search tree, which will of course have min heap properties but also satisfies a lot more order conditions; the index does not contain any data other than keys and pointers, so the table heap will have a lot of data which isn't in the min heap, which should make it clear that they are definitely not the same thing. Heap is one of those words with rather a lot of different meanings.
Tom
August 27, 2013 at 12:25 pm
L' Eomot Inversé (8/27/2013)
Heap is one of those words with rather a lot of different meanings.
So heap, like orthogonal and obtuse, have many different meanings. Only one of which was covered by today's QOTD.
August 27, 2013 at 12:33 pm
SQLRNNR (8/27/2013)
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:
+100
August 27, 2013 at 12:56 pm
I like this definition -
If updating a row does not affect other records (though it affects secondary indexes) the table is a heap.
Very short exact and clear.
This might have come from Christopher J. Date but I can not be certain. It is not in the Third Manifesto.
August 27, 2013 at 3:41 pm
PHYData DBA (8/27/2013)
I like this definition -If updating a row does not affect other records (though it affects secondary indexes) the table is a heap.
Very short exact and clear.
This might have come from Christopher J. Date but I can not be certain. It is not in the Third Manifesto.
That's another different meaning for heap, one that I've never come across before.
If updating a row affects other records (other than effects through triggers and effects on index nodes other than the leaf elements of a clustered index if one exists), it's not a row in a table in a relational database because in a relational system updates to rows generally don't affect other rows. So that definition appears to say that every table in a relational database (ignoring the effect of triggers) is a heap. Well, maybe cascading effects through a foreign key constraint may effect other rows, so if you have those in your version or the relational model (some don't, some do; I'm a do) a table which is the target of a foreign key of which it is also the source where updates are cascaded is not a heap, but everything else is.
It's almost the sort of illogical stuff that Date would come up with of course, almost on a par with his strange ideas about NULL (especially his "dialogue" with Codd) and his even stranger claims about his atomicity principle being right while Codd's is wrong; but only almost - I think it's probably too silly even for Date; after all, he isn't stupid. So I think you are wrong when you suggest he's where it came from.
Tom
August 28, 2013 at 7:58 am
Nice question.. and awesome discussion...
August 29, 2013 at 2:54 am
In which database the heap table will be stored?
nice question...
Manik
You cannot get to the top by sitting on your bottom.
September 7, 2013 at 5:52 am
Easy.........
October 1, 2013 at 3:57 am
so easy question
so big headache after reading your answers and comments
December 15, 2013 at 9:48 pm
Don't think 'nonclustered index' shoud be included in the question.
Viewing 12 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy