Professional Web Applications Themes

is primary key implicitly included in an index? - Microsoft SQL / MS SQL Server

on the following: CREATE TABLE T (id INTEGER NOT NULL PRIMARY KEY, Name varchar(50) NOT NULL); I want the following query to run very fast: SELECT id, Name FROM T ORDER BY Name so I need to index the Name column. I want all columns to be included in the index. CREATE INDEX TIndex ON T (Name) will that do it, or do I need to do: CREATE INDEX TIndex ON T (Name, Id) The generic question is, do I need to include primary keys are part of the index? Seems to me that maybe an index entry could implicitly ...

  1. #1

    Default is primary key implicitly included in an index?

    on the following:

    CREATE TABLE T
    (id INTEGER NOT NULL PRIMARY KEY,
    Name varchar(50) NOT NULL);


    I want the following query to run very fast:

    SELECT id, Name FROM T ORDER BY Name


    so I need to index the Name column. I want all columns to be included in the
    index.

    CREATE INDEX TIndex
    ON T (Name)

    will that do it, or do I need to do:

    CREATE INDEX TIndex
    ON T (Name, Id)

    The generic question is, do I need to include primary keys are part of the
    index? Seems to me that maybe an index entry could implicitly have the
    primary key stored? (Im new to sql, but seems it would be a good idea to
    have such feature in sql)


    Zig Mandel Guest

  2. #2

    Default Re: is primary key implicitly included in an index?

    Here is my understanding:
    When you specify a primary key for your table, if a clustered index does not
    already exist or if the non-clustered specification is not provided, SQL
    Server automatically creates a clustered index for the primary key. Now, if
    your "name" column is defined as a "non-clustered" index, the index pages
    have a bookmark that tell SQL Server where to find the data. If you have a
    clustered index defined on the table, the bookmark is the clustered key
    itself, therefore, if you search on a non-clustered key, after traversing
    the index pages and finding the values, SQL Server will use the clustered
    index and scan the index pages again and obtains the data (since the
    leaf-node of clustered index is the data itself). So, for your case,
    specifying just (Name) as a clustered index will help, since SQL Server has
    to do only one level of traversing, since the leaf level pages will have the
    data. This is provided, that (Name) searches are predominant in your
    queries.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!

    "Zig Mandel" <nonehotmail.com> wrote in message
    news:%239FQMyHQDHA.2480tk2msftngp13.phx.gbl...
    > on the following:
    >
    > CREATE TABLE T
    > (id INTEGER NOT NULL PRIMARY KEY,
    > Name varchar(50) NOT NULL);
    >
    >
    > I want the following query to run very fast:
    >
    > SELECT id, Name FROM T ORDER BY Name
    >
    >
    > so I need to index the Name column. I want all columns to be included in
    the
    > index.
    >
    > CREATE INDEX TIndex
    > ON T (Name)
    >
    > will that do it, or do I need to do:
    >
    > CREATE INDEX TIndex
    > ON T (Name, Id)
    >
    > The generic question is, do I need to include primary keys are part of the
    > index? Seems to me that maybe an index entry could implicitly have the
    > primary key stored? (Im new to sql, but seems it would be a good idea to
    > have such feature in sql)
    >
    >

    SriSamp Guest

Similar Threads

  1. default index for primary key of a table
    By Greg Stark in forum PostgreSQL / PGSQL
    Replies: 0
    Last Post: December 24th, 08:15 PM
  2. converting unique index into primary key
    By Ed L. in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: December 23rd, 03:42 AM
  3. default index created for primary key
    By vinita bansal in forum PostgreSQL / PGSQL
    Replies: 7
    Last Post: December 22nd, 07:30 PM
  4. Replies: 6
    Last Post: January 31st, 02:48 AM
  5. Changing Primary Index Question
    By Bill Hamilton in forum Informix
    Replies: 8
    Last Post: September 4th, 05:29 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139