New answers tagged

2

Thanks Kin for pointing to Paul's Article.Earlier wherever I have scalar operators I used to see them assigned some value,but in this case,those values are calculated from below function.. GetRangeWithMismatchedTypes('2016-02-07 15:39:02.1000000',NULL,(6)) So scalar operator Expr1014 is assigned--2016-02-07 15:39:02.1000000 Expr1015 is assigned null ...


0

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


6

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 ...


3

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, ...


3

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