• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Create Index WHERE Clause + SQL Server 2005

 
Jigar Naik
Ranch Hand
Posts: 761
  • 0
  • Mark post as helpful
  • send pies
  • 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
 
Jan Cumps
Bartender
Posts: 2577
11
C++ Linux Netbeans IDE
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do you have the exact error message?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 33699
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • 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: 2577
11
C++ Linux Netbeans IDE
  • 0
  • Mark post as helpful
  • send pies
  • 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
Marshal
Posts: 33699
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • 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: 761
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


Exact Error

 
Jan Cumps
Bartender
Posts: 2577
11
C++ Linux Netbeans IDE
  • 0
  • Mark post as helpful
  • send pies
  • 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: 761
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yeah i can create index without where clause.... and type of ISCB_IS_ACTIVE is BIT.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • 0
  • Mark post as helpful
  • send pies
  • 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: 2577
11
C++ Linux Netbeans IDE
  • 0
  • Mark post as helpful
  • send pies
  • 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: 761
  • 0
  • Mark post as helpful
  • send pies
  • 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...
 
Consider Paul's rocket mass heater.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic