What is Source_Database_ID in Sys.Databases?- Interview Question of the Week #232

Question: What is Source_Database_ID in Sys.Databases and why it is mostly NULL?

Answer: Indeed a very interesting question asked to me by attendees of SQL Server Performance Tuning Practical Workshop – Recorded Classes.

What is Source_Database_ID in Sys.Databases?- Interview Question of the Week #232 Source_Database_ID-800x217

Let us understand the question in detail. Whenever the user was running the following query, she was always noticing the column Source_Database_ID with only NULL values. The name of the column is source database id but she was not able to understand how the newly created database by the user can have a source and hence the question was sent to me.

Let us see the query first.

SELECT name, source_database_id,
DB_NAME(source_database_id) SourceDatabase
FROM sys.databases
GO

In the above query, I have removed unnecessary columns and kept only relevant columns. Now if you notice that the column is source database id only contains NULL.

What is Source_Database_ID in Sys.Databases?- Interview Question of the Week #232 sysdatabase1

The column source database id will only have values when the database created is snapshot based on another database. In simple words, if I create a new database SQLAuthority_Snapshot based as a Snapshot of the SQLAuthority database, the column source database id of the snapshot database will contain the id of the source database.

Let us run the following query to understand our example.

CREATE DATABASE SQLAuthority
GO
CREATE DATABASE SQLAuthority_Snapshot ON
( NAME = SQLAuthority, FILENAME =
'D:\Data\WideWorldImporters_Snapshot.ss' )
AS SNAPSHOT OF SQLAuthority;
GO

Now once you run the above statement, it will create two databases 1) SQLAuthority and 2) SQLAuthority_Snashot which is based on the SQLAuthority database. Now run our earlier script once again.

SELECT name, source_database_id, 
DB_NAME(source_database_id) SourceDatabase
FROM sys.databases
GO

What is Source_Database_ID in Sys.Databases?- Interview Question of the Week #232 sysdatabase2

In the resultset, you can clearly see that we have a new database which is snapshot showing the course database id of its source database. I hope you find this blog post interesting. Let me know if you have other similar questions, I would have happy to answer them on this blog.

If you are interested, you can sign up for my SQL Server Performance Tuning Practical Workshop. On the popular demand of everyone, I am going to make this class available for everyone for Instant Learning. Here is the video recording of the class, at your own comfort you can now watch it from office, home or on mobile during your daily commute.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Snapshot, SQL High Availability, SQL Scripts, SQL Server, SQL System Table
Previous Post
What is Faster, SUM or COUNT? – Interview Question of the Week #231
Next Post
How to Find Definition of Computed Column in SQL Server using T-SQL? – Interview Question of the Week #233

Related Posts

Leave a Reply