Professional Web Applications Themes

PK == Clustered by default? - Microsoft SQL / MS SQL Server

Can an MVP (SQL) verify this information? I do not find any supporting evidence or other pages for it. Also, I am skeptical. If this were true, the checkbox for 'clustered' would be marked in the manage indexes dialog. I would not be able to create a clustered key if the primary key were already clustered. Thanks....

  1. #1

    Default PK == Clustered by default?

    Can an MVP (SQL) verify this information? I do not find any
    supporting evidence or other pages for it. Also, I am skeptical. If
    this were true, the checkbox for 'clustered' would be marked in the
    manage indexes dialog. I would not be able to create a clustered key
    if the primary key were already clustered.

    Thanks.

    Grok Guest

  2. #2

    Default Re: PK == Clustered by default?

    By default, when you create a primary key, it is clustered. You can override this.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Grok" <net> wrote in message news:com...
    Can an MVP (SQL) verify this information? I do not find any
    supporting evidence or other pages for it. Also, I am skeptical. If
    this were true, the checkbox for 'clustered' would be marked in the
    manage indexes dialog. I would not be able to create a clustered key
    if the primary key were already clustered.

    Thanks.


    Tom Guest

  3. #3

    Default Re: PK == Clustered by default?

    A PK is created as a clustered index by default unless a clustered index
    already exists or you specify the NONCLUSTERED option.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Grok" <net> wrote in message
    news:com... 


    David Guest

  4. #4

    Default Re: PK == Clustered by default?

    On Wed, 6 Aug 2003 09:29:16 -0400, "Tom Moreau"
    <spam.me.cips.ca> wrote:
     

    Thank you both for your prompt answers.

    * Is this a new default setting for new primary keys with SQL Server
    2000? The databases in question were created in SQL 6.5 and upgraded
    to SQL 7.0, and we will be going to SQL 2000 within two months.

    In chasing down deadlocks, we read about having a clustered index on
    larger tables. Enterprise Manager -> Manage Indexes -> shows the list
    of indexes, and a column indicating if each index is clustered. It
    shows 'no' for every index, including the primary key index.

    * Am I correct that this means our table has no clustered index?

    * Is there a query I can run to ask SQL to tell me the clustered
    index on a table?


    Grok Guest

  5. #5

    Default Re: PK == Clustered by default?

    Run sp_helpindex for the table in question. It tells you if an index is clustered or not. In versions 6.5, 7.0 and 2000, the PK is clustered by default - unless you specify otherwise.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Grok" <net> wrote in message news:com...
    On Wed, 6 Aug 2003 09:29:16 -0400, "Tom Moreau"
    <spam.me.cips.ca> wrote:
     

    Thank you both for your prompt answers.

    * Is this a new default setting for new primary keys with SQL Server
    2000? The databases in question were created in SQL 6.5 and upgraded
    to SQL 7.0, and we will be going to SQL 2000 within two months.

    In chasing down deadlocks, we read about having a clustered index on
    larger tables. Enterprise Manager -> Manage Indexes -> shows the list
    of indexes, and a column indicating if each index is clustered. It
    shows 'no' for every index, including the primary key index.

    * Am I correct that this means our table has no clustered index?

    * Is there a query I can run to ask SQL to tell me the clustered
    index on a table?



    Tom Guest

  6. #6

    Default Re: PK == Clustered by default?

    Excellent. This shows all indexes, including primary key, as
    nonclustered. My associate is insisting that this means we did not
    ask for it to be clustered, but that SQL Server uses the primary key
    as a clustered index "in the background" and won't display the
    checkbox unless you ask for it to be clustered. I'm using inadequate
    arguments to disuade him from this opinion.

    index_name index_description
    ------------------------------------
    ------------------------------------------------------------
    aaaaaClaimInfo_PK nonclustered, unique, primary key
    located on PRIMARY
    ClaimArea nonclustered located on PRIMARY
    ClaimNumber nonclustered located on PRIMARY
    FoldersClaimInfo nonclustered located on PRIMARY
    FromID nonclustered located on PRIMARY
    GroupNumber nonclustered located on PRIMARY
    IssuedDate nonclustered located on PRIMARY
    PlanCode nonclustered located on PRIMARY
    PolicyNumber nonclustered located on PRIMARY
    Product nonclustered located on PRIMARY
    StateCode nonclustered located on PRIMARY
    UserID nonclustered located on PRIMARY
    _WA_Sys_BatchDate_2BFE89A6 nonclustered, statistics, auto
    create located on PRIMARY
    _WA_Sys_ClaimsIDStatus_2BFE89A6 nonclustered, statistics, auto
    create located on PRIMARY
    Priority nonclustered located on PRIMARY
    _WA_Sys_ClaimantName_2BFE89A6 nonclustered, statistics, auto
    create located on PRIMARY

    I have no idea where these aaaaaClaimInfo_PK or _WA_Sys_... names come
    from. Nobody here remembers creating them.



    On Wed, 6 Aug 2003 10:00:59 -0400, "Tom Moreau"
    <spam.me.cips.ca> wrote:
     

    Grok Guest

  7. #7

    Default Re: PK == Clustered by default?

    here is a query that will return all tables without clustered indexes and
    their rowcounts. Should work in 7.0 or 2000. Could take a while with large
    tables.

    IMHO, all tables should have a clustered index unless you are _temporarily_
    optimizing data loading by using a heap.

    ----
    set cursor_close_on_commit off
    DECLARE c CURSOR

    FOR SELECT name FROM sysobjects where xtype = 'U' and not exists (select
    * from sysindexes where indid = 1 and sysindexes.id = sysobjects.id) order
    by name

    OPEN c
    declare tbl varchar(200)
    declare sql varchar(1000)

    FETCH NEXT FROM c into tbl
    SET NOCOUNT ON
    WHILE FETCH_STATUS =0
    BEGIN
    select sql = 'SELECT ''' + tbl + ''' as TABLENAME, COUNT(*) AS ROW_COUNT
    FROM [' + tbl + ']'
    exec(sql)
    FETCH NEXT FROM c into tbl

    END

    close c
    deallocate c

    ----------------------------------------------------
    The views expressed here are my own
    and not of my employer.
    ----------------------------------------------------
    "Grok" <net> wrote in message
    news:com... [/ref]
    clustered or not. In versions 6.5, 7.0 and 2000, the PK is clustered by
    default - unless you specify otherwise. 


    Kevin Guest

  8. #8

    Default Re: PK == Clustered by default?

    Grok,
     [/ref]
    > from. Nobody here remembers creating them.[/ref]

    These are not indexes. They are statistics over non-indexed column and is a
    good thing to have. The reason why they are created is because you have the
    'auto create statistics' database option checked ON.

    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "Grok" <net> wrote in message
    news:com... [/ref]
    clustered or not. In versions 6.5, 7.0 and 2000, the PK is clustered by
    default - unless you specify otherwise. 


    Dinesh.T.K Guest

  9. #9

    Default Re: PK == Clustered by default?

    Ah, perfect. This was the source of his confusion. He, nor I, knew
    about this problem with SQL7 EM.

    To answer the service pack question, no, I do not think it was fixed.
    We are up to date on SQL Server 7 service packs and patches, that I am
    aware of.

    Thanks to all. Professional group.


    On Wed, 6 Aug 2003 09:11:26 -0500, "Dinesh.T.K"
    <mail.tkdinesh.com> wrote:
     [/ref]
    >
    >Since Grok mentions about enterprise manager...
    >Dont know if the below behaviour was corrected in later SQL7 service packs,
    >
    >BUG: SQL Server Enterprise Manager Creates a Nonclustered Index for a PRIMARY KEY Constraint
    >http://support.microsoft.com/default.aspx?scid=kb;en-us;315875[/ref]

    Grok Guest

Similar Threads

  1. clustered CF services
    By johnnyTarr in forum Coldfusion Server Administration
    Replies: 0
    Last Post: December 2nd, 01:21 AM
  2. cfchart clustered bar chart
    By gogl in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: October 13th, 08:26 PM
  3. can CFCHART be used in a Clustered Envrionment
    By jcameron in forum Macromedia ColdFusion
    Replies: 0
    Last Post: June 18th, 08:25 PM
  4. Session / Clustered Servers
    By Rob Meade in forum ASP
    Replies: 0
    Last Post: October 11th, 10:24 AM
  5. Clustered web farm
    By sudha in forum ASP.NET General
    Replies: 3
    Last Post: July 10th, 12:35 AM

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