Thursday, October 13, 2011

SQL Server Compatibility Level Testing

upgrading technologyI 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)
    , L.CourseID
    , L.LessonName
    , L.LessonDesc

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 

No comments: