ALTER DATABASE SET COMPATIBILITY_LEVEL runs irrespective of IF EXIST condition

  • Hi,

    Problem:

    I want to set compatibility_level only when it is greater than 110.

    Solution:

    Select the compatibility level and if it is greater than 110, I alter database set compatibility level=110

    ISSUE

    Irrespective of IF Exist statement the alter database statement is executed all the time.

    Here is the sql statement

    IF EXISTS (

    SELECT * FROM sys.databases where compatibility_level >110 AND name='mydatabase'

    )

    BEGIN

    ALTER DATABASE mydatabase SET COMPATIBILITY_LEVEL = 110

    END

    What is that I am missing here ??

    please support.

  • Nothing Wrong with Your statement.

    What do you mean by executing all the time ?

  • I mean, even if there are no record found for "query" under IF EXISTS (query), the alter database get executed.

    Query returns zero record but alter database statement gets executed.

  • Quick suggestion, do this instead

    😎

    USE master;

    GO

    IF (SELECT

    SD.compatibility_level

    FROM sys.databases SD

    WHERE SD.name = N'mydatabase'

    ) > 110

    BEGIN

    ALTER DATABASE mydatabase SET COMPATIBILITY_LEVEL = 110

    END

  • I would also check how you are confirming that the ALTER DATABASE command is actually being executed.

    I tested your code to see if there were any strange quirks with how that was run, and it runs as expected. If mydatabase does not have a compatibility higher than 110, the ALTER DATABASE does not fire.

    My best guess based on the information given is that the ALTER DATABASE is indeed not firing, and that you're being misled into thinking that it did. How are you checking that the ALTER DATABASE ran?

    Cheers!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply