SQL Server Performance

Avoiding Table scans in a lookup function

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by EMoscosoCam, Jun 10, 2009.

  1. EMoscosoCam Member

    Hello
    Suppose that I have the table BusinessWeek(BiWk char(7), InitialDate date, EndDate date). I want to create a function that given a date, it would return the BiWk value corresponding to that value. The following query uses a Table Scan:DECLARE @BiDay AS DATE
    SET
    @BiDay = '2008-05-17'SELECTBiWkFROM
    BusinessWeekWHERE @BiDay BETWEEN InitialDate AND EndDate
    How can I boost the performance of this query? What indexes should be created? My intention is to process a large table row by row and to get the Business Week corresponding to a Date Field of that table.
    Thanks a lot.
  2. ndinakar Member

    I would add an index covering both columns - InitialDate and EndDate and INCLUDE BiWk via the INCLUDE option.
  3. satya Moderator

    HOw many rows will be returned with this function?
  4. EMoscosoCam Member

    A single scalar value (char(7)), which would be the Business Week. I would use it like:
    Select
    GetBiWk(LT.MyDateField) as BiWk
    from LargeTable as LT
    Thanks.

Share This Page