SQL Server Tables

  • 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

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

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



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

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

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

    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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

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

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

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

    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:

  • I also agree with SQLRNNR on this one.

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


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • ssimmons 2102 (8/27/2013)


    I also agree with SQLRNNR on this one.

    Me too.

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

    😛

  • 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

  • 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

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

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


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 15 posts - 16 through 30 (of 43 total)

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