You are right, if I declare the year column (and the partition function) with BIGINT instead of SMALLINT the implicit convertation will work and it scans only one partition

when I have to sent passwords per email, I try to include neither the username nor the app / site that uses the password and tell the person what it...

Nice article, but

- minus point for the usage of [dbo].[udfBuildISO8601Date] (UDFs are slow); there is a built in function DATEFROMPARTS() with the same syntax, that outputs a date. If necessary...

I'd say, that - depending on which index it uses and / which other sorts it has to do / when you are MERGE joining / going parallel / are...

... Furthermore without the ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW it would be slow because with the default RANGE-parameter it has to spill out to TempDB.

@topic:

- you should never use FIRST_VALUE without "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW", otherwise it will always spill out to TempDB -> slow

- for this reason I prever to...

Yes - it seems so. But please don't ask me, if this is a bug or intended behavior (I tend to bug or at least misbehavior, because MSSQL usually do...