by Marlon Ribunal
1 Comment

How to resequence column based on numeric prefix using TSQL

Beginning in version 2005, SQL Server is making manipulation of  partitioned set of rows easier by using Window Functions (ROW_NUMBER(), aggregate functions with OVER(), etc.). You can manipulate partitions of rows on the fly with Window Functions.

I’ve seen how complicated queries that require recursion were simplified by using combination of   Common Table Expression (CTE) and Window Functions. There are less reasons now, or almost zero excuses, to justify using cursors in your queries.

I had a data requirement that I thought on the onset that I could apply the awesomeness of CTE’s and Window Functions on. I needed to process a dataset into deterministic output. I had to output the data in pre-determined sort order. Each partition would have maximum of 10 rows.

To preserve the deterministic order of my data, I would have to prefix each data with 0,1,2,3,5,6,7,8,and 9 within the partition group.

Easy, right? Not really. The problem was my input (bar delimited string) has values that could exceed that 10 max. In cases when the number of values exceeds 10, I would grab the last 10 values. The number of values is not fixed. Some record would have 2, 3, 11, 99, or who-knows-what number of values. Regardless of the number of values, I need to grab the last 10.

Since I don’t know how many values there are in a given record, I would have to read beginning from the last value by reversing the whole string input, and reversing the results back to normal order. I have a parse function that split the values in the bar delimited string input. Since I am parsing the input in reverse, the 0 index gets assigned to the actual last value in the string (reversed).

