Skip to main content
Database Administrators

Return to Question

deleted 20 characters in body; edited title
Source Link
Paul White ♦
Paul White ♦
  • 90.3k
  • 30
  • 424
  • 663

Is there a way to execute sqlSQL statement in sql server based on sql serverSQL Server version?

I would like to create computed index on a table if sql serverSQL Server is 2008 or newer and a simple index if sql serverSQL 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 serverSQL Server 2005 this throws an error:

Incorrect syntax near the keyword 'WHERE'.

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

Thanks

Dragan

Is there a way to execute sql statement in sql server based on sql server version?

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

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

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?

Post Merged (destination) from dba.stackexchange.com/questions/57807/…
Tweeted twitter.com/#!/StackDBAs/status/428566331085303808
Source Link
Dragan Matic
Dragan Matic
  • 233
  • 2
  • 5

Is there a way to execute sql statement in sql server based on sql server version?

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