Of course, the whole idea about insertion sort being done in T-SQL is absurd. The following lines were written just for fun and also to point out that it happens surprisingly often that I see other programmers approaching the SQL in a procedural way. SQL is a declarative language and works the best when is approached in a declarative way. It's not C# or object oriented language in general. Rather a set oriented language.
How often have you seen usage of cursors, loops and other similar constructs in T-SQL on cases where it could have been done just using a plain SQL? It wasn't so rare in my career. Such an usage is sometimes necessary however many times it was a quick and of course a dirty solution for a given task.
If you don't know what the insertion sort is check the wikipedia page about insertion sort
Consider the insertion sort algorithm that is in O(N^2). The following T-SQL code tries to sort an array of integers by implementing a couple of LOOP statements.
First, let's prepare example data
This simply adds 1000 of random integers to an array. No big deal. Now, let's implement the insertion sort.
T-SQL implementation of insertion sort
Terrible, right? I know. When you could simply do the following instead:
Here comes the best part. Compare the times of both methods. On my Core i7, 8GB RAM machine the results are as follows:
This is of course an extreme case. I believe nobody would really consider the sorting being done manually. This article only demonstrates that T-SQL implementations quite often defeat the holy idea of SQL.
Blog about my programming experiments, tests and so on.