Post reply

Identity column as Primary Key - good or bad?

  • December 3, 2019 at 5:42 pm

    #3703912

    Jeff Moden wrote:

    Mr. Brian Gale wrote:

    Are you going to have more than 2 million records in the table? If so, probably want that to be a bigint.

    Heh... I know you meant BILLION but thought I'd point it out for any newbies that might read this. 😀

    Yes, that was a typo.  Had a few things break at work as I was typing up that reply, so had it half written and then came back to it later and tried to just continue my train of thought.  Yes, it should say billion.  Good catch with that!  Thanks 😀

    The other fun thing with Identity is that it doesn't get reset if you do a "DELETE [dbo].[table_name]".

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • December 3, 2019 at 6:06 pm

    #3703920

    Mr. Brian Gale wrote:

    Jeff Moden wrote:

    Mr. Brian Gale wrote:

    Are you going to have more than 2 million records in the table? If so, probably want that to be a bigint.

    Heh... I know you meant BILLION but thought I'd point it out for any newbies that might read this. 😀

    Yes, that was a typo.  Had a few things break at work as I was typing up that reply, so had it half written and then came back to it later and tried to just continue my train of thought.  Yes, it should say billion.  Good catch with that!  Thanks 😀

    The other fun thing with Identity is that it doesn't get reset if you do a "DELETE [dbo].[table_name]".

    That's actually a plus. 🙂 True names, and all that. Is the lack of resets also true for the TRUNCATE verb?

     

    • This reply was modified 4 years, 4 months ago by  roger.plowman. Reason: Spelling

  • December 3, 2019 at 6:58 pm

    #3703964

    roger.plowman wrote:

    That's actually a plus. 🙂 True names, and all that. Is the lack of resets also true for the TRUNCATE verb? 

    No - truncate will reset the identity value to the original seed value.  If the original seed was 1 - it will reset it back to 1, if the original seed value was 1000000 - it will reset it to 1000000.

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • December 3, 2019 at 9:05 pm

    #3704016

    Of course, you can do an immediate reseed to whatever number you want after that.  Heh... and if you want the table to handle up to 4 Billion rows, start the seed at minus 2 Billion.

    Other fun things about TRUNCATE vs DELETE is that TRUNCATE...

    1. Is almost instantaneous because it only deallocates pages instead of deleting data.
    2. Is mistakenly called minimally logged.  It's NOT minimally logged.  The page deallocations are fully logged and you don't need to meet any requirements for any of that to be true.
    3. Is thought unable to be rolled back.  It can be rolled back if with ROLLBACK if you have implicit transactions running (I recommend you almost never do that) or you use an explicit BEGIN TRANSACTION (or you  somehow stop the command in the middle while it's running, which is almost impossible for a human to be fast enough to do.
    4.  Cannot be used in the presence of Foreign Keys (something pointing at table even if that something has no rows).

    I know you folks already know that... just mentioning it for future newbies that might read this.

     

    --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.

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

  • December 3, 2019 at 10:58 pm

    #3704033

    >> THIS coming from the person that says he likes the MySQL "standard" of using YYYY-00-00 as the notation for whole years and YYYY-MM-00 for whole months. What a line of hooie. <<

    The MySQL proposal is well understood and not something I made up locally. I'm forcing this kind of thing only because SQL Server does not have INTERVAL temporal datatypes. The nice part about this is my lookup table of date ranges can be quickly converted when SQL Server comes up to standard. You might also remember that I was the guy that wrote "LEFT OUTER JOIN" Versions of the old *= extended equality outer joins so that when Microsoft caught up the rest of the world, they could simply uncomment my code.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • April 4, 2020 at 5:10 pm

    #3740451

    jcelko212 32090 wrote:

    >> THIS coming from the person that says he likes the MySQL "standard" of using YYYY-00-00 as the notation for whole years and YYYY-MM-00 for whole months. What a line of hooie. <<

    The MySQL proposal is well understood and not something I made up locally. I'm forcing this kind of thing only because SQL Server does not have INTERVAL temporal datatypes. The nice part about this is my lookup table of date ranges can be quickly converted when SQL Server comes up to standard. You might also remember that I was the guy that wrote "LEFT OUTER JOIN" Versions of the old *= extended equality outer joins so that when Microsoft caught up the rest of the world, they could simply uncomment my code.

    Understood but the trouble is that it violates the very ISO standards that you keep trumpeting about and, as of right now, there is no datatype that will support it as a date.  It will be (at best) a CHAR(10) and contains formatting for readability.  And, as of right now, it can't be used for interval calculations at all unless you modify the "00" parts of the string to something valid for such calculations.

    p.s.  You still owe me the ANSI or ISO standard/document number where it states your position of the YYYY-MM-DD format being the ONLY format supported in the "SQL Standard". 😉

    --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.

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

Viewing 6 posts - 31 through 35 (of 35 total)

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