Table Without a Primary key?

  • Welsh Corgi

    SSC Guru

    Points: 116520

    GilaMonster (7/15/2011)


    The one case that comes to mind is a staging table. If I'm importing data from some other system and I know that it's got data integrity issues, I'll use a staging table with no keys at all. No benefits to having keys on the table and having them could cause the data import to fail.

    Excellent example.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Subrata Bauri

    SSCommitted

    Points: 1503

    GilaMonster (7/15/2011)


    The one case that comes to mind is a staging table. If I'm importing data from some other system and I know that it's got data integrity issues, I'll use a staging table with no keys at all. No benefits to having keys on the table and having them could cause the data import to fail.

    Dear Sir,

    I will add a Column Like Entry No. and define it as Primary Key in the Table. I will increment the value of this Column at the Time of Data Insertion in this CASE.

    Advantage(s) :

    1. Easy to Count how many rows(Records) are there.

    2. Easy to differentiate a row due this Entry No. (Suppose Others Rows have the Same Value).

    Note :- If adding a New Column is not disturbing any thing.

  • Welsh Corgi

    SSC Guru

    Points: 116520

    Subrata Bauri (7/15/2011)


    GilaMonster (7/15/2011)


    Dear Sir,

    I will add a Column Like Entry No. and define it as Primary Key in the Table. I will increment the value of this Column at the Time of Data Insertion in this CASE.

    Not everyone on this forum is a Sir and quite frankly I know of some very smart Ladies that are the most bulbs on the Christmas Tree. Sorry not politically correct.

    I missing something but you blew me away with the sugesstion to increment the value of the primary key.:-D

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Sean Lange

    SSC Guru

    Points: 286536

    Advantage(s) :

    1. Easy to Count how many rows(Records) are there.

    This really won't let you get an accurate count. It will work until you delete a record. If you want an arbitrary number as your primary key use an identity. Trying to maintain your own key like this is not worth the hassles of making it work correctly. You have to take into account so many things. Just don't do it. Set a int/bigint column as identity and move on.

    2. Easy to differentiate a row due this Entry No. (Suppose Others Rows have the Same Value).

    That is pretty much the definition of a primary key. You can now identify the row by a unique value.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Welsh Corgi (7/15/2011)


    Not everyone on this forum is a Sir and quite frankly I know of some very smart Ladies that are the most bulbs on the Christmas Tree.

    Definitely not a sir here. But I'm so used to being called 'sir' that it really doesn't bother me any longer.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail Shaw

    SSC Guru

    Points: 1004474

    Subrata Bauri (7/15/2011)


    I will increment the value of this Column at the Time of Data Insertion in this CASE.

    If you're doing an INSERT.. SELECT, or bulk-insert, all the rows will have the same insert date/time. Manually managing an incrementing column is a nightmare, seriously there are better things to spend a week or so on.

    1. Easy to Count how many rows(Records) are there.

    Primary key has no advantages there. Want to count rows - select count(*).

    2. Easy to differentiate a row due this Entry No. (Suppose Others Rows have the Same Value).

    Supposing I wanted something like that I could use a row_number or an identity without having to make it the primary key. With a staging table that I'm going to load, clean and truncate there are few advantages to bothering with a primary key at all.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Jayanth_Kurup

    SSC-Insane

    Points: 22967

    GilaMonster (7/15/2011)


    Jayanth_Kurup (7/15/2011)


    However the first can be achieved simply by adding a identity column while the second is driven by the kind of data being inserted into the table.

    Identities alone don't prevent duplicates. I can insert a specific value into an identity column multiple times, SQL won't complain. To enforce uniqueness you need a primary key, unique constraint or unique index.

    Yes we are on the same page , The primary key enforces uniqueness on the columns which are part of its definition. I never suggested adding a primary key on the identity column would prevent duplicates from being inserted in other column. Or that an indentity column would maintain uniqueness on its own without a primary key.

    The same applies for unique indexes as well , i figured it didn't need to be called out separately.

    Jayanth Kurup[/url]

  • Jayanth_Kurup

    SSC-Insane

    Points: 22967

    Subrata Bauri (7/15/2011)


    Jayanth_Kurup (7/15/2011)


    Enforcing primary key on a table is purely a design decision , while sql doesn't force it; it allows suitable substitutes in columns such as identity columns , timestamps etc .

    The role of the primary key is dual :-

    To help uniquely identify each row in the table

    To ensure duplicate rows are not inserted .

    However the first can be achieved simply by adding a identity column while the second is driven by the kind of data being inserted into the table.

    SQL will not intuitively decide one over the other for you.

    Dear sir,

    I know the role of the primary key in a Table very well.

    I think design a Table with the Primary key(s) is logically correct compare to without Primary key concept.

    Can you Show me that you are getting more advantage(s) from a non-primary Key Table compare to a Table which has primary key.

    Note :- Suppose both the Table have more or less same structure.

    Like Gail ( btw its Maam 🙂 ) said a staging table is a good example, similarly a mapping table much like the FACT in a DW would be another example.

    Jayanth Kurup[/url]

  • Jayanth_Kurup

    SSC-Insane

    Points: 22967

    Subrata Bauri (7/15/2011)


    GilaMonster (7/15/2011)


    The one case that comes to mind is a staging table. If I'm importing data from some other system and I know that it's got data integrity issues, I'll use a staging table with no keys at all. No benefits to having keys on the table and having them could cause the data import to fail.

    Dear Sir,

    I will add a Column Like Entry No. and define it as Primary Key in the Table. I will increment the value of this Column at the Time of Data Insertion in this CASE.

    Advantage(s) :

    1. Easy to Count how many rows(Records) are there.

    2. Easy to differentiate a row due this Entry No. (Suppose Others Rows have the Same Value).

    Note :- If adding a New Column is not disturbing any thing.

    [/b]

    All the above come down to the last line you mentioned , its purely a design choice.

    I.e if its not disturbing anything why not include it and while you choose to others may not.

    Jayanth Kurup[/url]

  • PaulB-TheOneAndOnly

    SSC Guru

    Points: 66773

    Subrata Bauri (7/15/2011)


    Why does SQL Server allow to create a Table Without a Primary key ?

    What is the intention behind this ?

    If you are not planning to enforce Referential Integrity on the particular table then PK is not needed.

    By the way, not only SQL Server allows for it - that's a feature of all RDBMS out there.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Subrata Bauri

    SSCommitted

    Points: 1503

    Sean Lange (7/15/2011)


    This really won't let you get an accurate count. It will work until you delete a record. If you want an arbitrary number as your primary key use an identity. Trying to maintain your own key like this is not worth the hassles of making it work correctly. You have to take into account so many things. Just don't do it. Set a int/bigint column as identity and move on.

    The PK will be int/bigint and it will increment by 1,2,3 ...

    The Last value of this column will show the numbers of the Total Records.

    That is pretty much the definition of a primary key. You can now identify the row by a unique value.

    Yes.

  • Subrata Bauri

    SSCommitted

    Points: 1503

    PaulB-TheOneAndOnly (7/16/2011)

    If you are not planning to enforce Referential Integrity on the particular table then PK is not needed.

    By the way, not only SQL Server allows for it - that's a feature of all RDBMS out there.

    Thanks !:-P

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Subrata Bauri (7/17/2011)


    Sean Lange (7/15/2011)


    This really won't let you get an accurate count. It will work until you delete a record. If you want an arbitrary number as your primary key use an identity. Trying to maintain your own key like this is not worth the hassles of making it work correctly. You have to take into account so many things. Just don't do it. Set a int/bigint column as identity and move on.

    The PK will be int/bigint and it will increment by 1,2,3 ...

    The Last value of this column will show the numbers of the Total Records.

    Assuming no deletes, assuming no mistakes in your assignment of it.

    Seriously, manually assigning an identity is a poor idea. Way too much that needs to be taken into account to get it right.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Jeff Moden

    SSC Guru

    Points: 996622

    Subrata Bauri (7/15/2011)


    Jayanth_Kurup (7/15/2011)


    Enforcing primary key on a table is purely a design decision , while sql doesn't force it; it allows suitable substitutes in columns such as identity columns , timestamps etc .

    The role of the primary key is dual :-

    To help uniquely identify each row in the table

    To ensure duplicate rows are not inserted .

    However the first can be achieved simply by adding a identity column while the second is driven by the kind of data being inserted into the table.

    SQL will not intuitively decide one over the other for you.

    Dear sir,

    I know the role of the primary key in a Table very well.

    I think design a Table with the Primary key(s) is logically correct compare to without Primary key concept.

    Can you Show me that you are getting more advantage(s) from a non-primary Key Table compare to a Table which has primary key.

    Note :- Suppose both the Table have more or less same structure.

    Welsh Corgi stated one of the best reasons. Staging table which may contain duplicate raw data that needs to be cleaned.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Malcolm Daughtree

    SSCrazy

    Points: 2373

    And some of us just like to denormalise just for the fun of it.

    😛

    Sorry couldn't help myself

Viewing 15 posts - 16 through 30 (of 33 total)

You must be logged in to reply to this topic. Login to reply