Sql-server – Query Performance depending on parameter 21seconds vs. > 14hours

The DATEPART() function still has to be performed on every row before any filtering can be done/calculated.

Your view would be much more efficient if you changed it like this:

CREATE VIEW PartitionedTb_Sales 
AS 
SELECT *, 2010 AS SalesYear FROM tb_sales2010
UNION ALL
SELECT *, 2011 AS SalesYear FROM tb_sales2011
UNION ALL
SELECT *, 2012 AS SalesYear FROM tb_sales2012

Then, instead of using DATEPART() to calculate the year for each row, it will already be defined.

SELECT TOP 1 *
FROM partitionedTb_sales
WHERE SalesYear = 2011

This seems to work and keep the precision as well:

SELECT DATEADD(day, DATEDIFF(day,'19000101',@D), CAST(@T AS DATETIME2(7)))

The CAST to DATETIME2(7) converts the TIME(7) value (@T) to a DATETIME2 where the date part is '1900-01-01', which is the default value of date and datetime types (see datetime2 and the comment^* at CAST and CONVERT page at MSDN.)

^{* ... When character data that represents only date or only time components is cast to the datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.}

The DATEADD() and DATEDIFF()function take care of the rest, i.e. adding the difference in days between the 1900-01-01 and the DATE value (@D).

Test at: SQL-Fiddle

As noticed by @Quandary, the above expression is considered not-determninistic by SQL Server. If we want a deterministic expression, say because it is to be used for a PERSISTED column, the '19000101'^** needs to be replaced by 0 or CONVERT(DATE, '19000101', 112):

CREATE TABLE date_time
( d DATE NOT NULL,
  t TIME(7) NOT NULL,
  dt AS DATEADD(day, 
                DATEDIFF(day, CONVERT(DATE, '19000101', 112), d), 
                CAST(t AS DATETIME2(7))
               ) PERSISTED
) ;

^{**: DATEDIFF(day, '19000101', d) isn't deterministic as it does an implicit conversion of the string to DATETIME and conversions from strings to datetime are deterministic only when specific styles are used.}