SQL SERVER – Difference between DATABASEPROPERTY and DATABASEPROPERTYEX

Earlier I asked a simple question on Facebook regarding difference between DATABASEPROPERTY and DATABASEPROPERTYEX in SQL Server. You can view the original conversation there over here. The conversion immediately became very interesting and lots of healthy discussion happened on facebook page. The best part of having conversation on facebook page is the comfort it provides and leaner commenting interface.

Question

Question from SQLAuthority.com: What is the difference between DATABASEPROPERTY and DATABASEPROPERTYEX in SQL Server?

Answer

Answer from Rakesh Kumar: DATABASEPROPERTY is supported for backward compatibility but does not provide information about the properties added in this release. Also, many properties supported by DATABASEPROPERTY have been replaced by new properties in DATABASEPROPERTYEX.- source (MSDN).

Answer from Alphonso Jones: The only real difference I can see is one, the number of properties contained and the other is that EX returns a sql_variant while DATABASEPROPERTY returns only int.

Answer from Ambati Venkatasiva: Both are system meta data functions. DATABASEPROPERTYEX Returns the current setting of the specified database option. DATABASEPROPERTYEX returns the sq-varient value and DATABASEPROPERTY returns integer value.

Answer from Rama Sankar Molleti: 

Here is the best example about databasepropertyex

SELECT DATABASEPROPERTYEX('dbname', 'Collation')


Result
SQL_1xCompat_CP850_CI_AS

Whereas with databaseproperty it retuns nothing as the return type for this
is integer. Sql_variant datatype stores values of various sql server
supported datatypes except text, ntext, image and timestamp.

Answer from Alok Seth: 

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Status')
DatabaseStatus_DATABASEPROPERTYEX
GO
--Result - ONLINE
SELECT DATABASEPROPERTY('AdventureWorks', 'Status')
DatabaseStatus_DATABASEPROPERTY
GO
--Result - NULL

Summary

Use DATABASEPROPERTYEX as it is the only function supported in future version as well it returns status of various database properties which does not exists with DATABASEPROPERTY.

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