Professional Web Applications Themes

Creation of primary key - Microsoft SQL / MS SQL Server

Hello, I am going to create new tables for a database. Wanted to know whether creation of IDENTITY Column and setting it as a primary key is good OR Creation of column with Datatype UniqueID and setting a default with NEWID() and then setting the column as Primary key is good. Which would be beneficial on the ground of performance. Thanks Anand...

  1. #1

    Default Creation of primary key

    Hello,
    I am going to create new tables for a database.
    Wanted to know whether creation of IDENTITY Column and
    setting it as a primary key is good
    OR
    Creation of column with Datatype UniqueID and setting a
    default with NEWID() and then setting the column as
    Primary key is good.

    Which would be beneficial on the ground of performance.
    Thanks
    Anand

    Anand Guest

  2. #2

    Default Re: Creation of primary key

    I don't have any performance data, but more on the usability part, the
    general practice is to create an IDENTITY column and have it auto-increment.
    It makes your PK's small and indexing them is also easier. Also, relating
    them with FK's is easier too (since you need not store GUID's in all your
    tables). Quering for information based on PK becomes easier if you use
    IDENTITY columns. Some application have the concept of "prefixing"
    information along with the PK too (for example, an order number might be
    ORD001, where 1 is the primary key and the ORD00 is prefixed before it).
    These sort of operations are easier with IDENTITY columns.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Anand" <org> wrote in message
    news:049b01c34b98$343274e0$gbl... 


    SriSamp Guest

  3. #3

    Default Re: Creation of primary key

    >> Wanted to know whether creation of IDENTITY Column and setting it as a
    primary key is good <<

    Before jumping into conclusion about identity columns & guids, one has to
    understand what a key is and what the practical considerations in choosing a
    key are.

    A key is an attribute or set of attributes which can uniquely identify an
    entity in the conceptual model. Loosely put, a column or set of columns
    which can uniquely identify a row in a table is defined as a key. In
    reality, an entity may have many such candidates for key ( hence called
    candidate key ), however for practical purposes one out of those candidate
    keys has to be considered primal ( hence called primary key ). The
    consideration should include :
    i. Familiarity - meaningful to the user
    ii. Stability - should not be altered frequently
    iii. Simplicity - so that relational operations are effective & efficient
    iv. Irreducibility/Minimality - no subset of the key should uniquely
    identify a row in the table & this is also a derivation to 2NF.

    Having said that, based on your business model & data requirements, an
    intelligent database design should find a middle ground by trading off
    certain characteristics without compromising the integrity of the data. And
    one such compromise is the logical surrogate key which defies the
    characteristic of familiarity but provides excellent stability and
    simplicity.

    One of the fundamental principles guiding relational design, is a concept
    called physical data independence, which can be simply put as, the users
    must be presented with a logical view of the data & need not deal with the
    physical implementation details. That is primarily why we deal with tables,
    columns, keys etc ( which is logical) instead of files, disk indices, row
    positions etc (which is physical). Thus you will see why and how a
    surrogate in a clean logical model, should also conform to physical data
    independence.

    Features like identity, GUID etc provide uniqueness, but they fail miserably
    as logical values, since they are created & implemented through physical
    mechanisms provided by the DBMS. Hence the values created by these
    mechanisms are not valid surrogates either, since by definition, surrogates
    must be logical. However, this is widely ignored by database implementers
    due to various reasons & identity columns as well as guids are used as
    logical values (!!) claiming them as surrogates.
     [/ref]

    That should be least of your considerations in key selection, since the
    primary goal of keys is entity identification which is followed by
    preservation of data integrity. Performance is entirely dependent upon the
    physical implementation of a database. Obviously values generated and tied
    directly to the physical model tend to be better performing (though not as a
    rule). However, if you are comparing the general efficiency of queries
    involving identity columns & GUIDs, identity columns may be faster, perhaps
    due to its numeric type & smaller size, which generally facilitate faster
    disk access.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

Similar Threads

  1. Primary Scratch & Windows Primary Paging file?
    By Tommy Oberst in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 14
    Last Post: April 15th, 10:26 PM
  2. retrive primary key
    By Jorntk in forum PHP Development
    Replies: 1
    Last Post: April 25th, 08:05 PM
  3. Add primary key
    By Simon Gorski in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: January 20th, 11:00 PM
  4. Get Primary Key
    By Leon Shaw in forum ASP.NET General
    Replies: 2
    Last Post: July 21st, 12:03 PM
  5. Huge primary key creation
    By RR in forum Oracle Server
    Replies: 3
    Last Post: January 9th, 02:47 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