File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/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 EJB 3 in Action this week in the EJB and other Java EE Technologies 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: 751
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: 2476
    
    7

Do you have the exact error message?


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

Joined: May 26, 2003
Posts: 29220
    
135

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: 2476
    
    7

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
internet detective
Marshal

Joined: May 26, 2003
Posts: 29220
    
135

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: 751


Exact Error

Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2476
    
    7

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: 751
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: 2476
    
    7

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: 751
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...
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Create Index WHERE Clause + SQL Server 2005
 
Similar Threads
Searching technique used by SQL server
MS-SQL - Should i index the column?
Handling Null Values In Jdbc
Oracle performance hit for 1 million record
Function Based Indexes