kaisersoze Posted August 13, 2004 Posted August 13, 2004 SQL Server 2000. Is using auto increment number in a column a good practice. The table which contains auto increment number would be subjected to severe stress of deletes, updates and inserts during production phase. Quote Note: I think as a programmer not as a human, so use my answer at your will
Joe Mamma Posted August 13, 2004 Posted August 13, 2004 SQL Server 2000. Is using auto increment number in a column a good practice. The table which contains auto increment number would be subjected to severe stress of deletes, updates and inserts during production phase. whats the question? Quote Joe Mamma Amendment 4: The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized. Amendment 9: The enumeration in the Constitution, of certain rights, shall not be construed to deny or disparage others retained by the people.
Arch4ngel Posted August 13, 2004 Posted August 13, 2004 Well... it might be a good practice. Depend of your needs. Please be more specific. Quote "If someone say : "Die mortal !"... don't stay to see if he isn't." - Unknown "Learning to program is like going out with a new girl friend. There's always something that wasn't mentioned in the documentation..." - Me "A drunk girl is like an animal... it scream at everything like a cat and roll in the grass like a dog." - Me after seeing my girlfriend drunk and some of her drunk friend. C# TO VB TRANSLATOR
kaisersoze Posted August 16, 2004 Author Posted August 16, 2004 Is using auto increment number in a column a good practice? One of the table that i am working has no unique column. so, i crreated a column with datatype bigint and it has unique values. Approach was to have an auto increment number or in the stored procedure increment on to the max number and insert. Out of the 2 which one is good practice. Since the table is subjected to pyhsical deletes it is not adviced to have an auto increment number (based on my research). Justification for my research: scine it is an outo increment number and due to constant deletes there will be holes in the sequence of numbers. stop the server and strat the server (sql server) and try to insert few recordds. on version 7 i used to get errors. it did not happen on 2000 yet but i fear to use cause after development if an error occurs on production server it would take time to change. So, i want to find out how others are doing? Is using auto increment number in a column a good practice or in the SP increment and insert is good? Quote Note: I think as a programmer not as a human, so use my answer at your will
JABE Posted August 16, 2004 Posted August 16, 2004 If you're concerned about "holes" on your ID field (w/c IMHO you shouldn't be), then you can't use IDENTITY columns; an INSERT that violates a constraint, for instance, will increment the identity value even if the row wasn't actually inserted. However, if you're concerned about performance (the "hotspot" issue in SQL 7.0 caused by multiple INSERTs), then you'll be relieved to hear that this was corrected w/ SQL2K. Quote
Joe Mamma Posted August 17, 2004 Posted August 17, 2004 I believe that every table should have one field that represents a unique value. . . Be it time of initial entry, a Guid, or an Auto-Increment number. Row Id should not be confused with Primary Key. Primary Key is the field 'tuple' that represents a unique row. Many of my tables have a multi-field Primary Key and a system genetrated single field Surrogate Key that is the row id used as the foreign key in child tables. A mistake made by novices is to have a multifield primary key and then they propogate all those fields to child table relationships. . . ugh!!! My last job I overhauld had around ten tables that had multifield foreign keys with one extra field taged on to make a the primary key in that table. . . then they used that combined key as a forign key in another table and tagged another field on to make the primary in the next table in the hierarchy. What a mess!!! Quote Joe Mamma Amendment 4: The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized. Amendment 9: The enumeration in the Constitution, of certain rights, shall not be construed to deny or disparage others retained by the people.
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.