I just finished this particularly awesome new Ben-Gan article about the Batch Mode Window Aggregate Operator in SQL Server 2016. He refers to this as the third major milestone in SQL Server's support for Windows functions (the first being their introduction in 2005, the second was framing and windowing in 2012). The article is a goldmine of content about speeding up window functions.
I'd love to see articles about the the Batch Mode Window Aggregate Operator and how to leverage these window function performance enhancements.
-- Alan Burstein
Best practices for getting help on SQLServerCentral -- Jeff Moden
How to Post Performance Problems -- Gail Shaw
Nasty fast set-based string manipulation functions:
For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)
To split strings based on patterns try PatternSplitCM
Need to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8K
I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. -- Itzik Ben-Gan 2001