Digital Increment

A few times I've needed to create a numbered list from a SQL script where the underlying table didn't have numeric data types.   Sometimes something like this is from an internal customer, other times it is needed for development purposes. It's a fairly easy script that could be easily implemented so you don't have to make a temp table or variable table just to have a numbered list.  The keyword and syntax is as follows: ROW_NUMBER () OVER ( PARTITION BY <column_name> ORDER BY <column_name> ) That’s it!  As a quick example, here it is on the AdventureWorks2012 database.  The query: SELECT p . FirstName , p . LastName , e . JobTitle  ,        ROW_NUMBER () OVER ( PARTITION BY p . PersonType ORDER BY p . LastName ) AS place FROM person . person p        INNER JOIN HumanResources . Employee e ON e . BusinessEntityID = p . BusinessEntityI...

A few weeks ago I did a tutorial on how to pivot data in MS SQL Server.  In some cases, one may need to unpivot data in order to place it in a normalized database structure.  Many MS Excel experts can easily manipulate data so they can see it any kind of way.  Similar tools exist in SQL Server, but obviously the ways to achieve the same ends are different.  Here is a visualization of the data: ID Another ID Week1 Week2 Week3 Week4 Week5 Week6 100 A11 200 500 300 600 450 100 So, in this situation a client may send us sales data for 6 weeks that looks like this and it needs to be not only placed in the database to archive it and join it to other entities or dimensions (if it is a data warehouse).  To make sure the data will go in a normalized table or a fact table, the data will need to be unpivoted to look like this: ID Another ID ...

A few days ago I figured I’d add to my SQL repertoire and lo and behold I found that Microsoft added a few new SQL constructs to the T-SQL language. I remember how I felt about using the MERGE construct and in some instances it was very useful, but when I discovered IIF , CHOOSE , & THROW ( amongst some of the others like PARSE , CONCAT & ROWS ) I knew that I found some new toys. Let’s take a look at IIF , CHOOSE , & THROW . IIF If you’ve been using expressions in SSIS or VB.Net or even VBA, you should be familiar with “Inline If”.  This function is prototyped as such: IIF([boolean evaluation], [return value if evaluation is true], [return value if evaluation is false]) This function is awesome as the evaluation can be assigned to a variable.  There are SQL constructs like ISNULL , but IIF is way more effective when writing more robust & dynamic SQL scripts.  It can be used such that: DECLARE @c INT = 10 ...

Recursion is a process that was taught in computer science to traverse large datasets as an alternative to loops. When traversing linked lists and stacks recursion was used in C++, but recursion can be implemented in most programming and scripting languages.  Nowadays, recursion is most effective in traversing XML documents, database relationships, or perhaps solving equations.  For this situation, we’ll implement recursion in a SQL Server stored procedure.  This will be simple addition up to 31 as SQL Server has set limits on outside procedure calls (the database doesn’t want you to call more than 32 methods/instructions.  When it comes to the stack in SQL Server, it wants to run pretty lean and too many calls to external processes can cause memory problems in SQL Server; the @@NESTLEVEL refers to how many function calls are made in SQL Server). create procedure dbo . recursive_stack ( @i INT ) -- dbo.recursive_stack 23 AS begin if @@...