select @maxID = max(ID)
from Table1
where DateID < @Date

exec sp_help appl_user
go
set showplan on
go
dbcc traceon( 3604 , 302 )
go
declare @Date datetime
select @Date=...
select max(ID)
from Table1
where DateID < @Date
go
dbcc traceoff( 3604 , 302 )
go
set showplan off
go

QUERY PLAN FOR STATEMENT  1  (at line  1 ). 




    STEP  1  
        The type of query is DBCC. 


DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role. 




******************************* 
Beginning selection of qualifying indexes for table 'Table1', 
varno =  0 , objectid  1068530840 . 
   The table (Datarows) has  246068455  rows,  12605623  pages, 
   The table's Data Page Cluster Ratio 0.999990 
   The table has 4 partitions. 
   The largest partition has 4162181 pages. 
   The partition skew is 1.301858. 


Table scan cost is 246068455 rows, 12788432 pages, 
   using no data prefetch (size 2K I/O), 
   in data cache 'default data cache' (cacheid 0) with MRU replacement 




Selecting best index for the SEARCH CLAUSE: 
	Table1.DateID < unknown-value 


SARG is a local variable or the result of a function or an expression, 
using the default range selectivity to estimate selectivity. 


Estimated selectivity for DateID, 
   selectivity = 0.330000. 


Estimating selectivity of index 'DateID_FK', indid 5 
   scan selectivity 0.330000, filter selectivity 0.330000  
   81202590 rows, 4443950 pages, index height 5, 
   Data Row Cluster Ratio 0.999990, 
   Index Page Cluster Ratio 0.624928, 
   Data Page Cluster Ratio 0.000486 




The best qualifying  index is 'PK_Table1' (indid 2) 
   costing 6 pages, 
   with an estimate of 3 rows to be returned per scan of the table, 
   using no index prefetch (size 2K I/O) on leaf pages, 
   in index cache 'default data cache' (cacheid 0) with LRU replacement 
   using no data prefetch (size 2K I/O), 
   in data cache 'default data cache' (cacheid  0 ) with LRU replacement 
Search argument selectivity is  0 . 000000 . 


******************************* 


QUERY PLAN FOR STATEMENT  1  (at line  1 ). 




    STEP  1  
        The type of query is DECLARE. 




QUERY PLAN FOR STATEMENT  2  (at line  2 ). 




    STEP  1  
        The type of query is SELECT. 




QUERY PLAN FOR STATEMENT  3  (at line  3 ). 




    STEP  1  
        The type of query is SELECT. 
        Evaluate Ungrouped MAXIMUM AGGREGATE. 


        FROM TABLE 
            Table1 
        Nested iteration. 
        Using Clustered Index. 
        Index : PK_Table1 
        Backward scan. 
        Positioning at index end. 
        Scanning only up to the first qualifying row. 

declare @DateID datetime

select @DateID = max(DateID)
from Table1 (index DateID_FK)
where DateID < @Date

select @maxID = max(ID)
from Table1 (index DateID_FK)
where DateID = @DateID