Create Index WHERE Clause + SQL Server 2005

[Jigar Naik]

Hi,

SQL SERVER 2000

I am trying to create index using where clause but i m getting incorrect syntex near 'WHERE'

   
CREATE UNIQUE NONCLUSTERED INDEX 
IDX_IVR_SMS_CIRCLE_BRANCH_MAPPING 
ON 
IVR_SMS_CIRCLE_BRANCH_MAPPING(ISCB_CIRCLE_ID,ISCB_BRANCH_ID) 
WHERE ISCB_IS_ACTIVE=1;

Can anybody help me out with this ?

Thanks

[Jan Cumps]

Do you have the exact error message?

[Jeanne Boyarsky]

Jigar,
I've never seen a where clause in an index before. Are you sure your database supports it?

[Jan Cumps]

I only saw it in the sqlserver documentation, but have never used it. I was hoping that the exact error message could help.

Create Relational Index 
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

[ ; ]

[Jeanne Boyarsky]

What Jan posted does help. It shows that the "where" should be before the "on" clause.

[Jigar Naik]

CREATE UNIQUE NONCLUSTERED INDEX    
IDX_IVR_SMS_CIRCLE_BRANCH_MAPPING    
ON    
IVR_SMS_CIRCLE_BRANCH_MAPPING(ISCB_CIRCLE_ID,ISCB_BRANCH_ID)
WHERE ISCB_IS_ACTIVE=1;  

Exact Error

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'WHERE'.

[Jan Cumps]

Have you double-checked table and column names (or ask someone else to do that. A fresh pair of eyes can do miracles)?
Did you try to create the same index without the where clause?
Is ISCB_IS_ACTIVE a numeric column?

[Jigar Naik]

yeah i can create index without where clause.... and type of ISCB_IS_ACTIVE is BIT.

[Paul Sturrock]

As far as I am aware, that syntax appeared in SQL Server 2008. Make sure you are using the right documentation for the version of SQL Server you are working with, particularily since there were considerable changes in Transact SQL between 2000 and 2005.

[Jan Cumps]

Paul Sturrock wrote:As far as I am aware, that syntax appeared in SQL Server 2008. Make sure you are using the right documentation for the version of SQL Server you are working with, particularily since there were considerable changes in Transact SQL between 2000 and 2005.

This is a nice catch. Indeed the WHERE clause is not available on CREATE INDEX in pre-2008 versions. Well spotted.

[Jigar Naik]

yeah right... sql server 2000 does not support where clause in create index.

anyways... thanks a lot all of you... Thanks for your time.. and help...