I was working on SQL Server 2000 for this requirement :-(

To give you an idea of what I am talking about, here’s my input (left) and the desired output (right). Basically, I needed to resequence the numeric prefix in order, i.e., 0,1,2,3…9 from an input of x…3,2,1,0

resequence tsql sql server

Let me walk you through step by step…

Let’s build the sample dataset. Pardon my insert constructor. Remember this is SQL 2000. It could have been nicer, I know.

SELECT x.part_no, x.sub_part_no
INTO #temp
FROM ( SELECT 'a' AS part_no, '7ABC' AS sub_part_no
SELECT 'a' AS part_no,'6DEF' AS sub_part_no
SELECT 'a' AS part_no, '5GHI' AS sub_part_no
SELECT 'a' AS part_no, '4JKL' AS sub_part_no
SELECT 'a' AS part_no, '3MNO' AS sub_part_no
SELECT 'a' AS part_no, '2QRS' AS sub_part_no
SELECT 'a' AS part_no, '1TUV' AS sub_part_no
SELECT 'a' AS part_no, '0WXY' AS sub_part_no
SELECT 'b' AS part_no, '4A12' AS sub_part_no
SELECT 'b' AS part_no, '3B34' AS sub_part_no
SELECT 'b' AS part_no, '2C56' AS sub_part_no
SELECT 'b' AS part_no, '1D78' AS sub_part_no
SELECT 'b' AS part_no, '0E01' AS sub_part_no
) x

This is how the pre-processed dataset looks like:

input sequence tsql

SQL Server 2000

The next step is to find out the maximum, or highest, number of prefix (sequence) in each partition (part_no in this case). You probably already know what I am trying to do here.

SELECT part_no
,LEFT(sub_part_no, 1) AS sequence_no
FROM #temp y

max sequence tsql

A simple subquery (subselect) returns exactly that:

SELECT part_no
 ,( SELECT MAX(LEFT(sub_part_no, 1))
 FROM #temp x
 WHERE x.part_no = y.part_no
 ) AS max_sequence
FROM #temp y

resequencing values in tsql

Now that I have determined the max value of my prefix (sequence) within my partition, I lay that next to my original sequence and let simple arithmetic do its magic.

SELECT part_no, max_sequence, sequence_no, cast(max_sequence AS INT) - CAST(sequence_no AS INT) AS new_sequence
SELECT part_no
,(SELECT MAX(LEFT(sub_part_no,1)) FROM #temp x WHERE x.part_no = y.part_no) AS max_sequence
,LEFT(sub_part_no,1) AS sequence_no
FROM #temp y
) xx

new sequence tsql

Putting that together…

SELECT z.part_no
 ,sub_part_no AS old_sub_part_no
 ,CAST(CAST(max_counter AS INT) - CAST(LEFT(sub_part_no, 1) AS INT) AS VARCHAR(2)) + RIGHT(sub_part_no, LEN(sub_part_no) - 1) AS new_sub_part_no
FROM ( SELECT part_no
 ,( SELECT MAX(LEFT(sub_part_no, 1))
 FROM #temp x
 WHERE x.part_no = y.part_no
 ) AS max_counter
 FROM #temp y
 GROUP BY part_no
 ) z
ORDER BY z.part_no

Another option is to simulate the ROW_NUMBER() window function (reference here)…

SELECT part_no
,sub_part_no AS old_sub_part_no
,CAST(z.rowNumber AS VARCHAR(1)) + RIGHT(sub_part_no, LEN(sub_part_no) - 1) AS new_sub_part_no
FROM ( SELECT part_no
,( SELECT COUNT(*) - 1
FROM #temp AS x
WHERE x.part_no = y.part_no
AND x.sub_part_no >= y.sub_part_no
) AS rowNumber
FROM #temp AS y
) AS z
ORDER BY part_no, new_sub_part_no

tsql resequence output
And there you have it. That’s a simple way of resequencing values within partition in TSQL.

 SQL Server 2008/2012/2014

UPDATE: (3/26/2015)

Thanks, Bob for the script (see comment below). I tested this with SQL Server 2008 R2 and SQL Server 2012

SELECT part_no
,sub_part_no AS old_sub_part_no
,CAST (ROW_NUMBER() OVER ( PARTITION BY part_no ORDER BY CONVERT(INT, LEFT(sub_part_no, 1)) DESC ) - 1 AS VARCHAR(1)) + RIGHT(sub_part_no, LEN(sub_part_no) - 1) AS new_sub_part_no
FROM #temp

by Marlon Ribunal

Executing scripts on multiple servers the easy way

Central Management Server and server groups provide a convenient way to manage multiple servers and databases in one place and at the same time.You can register and manage any servers but only those in SQL Server versions 2008 and higher can be designated as Central Management Server. You can execute TSQL scripts on any or all the servers registered under the Central Management Server at the same time. If you have a Policy-Based Management structure in place, the Central Management Server makes it easy to execute policies on multiple servers at once. I’ve said that enough – at the same time. Click once, and you’re done with all the servers.

But what if, for some reasons, you cannot use Central Management in your environment?

This is the reason why tools like SQL Multi Script from Red Gate Software exist. Some of the features I like are:

  • Set the execution order of multiple scripts
  • Query databases in parallel
  • Messages from SQL Server displayed for all databases
  • Execute just the selected text in a script (just like in SSMS)

Let’s take a look. For this demo, we’re using two SQL Server 2012 instances from two separate servers on a domain. This demonstrates that we can use the SQL Multi Script tool on multiple servers across a network.

The first time you run the tool, you need to build your Database Distribution List:

1. Click the Configure button Build Database Distribution List 2. On the Configure Database Distribution List Dialog, click the New button. Type in a name for the new distribution list, then click Create. Let’s put SQL2012_Dev_Servers for this demo. This list is for databases, but I’d still like that “servers” in the nomenclature to have that logical grouping by servers. Using distribution lists is a good way of having that separation among server groups; e.g., Test, Dev, QA, Production, etc. So name your lists as intuitive as possible. Creating New Distribution List in SQL Multi Script 3. Still on the Configure Database Distribution List Dialog, click the Add a SQL Server Not Listed button. Type in the SQL Server whose databases you want to add to the distribution list, specify your authentication credentials, then click Add. Remember the distribution list is for databases, not for servers. Repeat this step for all the servers you want to add. Add SQL Server to Multi Script Distribution List 4. Now that we have added the servers, let’s add the databases we want to list in our SQL2012_Dev_Servers distribution list. Select the databases you want to add in the distribution list. You can multi-select databases among the servers by pressing CTRL + Right Click key. Then, click the Add button. Select database to add to the multi script distribution list 5. You can now see the databases in the Databases to Execute Against pane under the Distribution list. Click OK. Add database to databases to execute against in SQL Multi Script 6. You can create a new script via the SQL Multi Script editor; and, save that script for later use. You can also add an existing script. For this demo, let’s do the latter. Click the Add button. Select the script you want to add from the Add File Dialog box. I’m adding two scripts for this demo: a script that creates a database called DBTools and sp_Blitz by Brent Ozar Unlimited. I don’t intend to violate the sp_Blitz trademark here. I just want to demo that an enterprise-ready script such as sp_Blitz can execute in SQL Script.

add existing scripts to SQL Multi Script

 7. Like I mentioned in the beginning of this post, one of the features that I really like in SQL Multi Script is the ability to set the execution order of multiple scripts. The first script, Create_Database_DBTools.sql, creates a database called DBTools. The second script, the sp_Blitz (Brent Ozar Unlimited trademark), creates the sp_blitz stored procedure on the DBTools. Let’s order the two scripts so that the Create_Database_DBTools runs first before the sp_blitz script; otherwise, it will throw a missing object error. I modified the sp_blitz to run on the DBTools database instead of the default database (master).

Let’s parse the script to make sure our scripts are error-free. In this demo, I’m getting a “DBTools database does not exist” error, which makes sense, because the DBTools database has not been created at this point.

One of the features of SQL Multi Script is its ability to display Messages from SQL Server, which exactly is what we see here:

SQL Server message displayed on SQL Multi Script

8. If you’re executing long and complicated scripts, encountering errors is a possibility. SQL Multi Script provides a list of actions to be taken when an error occur; i.e., Continue Executing, Skip script on database with error, Stop executing on database with error, and Stop executing.

On error action when script has error on SQL Multi script


9. Let’s execute our scripts. We should get a confirmation that the scripts ran successfully. The tools did not encounter an error this time because we executed the two scripts in the correct order.

Script run successfully confirmation on SQL Multi Script

10. Let’s check our databases through SSMS, and make sure that the objects we just created through SQL Multi Scripts exist. The following screen captures show that the DBTools database and sp_blitz stored procedure objects are indeed created on both the SQL2012-A and SQL2012-B servers.

objects created by SQL Multi Script

database objects created by SQL multi Script

11. But here’s more. Here’s another feature I like in the tool – it’s ability to display the results of the script. Let’s execute the sp_Blitz via the SQL Multi Script tool. But before we do that, let’s add the DBTools from both SQL2012-A and SQL2012-B to our SQL2012_Dev_Servers distribution list. This time, instead of adding an existing script, let’s create a new script in the editor:

EXEC [DBTools].[dbo].[sp_Blitz]
@CheckUserDatabaseObjects = 1 ,
@CheckProcedureCache = 0 ,
@OutputType = 'TABLE' ,
@OutputProcedureCache = 0 ,
@CheckProcedureCacheFilter = NULL,
@CheckServerInfo = 1

Let’s execute that TSQL. SQL Multi Script should be able to show us the results of the sp_Blitz stored procedure, like this:

SQL Multi Script Query Results

The SQL Multi Script is a neat tool. I recommend it if you’re looking for something like this.

by Marlon Ribunal

Laying The Ground For A Successful Reporting Project

Building a reporting system from the ground up is a daunting task. Such a big project usually involves intricate resource movement. Reporting is an important component of Business Intelligence (BI). Therefore, perceptive implementation options of the reporting portion should be considered. Data analysis and integration become pointless if they lack […] Continue Reading…