Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!eternal-september.org!feeder.eternal-september.org!mx04.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver Subject: Re: using like vs = in exact match Date: Wed, 26 Dec 2012 20:10:27 +0100 Organization: Erland Sommarskog Lines: 42 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 8bit Injection-Info: mx04.eternal-september.org; posting-host="102f3b4a812ef50a29978963a1d5bdd7"; logging-data="3938"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/Tq/vGRdBY49YWW5syJzSz" User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32) Cancel-Lock: sha1:tfYfeZ3xSpuNm1EEOHu6gqMbHDc= Xref: csiph.com comp.databases.ms-sqlserver:1372 migurus (migurus@yahoo.com) writes: > I came across a situation where application builds a list of names by > matching first three letters entered by user. The list of names does not > have any duplicates The query generated by app is > SELECT LAST_NAME > FROM NAME_LIST > WHERE LAST_NAME LIKE 'GRE%' > > The result set is coming back as > GREAGORS > GREEN > GREISS > > Now user selects one line and application should retrieve that line, The > app generate the same query as in the 3 letter case above > SELECT LAST_NAME > FROM NAME_LIST > WHERE LAST_NAME LIKE 'GREEN%' > > I don't like the lazy programming, it should have been WHERE LAST_NAME = > 'GREEN' in my view, my guts feeling is that the access plan for LIKE > clause might be inefficient comparing to the = clause. > The example above is simplified. But in essence is LIKE less likely to > produce a perfect plan comparing to =, or there is no justification to > my rant. I would execpt there is no difference of practical importance, although may some measurable difference if you 200 processes all sending this LIKE query. But apart from that, I agree with you. If nothing else, assume that there also is a GREENE in the list. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx