How to Test Existing T-SQL Code Before Changing the Compatibility Level

You can easily determine whether existing stored procedures, UDFs and views can work in a higher (or lower, if you wish) compatibility level. The only tool that you need for this is SSMS and a copy of your production database on a development server. Here are the steps:

  1. In SQL Server Management Studio, click Stored Procedures and open the Object Explorer Details window (F7)
  2. Select all SPs, right-click and select “CREATE TO” then “New Query Editor Window”. This will produce a single script to re-create all of your existing stored procedures. Save that script before you proceed to the next step.
  3. Now go back to the Object Explorer Details window, select all SPs again and then Delete them all. (Feels good, huh? 🙂 )
  4. Next, go and set the database compatibility level to the desired value: open the database properties, go to Options, set the compatibility level to another value, click OK
  5. Now open the script you saved in the step 1 and execute it. If it finishes without any errors then your code doesn’t require any changes. Otherwise it will show errors indicating which parts are not supported, for example:
USE [AdventureWorks2012]
GO

SELECT TOP 10 * FROM [Production].[BillOfMaterials]

WITH myCTE AS (
        SELECT TOP 10 * FROM [Production].[Product]
        )
SELECT * FROM myCTE

Will throw the following error:

Msg 336, Level 15, State 1, Line 4
Incorrect syntax near ‘myCTE’. If this is intended to be a common table expression, you need to explicitly terminate the previous statement with a semi-colon.

The error means that in this compatibility level (90 and up) the statement before the CTE must end with a semicolon. In compatibility level 80 that was not mandatory.

The same procedure can be used for UDFs and views, too. The method will show you exactly what you need to change in your code and where. This is especially helpful when your database has a large number of database objects.

Advertisements

One thought on “How to Test Existing T-SQL Code Before Changing the Compatibility Level

  1. Pingback: SQL New Blogger Digest – Week 2 | The Rest is Just Code

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s