Win a copy of Microservices Testing (Live Project) this week in the Spring forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Liutauras Vilda
  • Henry Wong
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Al Hobbs
  • Carey Brown
Bartenders:
  • Piet Souris
  • Mikalai Zaikin
  • Himai Minh

Create Index WHERE Clause + SQL Server 2005

 
Ranch Hand
Posts: 763
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

SQL SERVER 2000

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



Can anybody help me out with this ?

Thanks
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Do you have the exact error message?
 
author & internet detective
Posts: 41184
848
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Jigar,
I've never seen a where clause in an index before. Are you sure your database supports it?
 
Jan Cumps
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I only saw it in the sqlserver documentation, but have never used it. I was hoping that the exact error message could help.

 
Jeanne Boyarsky
author & internet detective
Posts: 41184
848
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What Jan posted does help. It shows that the "where" should be before the "on" clause.
 
Jigar Naik
Ranch Hand
Posts: 763
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


Exact Error

 
Jan Cumps
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 763
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
yeah i can create index without where clause.... and type of ISCB_IS_ACTIVE is BIT.
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Ranch Hand
Posts: 763
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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...
 
reply
    Bookmark Topic Watch Topic
  • New Topic