Post reply

A basic question on indexes and string comparisons

  • February 17, 2012 at 11:17 am

    #264759

    I have two tables:

    create table Fruit( PK_Fruit int not null, FruitName nvarchar(20) not null) [constraints to make PK_Fruit PK, etc.]

    create table MyList( PK_List int not null, Produce1 nvarchar(20) null, Produce2 nvarchar(20) null, FK_Fruit int null)

    Now, I want to do a comparison of MyList to Fruit, and assign Fruit.PK_Fruit to MyList.FK_Fruit where appropriate, but it's possible that either MyList.Produce1 or MyList.Produce2 will contain the value that would match to Fruit.FruiteName. So, I have something like this:

    Update MyList

    set FK_Fruit = PK_Fruit

    From

    Fruit

    Where

    MyList.Produce1 = Fruit.FruitName

    Or

    MyList.Produce2 = Fruit.FruitName

    Assuming that Fruit contains and index for FruitName, what should my index on MyList look like to help with this comparison?

  • February 17, 2012 at 11:18 am

    #1448684

    http://sqlinthewild.co.za/index.php/2011/05/03/indexing-for-ors/

    Though in that example, it's unlikely that any index would be used except for a scan. That will most likely scan both tables (because there's no predicate that reduces the row count) and hash or merge join them. Depends on row count of course.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 17, 2012 at 11:22 am

    #1448686

    Thanks Gail

Viewing 3 posts - 1 through 2 (of 2 total)

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