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.

VMware ESXi 5.0 Release Features of Interest

I am a rather ardent fan of VMware (Public, NYSE:VMW) computing hardware virtualization products, especially VMware Workstation and VMware vSphere ESXi (a "bare iron" virtualization product). As a software developer, these tools give me the ability to maintain many simultaneous testing environments on any given desktop or server I employ for writing, testing, or deploying software — a huge time and cost saver for me.

-----------------------------------------------------------------------------------------------------------
UPDATE (2011-07-20): VMware has released their official guides to What's New in ESXi 5.0 and vSphere 5.0 since this blog was first written, and I have now written a followup blog (to this one) about the New Features in VMware ESXi 5.0 and vSphere 5.0 that goes into much more detail about certain aspects of the products and the new features.  But, I did not necessarily repeat every bullet-point from this initial post, so you may find some nice info on this page also.

-----------------------------------------------------------------------------------------------------------

The most recent official release of VMware's vSphere ESXi server virtualization / hypervisor product — a free bare-metal hypervisor — was version 4.1 (with update 1), though VMware ESXi 5.0 is nearing on the horizon now, and promises to introduce some features I have been longing for.  In case you need help migrating from 4.0 to 4.1 (in preparation for the upcoming 5.0 release), I wrote a blog about How to Upgrade VMware ESXi 4.0 to 4.1 recently.

I find version 4.x of ESXi to be quite capable for most of my needs, but there were a couple features in the Workstation virtualization product that I wanted to see on my server (via ESXi).  Some early feature-set "leaks" and "rumors" are hitting the technology blogs and other technology news sites now, and if what I am reading comes to fruition in the vSphere 5.0 version of the ESXi hypervisor, I will be quite pleased.

Note: I may not be the "typical" ESXi user, since I do not necessarily use the platform for all its enterprise-grade features, but I doubt I am alone in my using ESXi as a sort of "big brother" to my VMware Workstation desktops.  I am a software developer and SQL-Server database aficionado, and this leads me to using my Server platforms for what is a mix of enterprise-level database-design/testing in addition to actually writing software inside VM's hosted on vSphere ESXi.  I need some of the large-CPU-count limits and memory-size limits the product offers as well as its support for some more enterprise-grade hardware, but I also like features (introduced in v4.x) that included high-resolution graphics for my "server" VMs so that I could enjoy screen-real-estate when editing large blocks of code and testing software sporting robust GUIs.

New Features to Expect
So, with the above in mind, client-connected USB devices are something I would really like to see in the VMware ESXi 5.0 new features, as there are times I am using my server with ESXi to test some software that interacts with a USB-based product — be it a USB printer, USB scanner, USB camera, USB memory-stick, or USB external hard-drive.  From what I am reading, this feature is coming in vSphere 5.0!  In addition, some other noteworthy vSphere 5 / ESXi 5 features that are being talked about include the following (with the ones I listed first being of most interest to me):

• USB 3.0 device support — to me, this is a "must have" while on the topic of USB-connectivity these days.  I have seen first hand how much faster (than USB 2.0) my USB 3.0 is for large file-transfers to my Seagate FreeAgent GoFlex external USB 3.0 drive (using a Buffalo USB 3.O add-in card).  There is no comparison... the USB 3.0 is many multiples faster, and large-file transfers are done in a fraction of the time; as such, having this available within my ESXi 5.x virtual environment would be wonderful!
• Smart card reader support for VMs — again, this is complements my desire for more "workstation-like" features when I need them. 
• Swap to SSD (Solid State Disk)  — this should be really nice for speed-improvements.  I do not know the details, but I am a fan of SSD devices and their speed.  I blogged about my Intel X-25M benchmark results in a desktop I use for software development, and the SSD products have only gotten better, faster, and more affordable since then.  Being able to perhaps cache portions of frequently-accessed data using an SSD (much cheaper than RAM), will be nice indeed.
• Apple Mac OS X Server 10.6 "Snow Leopard" guest OS support — this is a nice-to-have for me, as I have been wanting to play with more Apple code without necessarily purchasing an Apple server.
• Non hardware-accelerated 3D graphics for Windows Aero support — again, a nice-to-have from where I stand, if I want to test Windows 7 guests on ESXi under certain circumstances
• a new browser-based vSphere Client — what I find most interesting with this is that it may be *extensible*, which would perhaps make it easier for open-source (OSS) add-ons of things like free backup software for ESXi 5.x and similar much-desired functionality.  I look forward to seeing how truly extensible this product is, especially as a software-developer that would love to write some handy plug-ins for things I commonly desire.
• iSCSI user interface support  — this could be interesting too.  I have been just starting to play with FreeNAS and NexentaStor and their iSCSI support and capabilities.  Anything that makes it easier to integrate this type of functionality with ESXi is welcome by me.
• Support for 2TB+ LUN  — in this day and age of ever-increasing data and storage requirements, coupled with ever-cheaper storage, the existing 2TB LUN limit seems a bit dated.  So, being able to provision for very, very large storage requirements is sure to help with things like massive databases and the like.  I personally do not keep any "test databases" of that size laying around, but I can see the day coming where I may want to do so for performance testing, benchmarking, and similar optimization work.  I have some multi-hundred-gigabyte SQL-Server databases for testing some of my algorithms on, so perhaps the multi-TB threshold is coming (though, I am not sure if the native OS will support that anyhow? hmmm...)
• 512 VMs  — I am not near that limit by any means, but I could see this making some sense if and when my vision of "a VM per application" ever comes to fruition; that is a vision I have written about elsewhere... perhaps I will publish it here soon.
• 160 CPUs and 2TB of RAM!  uhh... wow!  Sounds nice, and wish I had such a system just for "kicks".
• 32 vCPUs per VM

There were plenty of additional enterprise-focused features talked about on the web (for vSphere 5), but most of them will not be very applicable to me.  I will keep my eye out for more "official" release information from VMware, and once some of these rumors/leaks of new features can be confirmed, I will surely write about them (and/or test them out and write about them).  Stay tuned.

Something Amiss: Nvidia CUDA support in ESXi / vSphere?

I did not read anything about VMware implementing support for Nvidia CUDA technology in vSphere5 / ESXi 5.  I sure hope they are considering this, as I see parallel-computing with CUDA as a major and serious technology trend that can not be ignored, and that should be available to server-applications.  I have written a few blogs about CUDA-enabled graphics cards and the potential they offer us software-developers that can leverage the power of the many-GPU-cores for parallel operations; this is an emerging trend in high-powered software that needs to be more accessible via virtual machines.

The last time I researched whether there was any way to make the NVidia graphics cards and their CUDA processors available to ESXi virtual machines, I found that you were out of luck.  Even attempts to use an Nvidia CUDA card as a "pass through" device (to ESXi) would not work.  Basically, unless VMware implements (in their hypervisor) something to allow the CUDA processing architecture capabilities to be accessed through server-based virtual machines, any applications that implement such features will have to run outside of a VM.  

With server vendors offering dedicated parallel-compute modules (implemented using NVidia cards), there is bound to be more and more demand for VM support of CUDA.  Financial firms, medical technology, engineering firms, and other large corporations are embracing CUDA-based applications development, and this surely must translate into demand for CUDA-enabled virtual-machine infrastructures.  So, I sure hope this is addressed in v5 if not in a subsequent "point release" of ESXi 5.1 or 5.2 (though, it is a rather substantial feature for a dot-release perhaps).

I am waiting anxiously to see how EXSi CUDA support is addressed in upcoming releases.  Aside from that, ESXi 5.0 looks rather promising so far.