The Helper Table Workbench

 

Sometimes, when writing TSQL code in functions or procedures, it
is tempting to do iterations, or even worse, a cursor, when it isn't
really necessary. Cursors and iterations are both renowned for slowing
down Transact SQL Code SQL Server just isn't designed for it.
However, there is usually a way to do such operations in a set-based
way. If you do so, then your routines will run a lot faster, with speed
at least doubling. There are a lot of tricks to turning a problem that
seems to require an iterative approach into a set-based operation, and
we wish we could claim we'd invented one of them. Probably the most
useful technique involves that apparently useless entity, the 'helper'
table. This workshop will concentrate on this, because it is probably
the most widely used.
The most common Helper table you'll see is a table with nothing but the
numbers in a sequence from 1 upwards. These tables have a surprising
number of uses. Once you've understood the principles behind helper
tables, then you'll think of many more. We'll be providing several
examples where a helper table suddenly makes life easier. The objective
is to show the principles so that you'll try out something similar the
next time you have to tackle a tricky operation in TSQL.

As always, you're encouraged to load the example script into Query
Analyser or Management Studio, and experiment!
Our examples include:
Splitting Strings into table-rows, based on a specified delimiter
Encoding and decoding a string
Substituting values into a string
Extracting individual words from a string into a table
Extracting all the numbers in a string into a table
Removing all text between delimiters
Scrabble score
Moving averages
Getting the 'Week beginning' date in a table
Calculating the number of working days between dates.

Source: The Helper Table Workbench