SQL-Server Algorithms : Set-Based Running Subtotals, Moving Averages, and more.

Being an avid Microsoft SQL-Server developer, I love creating interesting algorithms for solving common business requirements — especially stored procedures and user-defined-functions that exploit the relational database strengths of set-based operations.

[UPDATE: JAN-2017] I moved all the free source code for SQL-Server, that I had previously published on another website of mine, onto this blog, and here is a link that searches this blog for all SQL postings.

Set-Based SQL Procedures Overview

I have implemented, using only set-based algorithms (i.e., no database cursors), functionality that many would consider "impossible" without cursors and without dynamic SQL; as such, these techniques may be intriguing and/or useful to other SQL-Server developers, especially web-based applications that need to be secure from SQL-injection attacks.  There is no dynamic-SQL used and no cursors used to implement these algorithms — seriously. 

The following table provides links to the various free source code for the stored procedures I have published under the terms of the MIT License (for freeware) on my company website's free source code library.  All of these techniques have been tested and developed using Microsoft SQL-Server versions: SQL Server 2005, SQL Server 2008, and SQL Server 2008r2.

SQL Server Set-Based Running Subtotals (i.e., Row-Level Accumulators)

SQL Server Set-Based Running Subtotals (i.e., Row-Level Accumulators) for Delimited-list Accumulator Functionality

SQL Server Parameterized GROUP BY Without Using Dynamic-SQL : The "impossible" is possible.

SQL Server Parameterized ORDER BY without Dynamic-SQL - Example 1

SQL Server Parameterized ORDER BY without Dynamic-SQL - Example 2 - With ASC/DESC by Column abilities

SQL Server Set-Based Moving Averages (without Cursors, self-joins, or sub-selects) - Example 1

SQL Server Set-Based Moving Averages (without Cursors, self-joins, or sub-selects) - with Break-Level Resets

SQL Server Set-Based Running String Accumulator with Break-Level Reset

There are also some related MS SQL-Server stored procedures and functions on the main-page of the "library" including routines and functions for splitting and parsing strings, padding numbers to fixed-width, accumulating totals of values within comma-delimited strings, comparing nullable columns, and more.  I also posted a series about tuning very large SQL-Server databases for anyone dealing with massive database performance-tuning issues.

I hope you find SQL-Server set-based algorithms, stored-procedures, and functions helpful when tackling common business-requirements.  I have used the SQL running-subtotals and moving-averages to implement some neat financial data modeling and analysis software (everyone is used to moving-average stock-prices and such, right?), and the order-by operations without dynamic-SQL have been quite handy on websites (preventing potential security issues associated with dynamic-SQL and SQL-Injection attacks).


Continue to read this Software Development and Technology Blog for computer programming articles (including useful free / OSS source-code and algorithms), software development insights, and technology Techniques, How-To's, Fixes, Reviews, and News — focused on Dart Language, SQL Server, Delphi, Nvidia CUDA, VMware, TypeScript, SVG, other technology tips and how-to's, plus my varied political and economic opinions.