Sql script:
SET SHOWPLAN_TEXT ON
go
select *
from Dealer d
inner join AccountNumber an
on d.AccountNumberID = an.ID
where an.AccountNumber='12345678'
and d.StartDate <= GETDATE()
and d.EndDate>=GETDATE()
It's output (Execution mode "results to text")
StmtText
-------------------------
SET SHOWPLAN_TEXT ON
(1 row(s) affected)
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select *
from Dealer d
inner join AccountNumber an
on d.AccountNumberID = an.ID
where an.AccountNumber='12345678'
and d.StartDate <= GETDATE()
and d.EndDate>=GETDATE()
(1 row(s) affected)
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Inner Join, OUTER REFERENCES:([d].[ID]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([an].[ID]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([an].[ID]))
| | |--Index Seek(OBJECT:([SCSSDev].[dbo].[AccountNumber].[UX_AccountNumber] AS [an]), SEEK:([an].[AccountNumber]=N'12345678') ORDERED FORWARD)
| | |--Clustered Index Seek(OBJECT:([SCSSDev].[dbo].[AccountNumber].[PK_AccountNumber] AS [an]), SEEK:([an].[ID]=[SCSSDev].[dbo].[AccountNumber].[ID] as [an].[ID]) LOOKUP ORDERED FORWARD)
| |--Index Seek(OBJECT:([SCSSDev].[dbo].[Dealer].[UX_Dealer] AS [d]), SEEK:([d].[AccountNumberID]=[SCSSDev].[dbo].[AccountNumber].[ID] as [an].[ID] AND [d].[StartDate] <= getdate()) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([SCSSDev].[dbo].[Dealer].[PK_Shop] AS [d]), SEEK:([d].[ID]=[SCSSDev].[dbo].[Dealer].[ID] as [d].[ID]), WHERE:([SCSSDev].[dbo].[Dealer].[EndDate] as [d].[EndDate]>=getdate()) LOOKUP ORDERED FORWARD)
(7 row(s) affected)