Results 1 to 6 of 6

Thread: SQL Question

  1. #1
    Join Date
    Sep 2003
    Posts
    1

    Smile Unanswered: SQL Question

    Does SQL Server need an explicit index to be created for a
    primary key?

  2. #2
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78
    Not quite sure what you mean by an 'explicit index'. But, you can designate a field to a primary key when you create the table. This then creates a primary key constraint.

    e.g.:

    create table fred (
    value int primary key
    )

    This is considered a constaint, not an index.

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    If you define field or fields as a primary key for table - index will be automatically created.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, sort of.

    Any unique column or combination of columns can be defined as the primary key, and this creates a "clustered index" on the table. Unlike other indexes, the clustered index is stored with the data and does not require additional space (non-clustered indexes are stored separate from the data), because the clustered index represents that order in which the data is actually stored. This is why you can only have one primary key/clustered index; the data can only be stored in one sort order.

    blindman

  5. #5
    Join Date
    Sep 2003
    Posts
    522
    unless you declare your PK as nonclustered, in which case order is not dictated by PK.

  6. #6
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    What a noise! Snail gave the short answer; an index will also automatically be created on a foreign key constraint. All other indices mentioned (unique, clustered, ...) are extra.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

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