QQ regarding DB_ID()

  • What actions aside from deleting and restoring from backup, would cause the value of DB_ID() for a database to change for a DB hosted on SQL Server 2016 - Build# 13.0.6300.2 ?

    Kindest Regards,

    Just say No to Facebook!
  • I haven't tried it but, perhaps, Detaching/Attaching.  I think that Offline/Online would still keep the same value.

    --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)
    Intro to Tally Tables and Functions

  • Out of curiosity, did some tests

    Offline/Online - No Change

    Detach/Attach - No Change

    Delete/Restore - No Change

    However if you attach or restore a database with an id that is already in use then the server will allocate a new id.

     

    Far away is close at hand in the images of elsewhere.
    Anon.

  • David Burrows wrote:

    Out of curiosity, did some tests

    Offline/Online - No Change Detach/Attach - No Change Delete/Restore - No Change

    However if you attach or restore a database with an id that is already in use then the server will allocate a new id.

    That's possible.  If you still have the tests, try the same thing but create a small test database as the 2nd of the 3 steps and see.

    --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)
    Intro to Tally Tables and Functions

  • Not sure what you are alluding too but I tried all three options without creating a new database and in each case the id was reused. Only the following two tests resulted in a new id, ie the id already exists.

    1.

    Create database A

    Detach A

    Create database B (gets same id as A)

    Attach A, A gets new id

    2.

    Create database A

    Backup A

    Drop A

    Create database B (gets same id as A)

    Restore A, A gets new id

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Don't rely on the DB_ID() to reference a db.  Assume it could change at any time.

    If you'll notice, in its system tables, MS stores the db name rather than the id unless it is absolutely certain that the db_id would work correctly.

    If you need a permanent number for a db name, you'll need to create your own.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • David Burrows wrote:

    Not sure what you are alluding too but I tried all three options without creating a new database and in each case the id was reused. Only the following two tests resulted in a new id, ie the id already exists.

    1.

    Create database A Detach A Create database B (gets same id as A) Attach A, A gets new id

    2.

    Create database A Backup A Drop A Create database B (gets same id as A) Restore A, A gets new id

    Yep... that's what I meant.  Thanks, David.

    --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)
    Intro to Tally Tables and Functions

  • ScottPletcher wrote:

    Don't rely on the DB_ID() to reference a db.  Assume it could change at any time.

    If you'll notice, in its system tables, MS stores the db name rather than the id unless it is absolutely certain that the db_id would work correctly.

    If you need a permanent number for a db name, you'll need to create your own.

    You can rely on it for certain things.  For example, if you want to run sys.dm_db_index_physical_stats in the "current" database, DB_ID() is the way to go.

    I totally agree on the rest though.

    --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)
    Intro to Tally Tables and Functions

Viewing 8 posts - 1 through 7 (of 7 total)

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