SQL query processing date parameter differently

Question

So, we have a simple query that starts with joining several tables, and we restrict the data. These queries are part of our data warehouse load and are critical to time. We noticed a huge difference in execution speed and execution plans when we changed the use of dates in the where clause to join a temporary table with dates in. So:

    Declare @StartDate DateTime = Cast(Floor(Cast(GetDate() AS FLOAT))AS DateTime)
    Declare @EndDate DateTime = GetDate()

    Select *
    From Table A 
           Inner Join Table B
                On A.A = B.A
    Where A.Date Between @StartDate AND @EndDate

A simplified version of the request, but returns 11k lines in about 50 seconds.

    Declare @StartDate DateTime = Cast(Floor(Cast(GetDate() AS FLOAT))AS DateTime)
    Declare @EndDate DateTime = GetDate()

    Select @StartDate StartDate, @EndDate EndDate
    Into #Dates

    Select *
    From Table A 
           Inner Join Table B
                On A.A = B.A
           Inner Join #Dates
                On A.Date Between StartDate AND EndDate

Returns the same rows 11k, but the second. The difference in execution plan is also noticeable, the second request is filled with nested loops, in contrast to hash matches in the first request.

My question is why? Why is the difference 50 or so?

/ * Edit * /

Two QEP

Answer 1

, , , - ( ). , , , SQL Server . , , .

:

• ( sproc paremeters)
• OPTION (RECOMPILE), .

( 50-) - QP .

Answer 2

, WHERE. here