Two IDs, One database and a Magic Number (and maybe even an empty string)

G’day,

I noticed something odd recently when looking at sys.dm_exec_requests. Specifically the system processes listed in that DMV – well at least I thought it was odd :)

Normally, I join with sys.dm_exec_sessions and filter out anything that is a system process (WHERE [sys].[dm_exec_sessions].[is_user_process] = 1 )

However, this time I quickly wrote the query and didn’t filter anything, I was actually looking for connections to a particular database when I noticed that the system processes where reporting their database_id as either 1 or 0 – interesting.

Buy, hey, see it for yourself

Run the query below -

USE [master];
GO
select
[der].[session_id],
DB_NAME([der].[database_id]) [Database Name],
[der].[database_id],
[des].[is_user_process],
[der].[command]
from
[sys].[dm_exec_requests] [der]
JOIN [sys].[dm_exec_sessions] [des]
ON
[der].[session_id] = [des].[session_id]
WHERE
[des].[is_user_process] = 0
ORDER BY
[der].[session_id] ASC;
GO

The number of rows containing a database_id of 0 and 1 for system processes actually vary on different instances.
Now try this

USE [master];
GO
select db_name(0) [db_name(with arg)] , 0 [db_name() argument]
UNION ALL
select db_name(1) , 1
GO

and I get “master” reported in both cases – so initially I thought that giving the db_name() function a value of 0 always returned ‘master’ – not so. – it actually is just reporting the current database, it just so happens that I was in master at the time

SELECT DB_NAME(0) will return the name of the current database.
SELECT DB_NAME() will also return the name of the current database.
SELECT DB_NAME(”) will also return the name of the current database – note the empty string :)
while
SELECT DB_NAME(‘sdfdf’) – replace ‘sdfdf’ with any string you like (even a valid database name) and it returns an expected conversion error

so, when you see a database id of 0 in sys.dm_exec_request - it really means “the database you are running in

0 (zero) seems to be a magic number that means “the current database” – try it for yourself, change the database name in the USE statement to anything you like

USE [tempdb];
GO
select 
	db_name(0) [db_name(with arg)] , 
	0 [db_name() argument], 
	db_name() [Current Database],
	CASE (db_name())
		WHEN db_name(0) THEN 'Same'
		ELSE 'Different'
	END [Name correspondes as]
UNION ALL
select 
	db_name(1) , 
	1 , 
	db_name(),
	CASE (db_name())
		WHEN db_name(1) THEN 'same'
		ELSE 'Different'
	END
GO

Have a great day

Cheers

Martin.

About Martin Catherall

Martin Catherall is Senior SQL consultant at SQL Down Under, based in Melbourne, Australia. Martin is also a Microsoft Data Platform MVP and Regional Mentor (RM) for the Professional Association for SQL Server. Prior to relocating to Australia he was extremely active in the Christchurch, New Zealand data community - founding the local SQL Server user group and organizing SQL Saturday Christchurch - which later became SQL Saturday South Island. He likes learning interesting stuff about processing and storing data and passing on his knowledge. In his spare time he likes to learn guitar and hang out with his two young sons Callum Glen and Robert Michael.