Post reply

Are strings that begin with left brackets indexed correctly in SQL server??

  • March 27, 2007 at 2:29 pm

    #171872

    Hi, I have a index built on an nvarchar column in a table that contains a few million rows.   When I seach for a string that begins with a set of characters, the search returns very fast (1ms).  However, if I search for strings beginning with a left bracket, it takes several seconds to run (even when it returns no rows).

     

    Normal search, which is fast:

    select top 10 * from <table> where str like 'asdf%'

     

    Two ways to search for strings beginning with left brackets (both run slow):

    select top 10 * from <table> where str like '[[]asdf%'

    select top 10 * from <table> where str like '\[asdf%' escape ('\')

     

    When I change this to search for strign beginning with a right bracket, the query runs very fast again:

    select top 10 * from <table> where str like '[]]asdf%'

     

    Apparently searches that begin with a left bracket always perform an index scan, but if they begin with anyhting else an index seek is performed.  Does anyone know why this is happening, or how to fix it?

    I am running this on: 

    Microsoft SQL Server 2005 - 9.00.3042.00 (X64)

    SQL Server Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

    Thanks,

    Tom

     

  • March 27, 2007 at 11:03 pm

    #697315

    SQL server is behaving as it suppose to...

    When you use wild card at the beginning of the search SQL is always scans the table/index....

    Check the following thread...

    Transact-SQL Query SQL Server Performance Tuning Tips

    http://www.sql-server-performance.com/transact_sql.asp

     

    MohammedU
    Microsoft SQL Server MVP

  • March 28, 2007 at 6:54 pm

    #697558

    I am an experienced DBA and understand that if I include a wildcard at the beginning on the search, a full scan will be required. However, I'm trying to find strings that begin with a left bracket.  I can't get SQL server to do an index seek on strings begining with a left bracket.  Searches for strings begining with any other character (right bracket, a letter, etc) all work correctly. 

    [[]asdf% does not work (does a scan)

    []]asdf% works (does a seek)

    [%]asdf% works (does a seek)

    The reason I have to search for "[[]asdf%" is that I want to escape the [ so that an exact search is performed for it.  The string it tries to match here is:   [asdf%

    I still think this is a bug.

  • March 28, 2007 at 10:10 pm

    #697576

    Given you know that the first part of the string you want to search, use the BETWEEN rather than LIKE.

    This does require you to determine the lowest rank character in your collation to be appended to the second operand.

    declare @lowestchar char(1)

    select top 1 @lowestchar = char(number)

    from master..spt_values

    where type = 'P'

    order by char(number) desc

    select top 10 * from

    where str between '[asdf' and '[asdf'+@lowestchar

    ------

    Robert

  • March 29, 2007 at 1:23 pm

    #697731

    I tried this and found the same behavior - searching for a string - where col5 like '[[]asdf%' . If I search on a indexed varchar column, it does a seek. If I search on a indexed nvarchar column it does a scan. Any other searches on either the varchar or nvarchar columns do a seek.

    Don't have time to understand why, but thought I would validate your tests

    SQL2005 SP2 build 3052 32bit, and build 2047 32bit

    jg

  • April 2, 2007 at 11:59 am

    #698153

    Thanks for confirming that I'm not doing something wrong, John G.  I will go ahead and report it as a bug.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply