Indexing adequately UPPER requests
-
Hello Everyone,
I want to know what would be the best practice to adequately index queries that contains UPPER or LOWER function in a case sensitive database.
Example: SELECT * FROM [dbo.employee] WHERE UPPER(emp_name) = 'TREMBLAY'
----------------------------------------------------------------------------------------------
With Oracle we have function based indexes which gave us the ability to create an index containing the UPPER/LOWER function:
Example: CREATE INDEX emp_idx1 ON employee (UPPER(emp_name));
Having this kind of index allow us to adequately index the above query with Oracle.
-----------------------------------------------------------------------------------------------
In SQL Server what would be the best approach to do that...
Is it by using a computed column to contains the UPPER or LOWER value of the concern columns and index this computed columns?
What else?
Best regards,
Carl
-
If the db/table/column are not case sensitive, then 'Trembley' = 'TREMBLEY', so a normal index will do its job.
-
Thanks RGR'us,
But what if the column must be case sensitive?
Because it is my point, the entire DB is case sensitive.
Best regards,
Carl
-
Select * from dbo.CSTable where CSCols like 'Trembley' collate Latin1_General_CI_AS
this will do the search ignoring the case.
-
Thank's RGR'us,
Short and simple.
Sometimes we tend to see things more complicated than it is, I should have thougth to this.
Carl
-
Might also think about making the db case insensitive because all the objects will also be CS which can be a big pain in the ass.
-
One more option could be to use a computed column which gets populated automatically using the UPPER function so that the values in that column would always be upper case. You can then index the computed column and run your query off of that column instead.
That is the only alternative to Oracle's FBI (Function Based Indexes) that you had mentioned above. As Remi mentioned above, if you are going to be using case sensitivity for the DB, the objects also because CS and you can run into other issues also.
-
Hello RGR'us,
Using this approach don't seem to solve the indexing problem...
In fact the proper index is not used.
Maybe the only solution is to have a computed column?
Best regards,
Carl
-
Not it's not the only solution >>
CREATE TABLE [Test] (
[Pktest] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_CS_AS NOT NULL ,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[Pktest]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_Text_Name ON dbo.Test (Name)
GO
INSERT INTO dbo.Test (Name) VALUES ('Tremblay')
INSERT INTO dbo.Test (Name) VALUES ('Trembley')
INSERT INTO dbo.Test (Name) VALUES ('tremblay')
INSERT INTO dbo.Test (Name) VALUES ('trembley')
INSERT INTO dbo.Test (Name) VALUES ('Tramblay')
GO
Select * from dbo.Test where name = 'Trembley'
--index seek 1 row(s) affected
Select * from dbo.Test where name BETWEEN LOWER('Trembley') AND UPPER('Trembley')
--index seek 2 row(s) affected
DROP TABLE dbo.TEST
-
RGR'us,
Its always good to have the "big picture". Maybe your solution is applicable in our case but we have to see.
Many thanks
Carl
-
I'm pretty sure it'll be usefull (unless maybe you can have some other characters that throw this off)???.
-
Hello RGR'us,
I tried this method on a big table (1 508 468 rows) and it does not take the index.
I tried with the column having this collation Latin1_General_CS_AS and with this collation Latin1_General_CI_AS and with both, the index on the column is not used.
select * from workday where UPPER(employee_id) = 'E1001' -- Index not used
Select * from workday where employee_id between UPPER('E1001') AND LOWER('E1001') -- index not used
But when I use the coulmn durectly (whitout the UPPER/LOPWER function) it takes the index:
select * from workday where employee_id = 'E1001' -- index used OK
But it is not case-insensitive...
Maybe there is something I missed somewhere... Because it seems that for you it worked.
Best regards,
Carl
-
You forgot that the db is already case sensitive. Here's how to do it when it's not case sensitive >>
Select name, id from dbo.SysObjects where name = 'sysobjects'
--seek
Select name, id from dbo.SysObjects where name = 'sysobjects' COLLATE Latin1_General_CS_AS
--scan
Select name, id from dbo.SysObjects where name = 'sysobjects' COLLATE Latin1_General_CS_AS AND name = 'SysObjects'
--scan + seek =
! -
Hello RGR'us,
at the beginning of this topic I mentionned that the database was case-sensitive...
So there is no other solution then using a computed column when the database is case-sensitive ans I want to do a case-insensitive search?
Regards,
Carl
-
Just reread the whole thing...
Can you post the table DDL and some sample data so I can figure it out?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply