June 20, 2013 at 6:01 am
I was told by the DBA that I have to eliminate the LOWER() function in my queries which is causing the Table Scan. Is that true?
If you have the Index on that column and if you try to compare the string by converting it to LOWER case, does it cause this Table Scan?
I am little skeptical on this and trying to find out whether or not its true.
Thank you very much for your advice in advance,
Vincy
June 20, 2013 at 6:05 am
The devil's in the detail.
A predicate/join that wraps a function around a column will indeed become un-SARGable (cannot seek on an index), so, this would cause a table/index scan, regardless whether the column has an appropriate index:
where LOWER(column)=@variable
However, this is SARGable (assuming data types are the same, if not, it's more complicated):
where column=LOWER(@variable)
June 20, 2013 at 6:17 am
Thank you very much for your quick response!
Hmm. So, the only option I have is to get rid of the function since I was using LOWER(FRUIT) = 'apple'. I guess SQL server is by default case insensitive right?
June 20, 2013 at 6:24 am
Vincy-856784 (6/20/2013)
Thank you very much for your quick response!Hmm. So, the only option I have is to get rid of the function since I was using LOWER(FRUIT) = 'apple'. I guess SQL server is by default in-case sensitive right?
Yes, SQL Server creates case insensitive databases by default
Check the setting in your database and if it is case insensitive, you can remove the function usage.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 20, 2013 at 6:24 am
Well, you need to check, but if you have case-insensitive collation in the database/columns, then the function is doing nothing other than preventing index seeks.
You can check the database collation with the following query:
SELECT DATABASEPROPERTYEX('DatabaseName', 'Collation')
You might want to check individual columns as well as they can have their own collation
June 20, 2013 at 6:27 am
+1 to what Howard wrote.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply