Practical used of database id

Question

Practical used of database id

Shadab Shah

SSCarpal Tunnel

Points: 4759
More actions
September 6, 2012 at 4:15 am

#280822

Hi,
Recently i came across something like databaseid. I search for it on internet but could not come up with its practical used.

Viewing 6 posts - 1 through 5 (of 5 total)

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

BrainDonor SSCoach Points: 19254 More actions · Answer 1

If you mean database_id, it is used within a wide variety of areas of SQL Server to identify a database. The name of a database can be changed, but the ID will remain the same.

select *

from sys.databases;

Steve Hall
Linkedin
Blog Site

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

Shadab Shah (9/6/2012)
Hi,
Recently i came across something like databaseid. I search for it on internet but could not come up with its practical used.

What do you call by "practical used"?

I hope you do refer to database_id from sys.databases (or the one returned by db_id(), which is the same...)

It's used by SQL Server internally to uniquely identify the database within server instance:

http://msdn.microsoft.com/en-us/library/ms178534.aspx

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

Each database has a unique identifier. The system databases are always 1-4: Master: 1, TempDB: 2, model: 3, msdb: 4. You see get them all with:

select database_id, name from sys.databases

DBAs use the database_id all the time for writing maintenance queries. For example, the query below will get you the size of your databases:

;WITH dbs([dbid],size) AS

(

SELECT database_id,

SUM(size)/128

FROM sys.master_files

GROUP BY database_id

)

SELECT d2.name db,

d1.size [size(mb)]

FROM dbs d1

JOIN sys.databases d2 ON d1.dbid=d2.database_id

I recently drafted a post about this.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Shadab Shah SSCarpal Tunnel Points: 4759 More actions · Answer 4

Shadab Shah

SSCarpal Tunnel

Points: 4759

September 6, 2012 at 10:24 pm

#1534835

Thank You

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

Shadab Shah (9/6/2012)
Thank You

no problem. 😀

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001