SQL SERVER – Difference between DATABASEPROPERTY and DATABASEPROPERTYEX

Posted by pinaldave on SQL Authority See other posts from SQL Authority or by pinaldave
Published on Thu, 29 Mar 2012 01:30:42 +0000 Indexed on 2012/03/29 11:35 UTC
Read the original article Hit count: 467

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 (http://blog.sqlauthority.com)


Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

© SQL Authority or respective owner

Related posts about PostADay

Related posts about sql