As I document the the database engine features that have been deprecated  in SQL Server 2012, one of the most important ones has to be the deprecation of DATABASEPROPERTY. I am yet to see a production code that does not use this function.

For those who came in late, DATABASEPROPERTY is a function that returns the named database property value for the specified database and property name. Here’s an example that uses the DATABASEPROPERTY function to see if the ANSI NULLS property is ON or OFF by default (runs on SQL Server 2008/R2).

SELECT DATABASEPROPERTY('AdventureWorks2008','IsAnsiNullDefault') AS IsAnsiNullDefault

/* Result Set
IsAnsiNullDefault
-----------------
0
*/

This function was quite old and did not cover some of the newly introduced properties like the comparison style for a collation. Also, the return type of the current function – DATABASEPROPERTY was an integer, which means that the function could not return a character or other data types.

Microsoft SQL Server 2005 therefore shipped with a new, replacement function – DATABASEPROPERTYEX. While the syntax is the same as the function it replaces, the return types are different. The extended, DATABASEPROPERTYEX now returns a sql_variant and therefore has the capability to return a string. We can therefore do the following which was not possible with the previous function.

SELECT DATABASEPROPERTYEX('AdventureWorks2012','Collation') AS Collation,
       DATABASEPROPERTYEX('AdventureWorks2012','Status') AS Status

/* Result Set
Collation                       Status
------------------------------  -------
SQL_Latin1_General_CP1_CI_AS    ONLINE
*/

I have already started replacing instances of the old DATABASEPROPERTY with DATABASEPROPERTYEX. Have you?

Reference:

• DATABASEPROPERTY: http://msdn.microsoft.com/en-us/library/ms176049.aspx
• DATABASEPROPERTYEX: http://msdn.microsoft.com/en-us/library/ms186823.aspx
• Pinal Dave (B|T)’s post: http://blog.sqlauthority.com/2012/03/29/sql-server-difference-between-databaseproperty-and-databasepropertyex/

Until we meet next time,

Be courteous. Drive responsibly.