March 24, 2009 at 12:36 pm
I need to determine how SQL decides which order to return the results from a query like the one I have.
I have a @SearchWord variable and I search as follows (PopularityRating is an Integer)
SELECT * FROM [PeopleTable]
WHERE Bio LIKE %+@SearchWord +%
OR Name LIKE %+@SearchWord +%
OR Location LIKE %+@SearchWord +%
OR Company LIKE %+@SearchWord +%
ORDER BY PopularityRating DESC
I thought the most relevant results would be matched in the order theyr fields are searched. I am now thinking that this logic is incorrect.
March 24, 2009 at 1:55 pm
CountCet (3/24/2009)
I need to determine how SQL decides which order to return the results from a query like the one I have.I have a @SearchWord variable and I search as follows (PopularityRating is an Integer)
SELECT * FROM [PeopleTable]
WHERE Bio LIKE %+@SearchWord +%
OR Name LIKE %+@SearchWord +%
OR Location LIKE %+@SearchWord +%
OR Company LIKE %+@SearchWord +%
ORDER BY PopularityRating DESC
I thought the most relevant results would be matched in the order theyr fields are searched. I am now thinking that this logic is incorrect.
The results will be returned in the order you have specified. That order being determined by the ORDER BY PopularityRating DESC statement.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 24, 2009 at 1:58 pm
I'm going to recommend taking a look at Full Text Indexing. Check it out, it will almost certainly do what you want, better than "like" statements will. Has some very cool options for this kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 25, 2009 at 6:33 am
No, SQL Server will determine the order of resolution based on the indexes and their statistics on the table (or the lack thereof). You have very little control over the order of processing. Take a look at the execution plan. That will tell you as much as you can know about the order that SQL Server is processing the checks.
I agree with GSquared too.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply