August 6, 2013 at 4:54 pm
Hello,
I have a textbox in my application which allows the user to type any criteria in this will then be fired to the DB to return matching records that are LIKE the criteria i've passed in.
Now im my database table there are two records which differ these are as follows
Kipling Road
Alexandra Park
When I type in the letter k both of them are returned yet only one has the letter k in?
below is my stored procedure
ALTER PROCEDURE [dbo].[sp_GetMapLocations]
@SearchText varchar (20)
AS
BEGIN
SET NOCOUNT ON;
Select LocationName, Latitude, Longitude
from Locations
where ((@SearchText is null) or ( LocationName like '%' + @SearchText + '%' ))
END
This is the part thats going wrong (which im sure you new anyway)
( LocationName like '%' + @SearchText + '%' )
Any help would be appreciated.
August 6, 2013 at 5:08 pm
I see letter k in both rows.
Kipling Road
Alexandra Park
August 6, 2013 at 6:05 pm
If you want to distinguish between k and K, you need either to write something like
LocationName collate latin1_general_cs_as like '%'+@searchstring+'%
or to give the column a case-sensitive collation when creating the table.
Tom
August 7, 2013 at 12:16 am
Luis Cazares (8/6/2013)
I see letter k in both rows.Kipling Road
Alexandra Park
I actually feel like an idiot! this just goes to shows I was completely shattered last night.
I'm sorry for my stupidity
August 7, 2013 at 2:36 am
1) Don't name your stored procedures sp_something. The sp_ prefix is reserved for system procedures.
2) As far as I can see there is a 'k' in both "Alexandra Park" and "Kipling Road". Maybe you think of "K" as different from "k", but if your column has a case-insensitive collation, they are not. You can review the collation for the columns in a table with sp_help. The default collation for a column, is the database collation, which in its turn defaults to the server collation. Which in its turn defaults to a case-insensitive collation, chosen depending on the system locale for your Windows installation.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 7, 2013 at 7:38 am
Since you are doing a catch-all type query you should read up about the performance of these types of queries. Gail's article does a great job explaining the performance issues and how to overcome them. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 7, 2013 at 10:12 am
Sean Lange (8/7/2013)
Since you are doing a catch-all type query you should read up about the performance of these types of queries. Gail's article does a great job explaining the performance issues and how to overcome them. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
Normally this would be important (and is still good to point out), but here with the LIKE '%' + text + '%', performance is gonna suck anyway! :hehe:
That pattern is gonna get an index scan at best if the field being searched has an NC index containing it, and the estimated rows coming out may well be WILDLY inaccurate, leading to HORRIBLY BAD query plans.
I note that LIKE WITHOUT the leading '%' CAN be SARGable and do seeks if appropriate AND get pretty good row count estimates too. I also note that even if it does, parameter sniffing will crush you here and you may want to put on an OPTION (RECOMPILE) at the end of the SELECT. Searching for LIKE 's%' is going to return WAY more rows than if you search for LIKE 'xq3ww%', and you want different query plans for each to be optimal.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 7, 2013 at 11:21 am
.Netter (8/7/2013)
Luis Cazares (8/6/2013)
I see letter k in both rows.Kipling Road
Alexandra Park
I actually feel like an idiot! this just goes to shows I was completely shattered last night.
I'm sorry for my stupidity
Don't be sorry, it happens to everyone from time to time and you got some good advices from here. 🙂
August 7, 2013 at 2:28 pm
TheSQLGuru (8/7/2013)
Normally this would be important (and is still good to point out), but here with the LIKE '%' + text + '%', performance is gonna suck anyway!
Although depending on the collation and data type there is quite a difference in how much it will suck. This is one of the few cases, it's a winner to use an SQL collation and varchar. It's something like a factor 7 better than a Windows collation and/or nvarchar. (Well, a binary collation always works, but it is not likely to be practically useful.)
That pattern is gonna get an index scan at best if the field being searched has an NC index containing it, and the estimated rows coming out may well be WILDLY inaccurate, leading to HORRIBLY BAD query plans.
Keep in mind that SQL Server maintains string statistics, so it can make an estimate of the hit rate if you are joining with other tables.
Also, only to pull our legs, the query plan will typically have an Index Seek operator. (Which includes a range scan function, and of course the entire index is scanned.)
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 7, 2013 at 3:46 pm
Erland, don't the string stats only work from left-to-right in the string though? i.e. it can't know that the string 'cat' is going to be hit in this field value ("the fat cat jumped on the table") or this one ("she is very scatterbrained") if you do field1 LIKE '%cat%', right?
However, if the field value is ("caterpillars are very cute") it CAN estimate that hit with field1 LIKE 'cat%'
That's my understanding at the moment anyway...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 7, 2013 at 3:58 pm
Run this in tempdb:
SELECT o.name AS objname, c.name as colname
INTO tempo
FROM sys.objects o
JOIN sys.columns c ON o.object_id = c.object_id
go
CREATE STATISTICS stringstat ON tempo(colname) WITH FULLSCAN
go
Then look at the estimated execution plan for these two queries:
SELECT * FROM tempo WHERE colname LIKE '%rea%'
SELECT * FROM tempo WHERE colname LIKE '%xyz%'
Note that the estimated number of rows are different!
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 7, 2013 at 6:10 pm
SWEET! Apparently the engine does a pretty good job digging through the characters too:
SELECT o.name AS objname, c.name as colname
INTO tempo
FROM sys.objects o
JOIN sys.columns c ON o.object_id = c.object_id
go
CREATE STATISTICS stringstat ON tempo(colname) WITH FULLSCAN
go
Then look at the estimated execution plan for these two queries:
14.3429 ROWS estimated, 12 returned
SELECT * FROM tempo WHERE colname LIKE '%rea%'
1.43429 ROWS estimated, 0 returned
SELECT * FROM tempo WHERE colname LIKE '%xyz%'
SELECT o.name + ' asdf /23#@##812 ' + c.name AS colname
INTO tempo1
FROM sys.objects o
JOIN sys.columns c ON o.object_id = c.object_id
go
CREATE STATISTICS stringstat ON tempo1(colname) WITH FULLSCAN
go
28.7429 ROWS estimated, 20 returned
SELECT * FROM tempo1 WHERE colname LIKE '%rea%'
1.43714 ROWS estimated, 0 returned
SELECT * FROM tempo1 WHERE colname LIKE '%xyz%'
perfect estimate - 501 ROWS estimated, 501 returned!
SELECT * FROM tempo1 WHERE colname LIKE '%asdf%'
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply