Faster Way To Check If Database Exist

Sometimes we need to check if the database exist before perform certain task. There are a few ways to be done. Today, let's evaluate these options to see which may give us a better performance.

Since checking if database exist is a relatively fast and simple operation, Let's create 100 databases.

DECLARE @i int = 0;
DECLARE @sql nvarchar(max);

WHILE (@i < 100)
BEGIN
 SET @sql = N'CREATE DATABASE Test' + CAST(@i AS nvarchar);
 EXEC(@sql);

 SET @i = @i + 1;
END
GO

Using sys.databases,

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO

IF EXISTS(SELECT [state] FROM sys.databases d WHERE name = 'test50')
BEGIN
 SELECT 1;
END

Here is the statistics results,

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time: 
   CPU time = 47 ms, elapsed time = 61 ms.
Table 'sysdbreg'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 59 ms.

Using DATABASEPROPERTYEX,

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO

IF DATABASEPROPERTYEX('test50', 'status') IS NOT NULL
BEGIN
 SELECT 1;
END
GO

Here is the statistics results,

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 14 ms.

Using DB_ID,

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO

IF DB_ID('test50') IS NOT NULL
BEGIN
 SELECT 1;
END
GO

Here is the statistics results,

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Let's clean up our databases,

DECLARE @i int = 0;
DECLARE @sql nvarchar(max);

WHILE (@i < 100)
BEGIN
 SET @sql = N'DROP DATABASE Test' + CAST(@i AS nvarchar);
 EXEC(@sql);

 SET @i = @i + 1;
END
GO

From the above statistics, the sys.databases method consumed the most CPU and took the longest time as it has to do a index seek on the table as shown in the execution plan below. The DATABASEPROPERTYEX is retrieving the information from metadata which is much faster. Due to the returned result is in nvachar(128), it takes slightly longer than the DB_ID method which also using metadata, but returning result in int.

Do we really care for that milisecond difference? At least we know how they perform.