Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

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?

Thanks

Dragan

share|improve this question
    
You have to use dynamic SQL. –  Kin Jan 29 at 13:02
add comment

3 Answers

up vote 4 down vote accepted

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
share|improve this answer
    
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 ...'; –  Aaron Bertrand Jan 29 at 15:17
    
Why not just exec '<SQL Query>'? What's the difference with exec sp_executeSQL '<Same SQL Query>'? –  Massimo Jan 29 at 17:46
add comment

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.

share|improve this answer
add comment

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!

share|improve this answer
1  
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. –  Massimo Jan 29 at 13:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.