function on indexed field

Hi, I am using sql server 2000 SP1.

select * from document_display where upper(document_name) = upper(v_document_name)

v_document_name is a variable.

The table is over 200,000 records and presently has index on column document_name

Could anyone help on how to improve the performance of above query ?

? Can you store the document_name in a column that uses a non-case-sensitive collation? Then you won't need to use the UPPER function to compare them... -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <gtisupport@.discussions.microsoft.com> wrote in message news:6a3d9de8-dd46-4977-87cf-dadb89f09ad0_WBRev1_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com Hi, I am using sql server 2000 SP1. select * from document_display where upper(document_name) = upper(v_document_name) v_document_name is a variable. The table is over 200,000 records and presently has index on column document_name Could anyone help on how to improve the performance of above query ?|||

depending on collation, there shouldnt really be a distinction between

select * from document_display where upper(document_name) = upper(v_document_name)

and

select * from document_display where document_name = v_document_name

|||

Thanks, but the effort is to too great to implement. I know that Oracle DB has feature called function index. Is there a similar one in SQL Server ?

|||Another thing you could to to speed performance is not do a select * from the table. Specify the specific columns in you table that you require for the query. Then have your index cover the document_name and the columns that are specified in the query. This will eliminated any bookmark lookups that might have been going on.|||? No, there are no function indexes in SQL Server. Why is a non-case sensitive column too difficult to implement? All you have to do is: ALTER TABLE document_displayADD document_name_ci AS (document_name) COLLATE SQL_Latin1_General_CP1_CI_AS Then you can create an index on the new column: CREATE INDEX IX_document_name_ci ON document_display (document_name_ci) ... and that's pretty much it! Now you can use the new column in your queries, without worrying about the UPPER function. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <gtisupport@.discussions.microsoft.com> wrote in message news:f4393446-77fc-4634-bc45-5bde6ecdc828@.discussions.microsoft.com... Thanks, but the effort is to too great to implement. I know that Oracle DB has feature called function index. Is there a similar one in SQL Server ?