Is there a way to execute SQL statement based on SQL Server version?

Question

I would like to create computed index on a table if SQL Server is 2008 or newer and a simple index if SQL Server is 2005 or older:

-- check for sql server version
if (select cast(left(cast(serverproperty('productversion') as varchar), 4) as decimal(5, 3))) >= 10 
        CREATE unique nonclustered index ix1_table
            ON table (column1, column2)
            WHERE column1 is not null and column2 is not null
        ELSE
            CREATE nonclustered index ix1_table
                ON table (column1, column2)

The problem is that the whole statement is evaluated and on SQL Server 2005 this throws an error:

Incorrect syntax near the keyword 'WHERE'.

Is it possible to somehow create different index based on SQL Server version?

Answer 1

You can use dynamic SQL

I mean first check the version

Then build your SQL statement using a string variable, for example nvarchar(max)

Then execute it by sp_executeSQL

I think following script can work for this task

-- check for sql server version
declare @sql nvarchar(max)
if (select cast(left(cast(serverproperty('productversion') as varchar), 4) as decimal(5, 3))) >= 10 

set @sql = N'CREATE unique nonclustered index ix1_table ON [table] (column1, column2)
        WHERE column1 is not null and column2 is not null'

        ELSE

set @sql = N'CREATE nonclustered index ix1_table ON [table] (column1, column2)'


exec sp_executeSQL @sql

Answer 2

As you guessed, the problem with your approach is the query syntax, which is considered invalid if the SQL Server version is not high enough, thus leading to the whole query being refused, even if that code would actually never be executed.

You can use the 'EXECUTE' (or 'EXEC') command to bypass this check:

IF <Version Check>
    EXECUTE('Index creation command for SQL Server 2008')
ELSE
    EXECUTE('Index creation command for SQL Server 2005')

The argument to an 'EXECUTE' statement is not evaluated for correctness beforehand; it's executed as is whenever the statement is reached (potentially incurring errors at runtime).

Full info here.

Answer 3

Sure, you can do a check for @@VERSION and do conditional code based on that.

If @@VERSION like 'Microsoft SQL Server 2008%' THEN
BEGIN
--'stuff here'
END

You could do it as a case statement, but since @@VERSION returns service pack information it might be providing TMI when all you want is to check for a supported feature.

Good luck!