Creating an INDEX with function UPPER

[ASmee]

I am querying a table using the upper function, the table has an index on the column, but UPPER negates the use of this index.

select exposureid from anthony where UPPER(exposure) = 'ANTHONY';

If I issue:

create index ex on anthony (UPPER(exposureid))

The error returned is:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.

Oracle allows this, is MS SQL Server unable to deal with this?

 

[SQLDenis]

upper won't help you, take a look at this

select exposureid from anthony where exposure COLLATE SQL_Latin1_General_CP1_CS_AS = 'ANTHONY';

create table #test (name varchar(50))
insert into #test
select 'Anthony' union all
select 'anthony' union all
select 'ANTHONY' 

--won't work
select * from #test
where name = 'ANTHONY'

--won't work
select * from #test
where upper(name) = 'ANTHONY'


--yeah it works ;-)
select * from #test
where name  COLLATE SQL_Latin1_General_CP1_CS_AS  = 'ANTHONY'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[yelworcm]

Here is a work-around. Not sure if this will be possible in your application. Just one more reason to not use "select *"

set quoted_identifier on

go
create table test1 (col1 varchar(10), col2 as upper(col1))

go
create index ind_test on test1 (col2)

 

[ASmee]

I left out a significant part, the application is querying the table with:

select exposureid, name from anthony where upper(exposurid) = 'TEXT';

I cannot change the application. To improve reponse times, under Oracle, I can create an index of:

create index tt on anthony (upper(exposureid))

How do I do this in MS SQL Server.

 

[SQLDenis]

running this

select exposureid, name from anthony where upper(exposurid) = 'TEXT';

or this

select exposureid, name from anthony where exposurid = 'TEXT';

will give you the same result (depending on collation of course), but the first query will not use the index since you are using a function on a field

what are you trying to do anyway?
should text and TEXT be returned or only TEXT


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

You can't create an index like that in SQL server
Your problem is that you can't change the app
If you had used stored procedures this would be an easy fix

Now why can't the app be changed? is it compiled or outsourced?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ASmee]

Denis, the first query is what the application is doing, this is not using the index, so the performance is terrible. I want to create the index on UPPER(exposureid) to improve performance.

My question, is this possible? (It is in Oracle).

 

[yelworcm]

Nope. Not possible. A Function Based Index is an Oracle only feature that MS has not picked up on, yet.

 

[SQLDenis]

nope, change the sql (if you can)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/