The Performance of Window Aggregates Revisited with SQL Server 2019

T-SQL window functions have been a fantastic addition to the T-SQL language. In this article, Kathi Kellenberger reviews how optimizations available in SQL Server 2019 can improve the performance of these functions.

Implicit function overloading

Bowled Over by SQL Window Functions

What better way to learn how to construct complex CHECK CONSTRAINTs, use the SQL 2012 window frame capability of the OVER clause and LEAD analytic function, as well as how to pivot rows into columns using a crosstab query? Create the SQL code to score Ten-pin Bowling, of course. Dwain Camps explains the how, what and why.

Lead and Lag Functions in SQL Server 2012

Arshad Ali discusses how to use CTE and the ranking function to access or query data from previous or subsequent rows. He also shows you how to leverage the LEAD and LAG analytics functions to achieve the same result without writing a self-join query using CTE and ranking function

Is It Time To Clear Out Those Quirky Functions?

Phil Factor ponders a cull of some of SQL Server's worst-behaving functions.

Stairway to XML: Level 9 - Creating XML-Based Functions

In this Level, we’ll look at how to use XML methods within user-defined functions to return XML fragments and values from your target XML instance.

Unused Input Parameters

Identify all objects containing unused input parameters.

An Implementation of the FNV1a Hash Algorithm for SQL Server

Three CLR-resident functions for 16-, 32-, and 64-bit FNV1a hashing

Find all Procedures, Views, Functions that have been renamed

Find all Procedures, Views, Functions that have been renamed with sp_rename where syscomments does not match.

RE: dynamic sql vs. clustered index scan

Or you could keep your static SQL but rather than make use of your charindex statement, which cannot use any indices, you can use a UDF that returns a table. For example, this UDF will take a comma separated list of integers as a string and returns a table (I think I got this from […]