dimanche 28 juin 2015

For which column index needs to be created

Select distinct c.classID, co.fCourseName as CourseName, StreetAddress + ', ' + l.City as LocationAddress, s.SessionName, sh.fShift as shift, StartTime, EndTime, c.classname, s.SessionID,
    c.StartDate,c.enddate 
    From dbo.vw_Class c 
    Inner Join dbo.lk_Session s 
    On (s.SessionID = c.sessionID) 
    Inner Join dbo.lk_Course co 
    On (co.CourseID = c.CourseID )
    Inner Join dbo.vw_Location l 
    On (l.locationid = c.locationid) 
    Inner Join lk_District d
    On (d.districtID = c.districtId) 
    Inner Join lk_Province p 
    On (p.provik = d.provik) 
    Inner Join lk_Shift sh 
    On (c.shiftid = sh.shiftid)
   where 
      c.DistrictID       =  case  when @Districtid is null   then c.DistrictID   else  @Districtid  end 
     and c.LocationID    =  case  when @locationid is null   then c.LocationID   else  @locationid  end 
     and s.SessionID     =  case  when @sessionid is null    then s.SessionID    else  @sessionid   end 
     and c.CourseID      =  case  when @levelid  is null     then c.CourseID     else  @levelid     end 
     and c.ShiftID       =  case  when @shiftid   is null    then c.ShiftID      else  @shiftid     end 
     and c.StartDate    >=  case  when @startdate is null    then c.StartDate    else  @startdate   end
     and c.EndDate      <=  case when  @enddate is null      then c.EndDate      else  @enddate     end
     and convert(time,c.StartTime) >= case when @starttime is null then convert(time,c.StartTime) else convert(time,@starttime) end
     and convert(time,c.endtime)   <= case when @endtime is null then convert(time,c.endtime) else convert(time,@endtime) end
     and c.Monday    = case  when @day1 = 'N' then c.monday     else  @day1  end 
     and c.Tuesday   = case  when @day2 = 'N' then c.Tuesday        else  @day2  end 
     and c.Wednesday = case  when @day3 = 'N' then c.Wednesday  else  @day3  end 
     and c.Thursday  = case  when @day4 = 'N' then c.Thursday       else  @day4  end 
     and c.Friday    = case  when @day5 = 'N' then c.Friday     else  @day5  end 
     and c.Saturday  = case  when @day6 = 'N'then c.Saturday        else  @day6  end 
     and c.Sunday    = case  when @day7 = 'N' then c.Sunday     else  @day7  end 
     and c.RowStatus    = 'A' 
     ORDER BY co.fCourseName, s.SessionID ,c.ClassName

In the above code for which columns i need to create the index.(None of tables used in this query don't have either primary key or indexes created)

Aucun commentaire:

Enregistrer un commentaire