aspose file tools*
The moose likes JDBC and the fly likes Create Index WHERE Clause + SQL Server 2005 Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Create Index WHERE Clause + SQL Server 2005" Watch "Create Index WHERE Clause + SQL Server 2005" New topic
Author

Create Index WHERE Clause + SQL Server 2005

Jigar Naik
Ranch Hand

Joined: Dec 12, 2006
Posts: 757
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


Jigar Naik


Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

Do you have the exact error message?


OCUP UML fundamental and ITIL foundation
youtube channel
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30758
    
156

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


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

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

Joined: May 26, 2003
Posts: 30758
    
156

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

Joined: Dec 12, 2006
Posts: 757


Exact Error

Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

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

Joined: Dec 12, 2006
Posts: 757
yeah i can create index without where clause.... and type of ISCB_IS_ACTIVE is BIT.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

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

Joined: Dec 12, 2006
Posts: 757
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...
 
Don't get me started about those stupid light bulbs.
 
subject: Create Index WHERE Clause + SQL Server 2005