How to improve estimate of 1 row in a View constrained by DateAdd() against an index Using Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64). 

Given a table and index:

create table [User].[Session] 
(
  SessionId int identity(1, 1) not null primary key
  CreatedUtc datetime2(7) not null default sysutcdatetime())
)

create nonclustered index [IX_User_Session_CreatedUtc]
on [User].[Session]([CreatedUtc]) include (SessionId)


Actual Rows for each of the following queries is 3.1M, the estimated rows are shown as comments.

When these queries feed another query in a View, the optimizer chooses a loop join because of the 1 row estimates.  How to improve the estimate at this ground level to avoid overriding the parent query join hint or resorting to an SP?

Using a hardcoded date works great:

 select distinct SessionId from [User].Session -- 2.9M (great)
  where CreatedUtc &gt; '04/08/2015'  -- but hardcoded


These equivalent queries are view compatible but all estimate 1 row:

select distinct SessionId from [User].Session -- 1
 where CreatedUtc &gt; dateadd(day, -365, sysutcdatetime())         

select distinct SessionId from [User].Session  -- 1
 where dateadd(day, 365, CreatedUtc) &gt; sysutcdatetime();          

select distinct SessionId from [User].Session s  -- 1
 inner loop join  (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
    on d.MinCreatedUtc &lt; s.CreatedUtc    
    -- (also tried reversing join order, not shown, no change)

select distinct SessionId from [User].Session s -- 1
 cross apply (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
 where d.MinCreatedUtc &lt; s.CreatedUtc
    -- (also tried reversing join order, not shown, no change)


Try some hints (but N/A to View):

 select distinct SessionId from [User].Session -- 1
  where CreatedUtc &gt; dateadd(day, -365, sysutcdatetime())
 option (recompile);

select distinct SessionId from [User].Session  -- 1
 where CreatedUtc &gt; (select dateadd(day, -365, sysutcdatetime()))
 option (recompile, optimize for unknown);

select distinct SessionId                     -- 1
  from (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
 inner loop join [User].Session s    
    on s.CreatedUtc &gt; d.MinCreatedUtc  
option (recompile);


Try using Parameter/Hints (but N/A to View):

declare
    @minDate datetime2(7) = dateadd(day, -365, sysutcdatetime());

select distinct SessionId from [User].Session  -- 1.2M (adequate)
 where CreatedUtc &gt; @minDate;

select distinct SessionId from [User].Session  -- 2.96M (great)
 where CreatedUtc &gt; @minDate
option (recompile);

select distinct SessionId from [User].Session  -- 1.2M (adequate)
 where CreatedUtc &gt; @minDate
option (optimize for unknown);




The statistics are up to date. 

DBCC SHOW_STATISTICS('user.Session', 'IX_User_Session_CreatedUtc') with histogram;


The last few rows of the histogram (189 rows total) are shown:
A less comprehensive answer than Aaron's but the core issue is a cardinality estimation bug with DATEADD when using the datetime2 type:
Connect: Incorrect estimate when sysdatetime appear in a dateadd() expression
One workaround is to use GETUTCDATE (which returns datetime):
WHERE CreatedUtc &gt; CONVERT(datetime2(7), DATEADD(DAY, -365, GETUTCDATE()))

Note the conversion to datetime2 must be outside the DATEADD to avoid the bug.
An incorrect cardinality estimation reproduces for me in all versions of SQL Server up to and including 2019 CU8 GDR (build 15.0.4083) when the 70 model cardinality estimator is used.
Aaron Bertrand has written an article about this for SQLPerformance.com:

Performance Surprises and Assumptions : DATEADD()