Paul

Following some reading I would now phrase it as the result set is identical, but the result sequence of the two functions are different (but repeatable and consistent)

You notice the first Index Scan is Ordered True and the second is not? Maybe I'm missing something but this optimisation looks correct to me. The first query partitions using the order already given by the clustered index. The second query has to re-order to meet the partition specification, hence the "extra" sort

I wouldn't write a function if that function was only called once from one place.

As this sounds like a login for customers as opposed to staff you have zero chance of people remembering the password they generated in your overly complex system. Therefore all passwords will be store on a Google docs sheet, or they will use your password retrieval system each and everytime. You may as well use one of the open ID systems instead seeing as you'll be use Google's by defacto.

Is your "boss" the head of the company or just the manager of your team?

@AaronBertrand I disagree it was misleading. You've also misrepresented the English SE question. I think this is going nowhere though, hopefully we all take away something from it.

Right. Integrity checking ([2]) is not balance updating ([1]).

@AaronBertrand I like the way you framed that query. But I believe you misrepresented the situation. If the initial query is "Is there a better way to use [x] to achieve [1]?" then the follow up comment from me was "Why do you want to use [x] and not [y] to achieve[1]? Also have you considered using [z] to achieve [2]?". I think trying to add that to your existing question without muddling up those already involved would be quite futile. Anyway, I'm about to log off for the day, have a good one.

@AaronBertrand from dictionary.com for 'also' "1. in addition; too; besides; as well: He was thin, and he was also tall. ". Seems clear to me from just that. Never mind I posited using a task to "integrity check", to check balances match transactions. Not to set balances to transactions. Asking me where I learned English (England btw) that's an example of being snippy.

@AaronBertrand I believe "Also" is quite clear. Feel free to cross post as a query to the English SE site ;)

@AaronBertrand I DID NOT recommend that. Forgive the caps but it's the 3rd time I've needed to state it to you. I quite clearly stated a consideration for a checking process outside of the consolidation process. The reason for this is to ensure the balance hasnt become invalid in some way. e.g. altered outside of the trigger, transaction values changed outside of the business layer, business rules being changed without thought to prior timeline. I worked with such a system for 10 years and integrity of the account values was always being put at risk by sloppy code and bespoke account 'fixes'

@AaronBertrand there's no need to be snippy. I've seen you go off on tangents that don't speak directly to a question being asked. And as stated, no one said to use tasks to consolidate the balances.

@AaronBertrand I did not recommend scheduled tasks to do the balance modifications

#2 Why do you want to do it in SQL and not in an application? Also have you considered integrity checking as a scheduled task? (Go through each account and ensure each balance matches the transactions)