QQ regarding DB_ID()

Question

Post reply

QQ regarding DB_ID()

YSLGuru

SSC-Insane

Points: 21661
More actions
January 26, 2022 at 11:47 pm

#3977699

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!

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

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

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)

David Burrows SSC Guru Points: 65143 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

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)

David Burrows SSC Guru Points: 65143 More actions · Answer 4

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.

ScottPletcher SSC Guru Points: 101120 More actions · Answer 5

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

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)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

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)