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.

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.

Haste makes waste – this proverb holds true today also. However, if you think about it hard enough, you may find that haste also teaches us new things, or as it happened recently with me, reminds us of things we already know.

I generally prefer the use of the keyboard to perform most of my tasks because I find that it works much faster for me. Therefore, to execute a query, I use the F5 key instead of pressing the “Execute” button. A couple of days ago, I was working on a query to diagnose a data issue and in my hurry to execute it, I pressed the F5 and the “5” key together (the number 5 is just below the F5 key if you observe).

Obviously, GIGO (“Garbage-In-Garbage-Out”) happened. The query was accidentally modified and this is what was seen:

USE AdventureWorks2008R2
GO
SELECT BusinessEntityID,
       OrganizationLevel,
       5JobTitle              -- Notice the accidental placement of "5" here
FROM HumanResources.Employee
GO

So, What Happened?

What happened here is actually, quite simple. As you can see, SQL Server took the number “5” as the value, and the rest of the string as the column alias. This is because the column alias is a database object name and all object names must follow the rules identified for identifiers.

Database Identifiers

Object identifiers are used to uniquely identify the object. Identifiers comply with the following rules:

1. Identifiers must start with one of the following:
   • A letter as defined by Unicode standard 3.2 (includes a-z, A-Z and letter characters from other languages)
   • The underscore sign (_), at sign (@) or the number sign (#)
2. Subsequent characters can be:
   • Letter as defined by Unicode standard 3.2
   • Decimal numbers from basic Latin or other national scripts
   • The underscore sign (_), at sign (@), the number sign (#) or the dollar sign ($)

3. The identifier must not be a T-SQL reserved keyword

4. Embedded spaces or special characters are not allowed

5. Supplementary characters are not allowed

In our case…

In our case, the column alias was accidentally altered to become “5JobTitle”. Because a column alias is an identifier and cannot start with a number, SQL Server automatically set the value to be “5” and the column name to be “JobTitle”.

Possible solutions

The solutions are many, including:

1. Remove the erroneous number in the column alias
2. Use the AS keyword and define a column alias by using:
   • Double quotes

     USE AdventureWorks2008R2
     GO
     SELECT BusinessEntityID AS "BusinessEntityID",
            OrganizationLevel AS "OrganizationLevel",
            JobTitle AS "JobTitle"
     FROM HumanResources.Employee
     GO

   • Square braces

     USE AdventureWorks2008R2
     GO
     SELECT BusinessEntityID AS [BusinessEntityID],
            OrganizationLevel AS [OrganizationLevel],
            JobTitle AS [JobTitle]
     FROM HumanResources.Employee
     GO

Reference: http://msdn.microsoft.com/en-us/library/ms175874.aspx

So, that was a reminder for me to slow down before I furiously start typing on the keyboard.

Until we meet next time,

Be courteous. Drive responsibly.

Haste makes waste – this proverb holds true today also. However, if you think about it hard enough, you may find that haste also teaches us new things, or as it happened recently with me, reminds us of things we already know.

I generally prefer the use of the keyboard to perform most of my tasks because I find that it works much faster for me. Therefore, to execute a query, I use the F5 key instead of pressing the “Execute” button. A couple of days ago, I was working on a query to diagnose a data issue and in my hurry to execute it, I pressed the F5 and the “5” key together (the number 5 is just below the F5 key if you observe).

Obviously, GIGO (“Garbage-In-Garbage-Out”) happened. The query was accidentally modified and this is what was seen:

~~~USE AdventureWorks2008R2
GO
SELECT BusinessEntityID,
OrganizationLevel,
5JobTitle — Notice the accidental placement of "5" here
FROM HumanResources.Employee
GO~~~

So, What Happened?

What happened here is actually, quite simple. As you can see, SQL Server took the number “5” as the value, and the rest of the string as the column alias. This is because the column alias is a database object name and all object names must follow the rules identified for identifiers.

Database Identifiers

Object identifiers are used to uniquely identify the object. Identifiers comply with the following rules:

1. Identifiers must start with one of the following:
   • A letter as defined by Unicode standard 3.2 (includes a-z, A-Z and letter characters from other languages)
   • The underscore sign (_), at sign (@) or the number sign (#)
2. Subsequent characters can be:
   • Letter as defined by Unicode standard 3.2
   • Decimal numbers from basic Latin or other national scripts
   • The underscore sign (_), at sign (@), the number sign (#) or the dollar sign ($)

3. The identifier must not be a T-SQL reserved keyword

4. Embedded spaces or special characters are not allowed

5. Supplementary characters are not allowed

In our case…

In our case, the column alias was accidentally altered to become “5JobTitle”. Because a column alias is an identifier and cannot start with a number, SQL Server automatically set the value to be “5” and the column name to be “JobTitle”.

Possible solutions

The solutions are many, including:

1. Remove the erroneous number in the column alias
2. Use the AS keyword and define a column alias by using:
   • Double quotes
     ~~~USE AdventureWorks2008R2
     GO
     SELECT BusinessEntityID AS "BusinessEntityID",
     OrganizationLevel AS "OrganizationLevel",
     JobTitle AS "JobTitle"
     FROM HumanResources.Employee
     GO~~~
   • Square braces
     ~~~USE AdventureWorks2008R2
     GO
     SELECT BusinessEntityID AS [BusinessEntityID],
     OrganizationLevel AS [OrganizationLevel],
     JobTitle AS [JobTitle]
     FROM HumanResources.Employee
     GO~~~

Reference: http://msdn.microsoft.com/en-us/library/ms175874.aspx

So, that was a reminder for me to slow down before I furiously start typing on the keyboard.

Until we meet next time,

Be courteous. Drive responsibly.