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.

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.

SQL Server 2012 (“Denali”) introduces many new features directed towards improving server efficiency and availability and developer/administrator productivity. At the same time, it needs to do away with the past “luggage”. These are features supported by the older versions of SQL Server, that are either no longer in popular use or violate the business rules as T-SQL enhancements made in the newer releases.

Compatibility levels

Every database within Microsoft SQL Server is characterized by something called as the “Compatibility Level”. The compatibility level is primarily used to allow for backward compatibility as it determines the database behaviour with respect to a specific SQL Server version. For example, if you would like to use a SQL Server 2005 specific database behaviour on a SQL Server 2008 R2 instance, you would need to set the compatibility level to “90”.

Trivia: The compatibility level is essentially the major build# of the product. Because SQL Server 2000 was build #80, the compatibility level for SQL Server 2000 databases is 80. Similarly, for SQL Server 2008, the build# is 10.0, and therefore the compatibility level is 100 (compatibility level of a newer release cannot be less than that of the previous releases). For SQL Server 2012 databases, the compatibility level is 110.

Removal of support for COMPATIBILITY_LEVEL 80

SQL Server 2008 and SQL Server 2008R2 were the last versions to support a database compatibility level of 80, i.e. a SQL Server 2000 database. Those who came in late would recall that SQL Server 2005 was a major departure from the architecture of the older SQL Server 2000. This meant that in order to provide backward compatibility support, the SQL Server code would need to carry a lot of additional code which hardly anyone would ever use (in order to leverage the latest features introduced in higher releases, the compatibility level would need to be changed).

In-line with the product’s policy to support 2 prior releases, starting SQL Server 2012, the compatibility level of 80 is no longer supported.

SQL Server 2008 – supported compatibility levels

SQL Server 2012 – supported compatibility levels

Attempting to use T-SQL to change the Compatibility level results in the following error:

ALTER DATABASE AdventureWorks2008R2
SET COMPATIBILITY_LEVEL=80

Msg 15048, Level 16, State 1, Line 1

Valid values of the database compatibility level are 90, 100, or 110.

So, if you are still using a SQL Server 2000 database, please upgrade!

More about the compatibility levels supported by SQL Server 2012 (“Denali”)

To know more about the compatibility levels supported by SQL Server 2012, please visit: http://msdn.microsoft.com/en-us/library/bb510680.aspx

Until we meet next time,

Be courteous. Drive responsibly.

SQL Server 2012 (“Denali”) introduces many new features directed towards improving server efficiency and availability and developer/administrator productivity. At the same time, it needs to do away with the past “luggage”. These are features supported by the older versions of SQL Server, that are either no longer in popular use or violate the business rules as T-SQL enhancements made in the newer releases.

Compatibility levels

Every database within Microsoft SQL Server is characterized by something called as the “Compatibility Level”. The compatibility level is primarily used to allow for backward compatibility as it determines the database behaviour with respect to a specific SQL Server version. For example, if you would like to use a SQL Server 2005 specific database behaviour on a SQL Server 2008 R2 instance, you would need to set the compatibility level to “90”.

Trivia: The compatibility level is essentially the major build# of the product. Because SQL Server 2000 was build #80, the compatibility level for SQL Server 2000 databases is 80. Similarly, for SQL Server 2008, the build# is 10.0, and therefore the compatibility level is 100 (compatibility level of a newer release cannot be less than that of the previous releases). For SQL Server 2012 databases, the compatibility level is 110.

Removal of support for COMPATIBILITY_LEVEL 80

SQL Server 2008 and SQL Server 2008R2 were the last versions to support a database compatibility level of 80, i.e. a SQL Server 2000 database. Those who came in late would recall that SQL Server 2005 was a major departure from the architecture of the older SQL Server 2000. This meant that in order to provide backward compatibility support, the SQL Server code would need to carry a lot of additional code which hardly anyone would ever use (in order to leverage the latest features introduced in higher releases, the compatibility level would need to be changed).

In-line with the product’s policy to support 2 prior releases, starting SQL Server 2012, the compatibility level of 80 is no longer supported.

SQL Server 2008 – supported compatibility levels

SQL Server 2012 – supported compatibility levels

Attempting to use T-SQL to change the Compatibility level results in the following error:

~~~ALTER DATABASE AdventureWorks2008R2
SET COMPATIBILITY_LEVEL=80~~~

Msg 15048, Level 16, State 1, Line 1

Valid values of the database compatibility level are 90, 100, or 110.

So, if you are still using a SQL Server 2000 database, please upgrade!

More about the compatibility levels supported by SQL Server 2012 (“Denali”)

To know more about the compatibility levels supported by SQL Server 2012, please visit: http://msdn.microsoft.com/en-us/library/bb510680.aspx

Until we meet next time,

Be courteous. Drive responsibly.

As we know, SQL Server 2012 (code named “Denali”) was launched this recently. A lot of content is currently available on the Internet that describes the new functionalities and features introduced in SQL Server 2012. However, if you are an ISV and want to certify your database against SQL Server 2012, you need to also know about the deprecated (i.e. removed) features of SQL Server 2012 and their corresponding replacements, if any.

I am therefore presenting a series on the Deprecated Features of SQL Server 2012.

WITH [MEDIA] PASSWORD option for database/log backup & restore

SQL Server 2008 and below allow for configuring a weak password on the entire backup set or media set to prevent unauthorized restores. This password does not protect against overwriting the backup or reading of the backup data.

In order to apply the password, a user would use a syntax similar to the following:

BACKUP DATABASE AdventureWorks2008R2 TO DISK = ‘C:DataAdv2K12.bak’

WITH PASSWORD = ‘Sql@12’

However, starting SQL Server 2012, using this statement will return the following error:

Msg 3032, Level 16, State 2, Line 1
One or more of the options (password) are not supported for this statement. Review the documentation for supported options.

The message clearly states that the option to specify the password for the backup set is no longer supported by SQL Server.

Replacement

No replacement is available from Microsoft against the removal of the feature.

An easy way to identify which feature is deprecated

If you would like to know if your application uses any feature that has been marked as “Deprecated” and if you are as big a fan of the SQL Server Profiler (some shockers on this coming soon) as I am, you can use the “Deprecation” event class when running a trace against your database. Read more about using the “Deprecation” event class here: http://beyondrelational.com/modules/2/blogs/77/posts/11375/sql-server-profiler-part-4-review-t-sql-code-to-identify-objects-no-longer-supported-by-microsoft-de.aspx

Until we meet next time,

Be courteous. Drive responsibly.