Sql-server – Why might a table scan have a huge I/O Cost

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

^{(Expanding on my comment on the question.)}

Without a unique constraint on the combination of AreaId and ParameterTypeId, the given code is broken because @UpdatedId = target.Id will only ever record a single row Id.

Unless you tell it so, SQL Server can't implicitly know the possible states of the data. Either the constraint should be enforced, or if multiple rows are valid, the code will need to be changed to use a different mechanism to output the Id values.

Because of the possibility that the scan operator will come across multiple matching rows, the query must eager spool all the matches for Halloween protection. As indicated in the comments, the constraint is valid, so adding it will not only change the plan from a scan to a seek, but also eliminate the need for the table spool, as SQL Server will know there is going to be either 0 or 1 rows returned from the seek operator.