New answers tagged execution-plan
Would it not be better to have the dates as variables so you only have to get them once before the query? DECLARE @Today DATE DECLARE @Tomorrow DATE SET @Today = GETDATE() SET @Tomorrow = DATEADD(day,1,GETDATE()) SELECT FROM <Table> WHERE InsertedOn >= @Today AND InsertedOn < @Tomorrow
Instead, try WHERE InsertedOn>=CAST(GETDATE() AS date) AND InsertedOn<DATEADD(day, 1, CAST(GETDATE() AS date)) This expression is sargable which is what you want for optimum performance. Like @Mikael indicates, you would do well to design one of your indexes so that InsertedOn is the first column, and that all the other columns used in the ...
Then it looks like an optimizer's blind spot and you should use the second query. When there is a condition joining two tables a and b: a.id = b.id and an additional condition a.id > @some_constant, seems like the optimizer uses the "index condition" for where to start the index scan on a (id) index but it doesn't use it for the second index b (id). So, ...
You are looking to choose rows from one of two tables dynamically. This is generally possible to achieve without dynamic SQL. To demonstrate, here is a simplified version based on the AdventureWorks sample database: The idea will be to choose rows from either the TransactionHistory or TransactionHistoryArchive tables for each Product, based on the value of ...
Top 50 recent answers are included