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

• Aaron Bertrand: Slightly simpler to only define the repeated portion of the index once, and less prone to mistakes if you later decide to change the name/columns etc. (you might forget to change it in both places). SET @sql = N'CREATE ...'; IF (version check) SET @sql = @sql + ' WHERE ...';
• Massimo: Why not just exec '<SQL Query>'? What's the difference with exec sp_executeSQL '<Same SQL Query>'?
• vonPryz: @Massimo exec is a sledgehammer that executes anything that goes. sp_executesql is more strict about the input. See more at SqlSkills' blog

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!

• Massimo: The problem is, the index creation command isn't even considered valid syntax if the version is not high enough, thus the whole query is refused.