SQL Server Tables

  • Hugo Kornelis (8/27/2013)


    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:

    Somehow I knew you would bypass clarity to be as obtuse as ever...

    i.e.

    1. annoyingly insensitive or slow to understand.

    "he wondered if the doctor was being deliberately obtuse"

    :rolleyes:

  • Love the discussion. I got it by process of elimination.

    Not all gray hairs are Dinosaurs!

  • 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. πŸ˜€

  • 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?

    http://en.wikipedia.org/wiki/Heap_(data_structure) :unsure:

    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

  • 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. 😎

  • 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 πŸ™‚

  • 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.

  • 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

  • Nice question.. and awesome discussion...

  • In which database the heap table will be stored?

    nice question...

    Manik
    You cannot get to the top by sitting on your bottom.

  • Easy.........

  • so easy question

    so big headache after reading your answers and comments

  • Don't think 'nonclustered index' shoud be included in the question.

Viewing 13 posts - 31 through 43 (of 43 total)

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