SQL Server Performance

INDEX KEY

Discussion in 'SQL Server 2005 General Developer Questions' started by Rabani, May 22, 2008.

  1. Rabani New Member

    Hi all,
    I am really confused here about the index key. My question is that is the whole indexed column considered as index key or the individual values inside the row of the indexed column is considered as index key? Can somebody please, clearify about it.
    Thanks a lot
    Rabani, May 22, 2008
    #2

  2. Luis Martin Moderator

    If I understood your question in the right way, the whole column.
    Luis Martin, May 22, 2008
    #3

  3. FrankKalis Moderator

    FrankKalis, May 22, 2008
    #4

  4. Luis Martin Moderator

    So, back to the original question: Index Key <> key column nor key values.[;)]
    Luis Martin, May 22, 2008
    #5

  5. FrankKalis Moderator

    <p>[quote user="Luis Martin"]</p><p>&nbsp;So, back to the original question: Index Key &lt;&gt; key column nor key values.<img src="http://sql-server-performance.com/Community/emoticons/emotion-5.gif" alt="Wink"> <br></p><p>[/quote]&nbsp;</p><p>Sorry again, must have been blind yesterday evening. I just reread the question and from that it appears that you are right.<br></p>
    FrankKalis, May 22, 2008
    #6

  6. Adriaan New Member

    If you are talking about data in a table, the "key" would be the (combination of) column value(s) that identifies the single row that you're looking at.
    If you have a unique index, it's the same as for a table.
    If you have a non-unique index, the key will be derived from the clustered index. IIRC, in case there is no clustered index, the database engine adds a uniquifier.
    Adriaan, May 23, 2008
    #7

  7. FrankKalis Moderator

    [quote user="Adriaan"]
    If you have a non-unique index, the key will be derived from the clustered index. IIRC, in case there is no clustered index, the database engine adds a uniquifier.
    [/quote]
    The uniquifier applies only to clustered indices, because internally all clustered indices have to be unique. So, when you create a clustered index not as unique, SQL Server will automatically add the uniquifier when a duplicate key value is about to be stored.
    For nonclustered indices there is no uniquifier. the index is either unique or not. [:)]
    FrankKalis, May 23, 2008
    #8

  8. Adriaan New Member

    Frank,
    I guess it's time for me to read up on indexes again.[:)]
    Adriaan, May 23, 2008
    #9

  9. FrankKalis Moderator

    [quote user="Adriaan"]
    I guess it's time for me to read up on indexes again.
    [/quote]
    Well, I just happen to deal with that issue some days ago, so I read that up in BOL. [:)]
    FrankKalis, May 23, 2008
    #10

  10. Adriaan New Member

    If people started searching BOL before posting here, I'm sure it would get pretty quiet on the forums.[:D]
    Adriaan, May 23, 2008
    #11

  11. FrankKalis Moderator

    <p>[quote user="Adriaan"]</p><p>If&nbsp;people&nbsp;started searching BOL before posting here, I'm sure it would get pretty quiet on the forums.<img src="http://sql-server-performance.com/Community/emoticons/emotion-2.gif" alt="Big Smile"></p><p>[/quote]&nbsp;</p><p>Good point, well presented. [:D]&nbsp;</p>
    FrankKalis, May 23, 2008
    #12

  12. Adriaan New Member

    It's sooooo easy to become a guru! [;)]
    Adriaan, May 23, 2008
    #13

  13. Luis Martin Moderator

    Most of the times I have to read something to answer. Sometimes BOL,, sometimes MS, search, etc. I can't have all in my mind.
    And that is good.[:)]
    Luis Martin, May 23, 2008
    #14

  14. FrankKalis Moderator

    [quote user="Luis Martin"]
    Most of the times I have to read something to answer. Sometimes BOL,, sometimes MS, search, etc. I can't have all in my mind.
    [/quote]
    But you know where to search and that is what it's all about. [;)]
    FrankKalis, May 23, 2008
    #15

  15. Luis Martin Moderator

    Touche.[:D]
    Luis Martin, May 23, 2008
    #16

  16. Kewin New Member

    I must confess I don't quite understand the question..

    To me, 'index key' is a contradiction.
    Perhaps I'm just totally loosing the actual question, but it suggests some mixup between 'index' and 'key',
    where 'index' is a physical structure on the table/column(s) in the physical model, and 'key' is an abstract concept that belongs
    in the logical model.

    In short, an index is not a key, and a key is not an index.
    (if that is indeed the kind of 'key' that is ment here..)
    (Kenneth
    Kewin, May 23, 2008
    #17

  17. Rabani New Member

    Thanks everybody for the answers.It made me so clear about index key and index key values, but I have another question. Can somebody please, let me know about the difference between a table variable and a temporary table.
    Thanks a lot,
    Rabani

    Rabani, May 29, 2008
    #18

  18. satya Moderator

    satya, May 30, 2008
    #19

  19. FrankKalis Moderator

    [quote user="Rabani"]
    Thanks everybody for the answers.It made me so clear about index key and index key values, but I have another question. Can somebody please, let me know about the difference between a table variable and a temporary table.
    Thanks a lot,
    Rabani

    [/quote]
    Please start a new thread for this question. This will improve your chances that people actually notice that you have a second question. Especially since this question is not correlated to the first one and therefore the thread subject is misleading.
    FrankKalis, May 30, 2008
    #20

Share This Page