Of course, the whole idea about insertion sort being done in TSQL 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 TSQL 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 TSQL 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. TSQL implementation of insertion sort
Terrible, right? I know. When you could simply do the following instead: ORDER BY
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 TSQL implementations quite often defeat the holy idea of SQL.
0 Comments

AboutBlog about my programming experiments, tests and so on. Categories
All
Archives
November 2016
