SQL Server Compatibility Level Testing

I stumbled on interesting version compatibility issues while working with a database in SQL Server 2008R2. The database was originally developed in SQL Server 2000. It was upgraded to MSSQL 2008R2 as a part of a virtualization initiative, however, the Compatibility Level was still set to 80 (MSSQL 2000).

After seeing columns of type VARCHAR(MAX), it got me wondering about compatibility issues since there was no VARCHAR(MAX) in 2000.

Anomaly #1

Doing some research I found this post with a script for Testing all procs before upgrading to Compatibility Level 90. I ran it on the database (while still set to Compatibility Level 80) and it reported a stored procedure that had a syntax error that was not caught by the syntax checker in MSSQL 2000, 2005, or 2008R2. The SQL is below:

SELECT     ChangeLogID

    , CourseID

    , RecordID

    , RecordTable

    , ChangeType

    , ModifiedDateTime

    , ModifiedByEmpNo

    , CASE RecordTable 

        WHEN 'Course' THEN

        ( SELECT CourseName 

          FROM Course 

          WHERE CourseID = cl.CourseID)

        WHEN 'Lesson' THEN

        ( SELECT LessonName 

          FROM Lesson 

          WHERE LessonID = cl.LessonID)

         WHEN 'Content' THEN

        ( SELECT ContentName 

          FROM Content 

          WHERE ContentID = cl.ContentID)

        ELSE "None"

    END AS RecordName

FROM         ChangeLog cl

WHERE     (CourseID = @CourseID)

The error message was: Invalid column name 'None'. Note the quotation marks around “None” after the ELSE. Strangely, the stored procedure executes without error. It only detects the syntax error on CREATE, not when I ALTER the stored procedure.

Anomaly #2

Using the same database described above, the compatibility error detection script I used (while still having a Compatibility Level of 80) did not detect the error:

Msg 8127, Level 16, State 1, Procedure GetNextLessonForLessonNo, Line 49
Column "Lesson.LessonNo" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

The SQL generating this error is:

SELECT     TOP 1 MIN(L.LessonNo) AS LessonNo

    , L.LessonID

    , L.CourseID

    , L.LessonName

    , L.LessonDesc

    , COUNT(Content.ContentID) AS NContentPages

FROM     Course C 

         INNER JOIN Lesson L 

            ON C.CourseID = L.CourseID

         LEFT OUTER JOIN Content 

            ON L.LessonID = Content.LessonID

WHERE     (L.LessonNo > @LessonNo 

       AND L.CourseID = @CourseID)

GROUP BY L.LessonID

    , L.CourseID

    , L.LessonName

    , L.LessonDesc

ORDER BY L.LessonNo

This error is not detected in SSMS (in a query window, or by running the detection script) until the Database Compatibility Level is changed from 80 (MSSQL 2000) to 100 (MSSQL 2008). The issue can be resolved in SQL Server 2008 by adding L.LessonNo to the GROUP BY clause.

photo credit: somegeekintn / CC BY 2.0