Is ok to use functions to format columns inside OVER PARTITION BY?

I got a column that its maximum length is 8 characters, but usually it has less. I tried to normalize it, using the RIGHT function. It works very well on JOINS, and it doesn’t show syntax errors when I use it in OVER PARTITION BY function. I run the query many times but I think I don’t have a database to prove my point, but I want to know if it could be a problem in order to get the total number of rows with the same vcAuth.

SELECT TOP 1 @iT40Total = COUNT(*) OVER(PARTITION BY RIGHT('00000000'+ [vcAuth],8) )
FROM MyTable
WHERE [bProcessed] = 0

My desired result is the number of rows with the same AuthCode. It works just with @iT40Total = COUNT(*) OVER(PARTITION BY [vcAuth] ) but using it, I don’t know if there is another row with more zeros.

Answer

There is no problem. You can use almost any scalar expression for the partition by and order by clauses with a window function. To be honest, I have never tried using subqueries, but it is hard to imagine a situation where those would be useful.

That said, using TOP without ORDER BY is suspicious. And your code snippet is better written with GROUP BY:

SELECT TOP (1) @iT40Total = COUNT(*) 
FROM MyTable
WHERE [bProcessed] = 0
GROUP BY RIGHT('00000000' + [vcAuth], 8)