Introduction

For a recent Tsql development project involving text document records, the customer wanted a product that would show the latest and most recent earlier version of specific document text stored in a SQL Server 2014 database. One result set row had to hold all relevant information for each document: both document versions as described above, and relevant metadata. This essentially required fine-grained row pivots. This article will describe the engineering behind the solution, and compare that solution with the SQL Server PIVOT operator.

The Sample Database

This article will use a sample database named DOCUMENT_VERSIONING, built-in SQL Server 2014 Standard Edition on a Windows 10 PC. The database has one table named DOCUMENTS, as shown here:

Otherwise, the database has no user-defined functions or stored procedures, triggers, etc. To build the DOCUMENT_VERSIONING database and its data with the script shown next, first create the directory C:\DOCUMENT_VERSIONING. This directory will host the component .LDF and .MDF database files that the database creation script will build. To build the database itself, connect to the master database in Management Studio, and run this Tsql script in a SQL Server Management Studio query analyzer window:

This script will run successfully, but it could return this error message when run for the first time:

Msg 3701, Level 11, State 1, Line 9
Cannot drop the database ‘DOCUMENT_VERSIONING’, because it does not exist or you do not have permission.

Ignore that error.

The Table Data

This screenshot shows the sample data we’ll use:

Except for DOCUMENT_ID = 2, each document has multiple versions. This query sorted the documents by DOCUMENT_ID ascending, and then DATE_TIME_STAMP descending.

The Solution

We’ll solve the problem with this code:

This screenshot shows the code in a SQL Server query analyzer window:

We’ll examine the code line by line.

The Line 2 USE statement points the query analyzer window to the DOCUMENT_VERSIONING database hosting the DOCUMENTS table. The code will use the table variable @DOCUMENT_TABLE_VAR, declared between lines 4 through 11, as a “scratchpad” for its operations. The Tsql code will generate and store the document version numbers in the VERSION_NUM column, defined in line 10. For the rows “owned” by each separate DOCUMENT_ID, the document version numbers will map to those rows, ordered by DATE_TIME_STAMP descending. The newest row for a document will have a VERSION_NUM value of 1. The next newest row will have a VERSION_NUM value of 2, etc.

The INSERT statement from lines 13 to 24 inserts the DOCUMENTS table rows into the @DOCUMENT_TABLE_VAR table variable. Within the statement, the SELECT statement between lines 17 and 22 returns all columns from the DOCUMENTS table. In addition, it builds values for the VERSION_NUM column. The ROW_NUMBER statement between lines 18 and 21 builds the values in the table variable VERSION_NUMBER column. In line 19, the Tsql PARTITION BY clause builds partitions, or “row windows”, in the data pulled from the source DOCUMENTS table. This line defines a partition as all rows with the same DOCUMENT_ID column value. In those partitions, the line 20 ORDER BY clause sorts the rows by DATE_TIME_STAMP descending. This will place the newest version row of each document at the top of each partition, the next newest version row just below, etc. Line 21 names the ROW_NUMBER values column as VERSION_NUMBER. Line 22 specifies the DOCUMENTS table as the data source. This screenshot shows the result set of this SELECT query:

Note that it matches the original DOCUMENTS table data, with an additional VERSION_NUM column. The result set shown here sorted the version rows by DATE_TIME_STAMP descending in each row window, and the VERSION_NUM column has the expected values. Back in the code, line 23 essentially wraps the query from lines 17 to 22 in a table alias named “TMP.” Line 24 filters this query, to exclude rows with VERSION_NUM values that exceed 3. This approach will keep only the rows with VERSION_NUM values of 1 (the newest versions) and 2 (the next newest). It might seem logical to place the line 24 WHERE clause directly in the line 17 query. However, that approach won’t work, because a SQL Server Tsql query “sees” the SELECT clause after the WHERE clause. As a result, the WHERE clause would never see the VERSION_NUMBER column in the SELECT clause, and the query would crash. If we place the WHERE clause outside this query, we’ll solve the problem. See this earlier article to learn more.

The Tsql query on lines 26 to 33 returns the finished result set. The FROM clause on line 32 defines the @DOCUMENT_TABLE_VAR table variable as the query source table. In line 26, the SELECT clause lists columns directly from the source table. Lines 28 to 31 rely on the VERSION_NUM values to place the DOCUMENT_TEXT and DATE_TIME_STAMP row values into four new columns in the final query result set.

We’ll examine line 28 from the “inside out” to understand the engineering. Line 28 builds values for the NEW_DOCUMENT_TEXT column with this code:

For each result set row, the inner below the TSQL IIF function returns the DOCUMENT_TEXT value if VERSION_NUM equals 1.

For all other VERSION_NUM values, this IIF returns NULL. Line 28 passes this value to the MAX aggregate function. TThe TSQL MAX function takes only one parameter, either a column name or in this case, a string value. For a column name, the MAX function returns the highest value in that column, or each group of rows defined in the query GROUP BY clause. Now, when the MAX function receives a string value parameter, it will return that string, as seen in this screenshot:

Microsoft documentation explains that the MAX function ignores NULL values. Based on all this, line 28 returns NULL if VERSION_NUM does not equal 1, and returns DOCUMENT_TEXT for that row if VERSION_NUM equals 1. Finally, line 28 will alias the column name as ‘NEW_DOCUMENT_TEXT.’ Line 30 operates the same way, for the ‘OLD_DOCUMENT_TEXT’ column. Lines 29 and 31 show that the MIN function works like the MAX function. The Tsql MIN and MAX aggregate functions expect the GROUP BY clause at line 33.

The second screenshot above showed the raw DOCUMENTS table data. For comparison with the query result set, this screenshot again shows the DOCUMENTS table data:

This screenshot shows the result set when we run the entire code sample:

The DOCUMENT_1, DOCUMENT_3, and DOCUMENT_4 rows match our expectations. The DOCUMENT_2 row looks good, because the DOCUMENTS table had only one row for DOCUMENT_2. At line 29, the IIF function returned NULL.

The TSQL MAX function ignored that NULL value, and passed it along to the result set, which we see in the OLD_DOCUMENT_TEXT column for result set row 2. In line 30, the MIN function operated the same way. The DOCUMENT_5 row involved an edge case, because the two newest DOCUMENT_5 rows have the same 2018-07-18 12:09:19.320 DATE_TIME_STAMP values. As a result, the ORDER BY clause in line 19 might not behave predictably. It can randomly sort rows based on matching values.

How about the Pivot Operator?

The solution described above pivoted the DOCUMENTS table data in a customized way. Maybe the SQL Server PIVOT operator could also solve this problem. Using the DOCUMENTS table data, this code uses the PIVOT operator to pivot the DOCUMENT_TEXT column as a partial solution:

This screenshot shows the code in a query analyzer window:

The PIVOT operator from lines 26 to 31 pivots the original DOCUMENT_TEXT column into different columns. It builds a column named ‘1’ for the latest DOCUMENT_TEXT versions, and ‘2’ for the next-latest DOCUMENT_TEXT versions. The Tsql MAX function at line 28 serves as the aggregate function that the PIVOT operator expects. MAX works the same way here as it did in the earlier solution, except that it won’t need a GROUP BY clause. In line 29, the FOR clause tells the PIVOT operator the VERSION_NUM values to use for the pivot columns it generates. The ORDER BY clause on line 32 sorts the result set. The SELECT clause on lines 23 and 24 builds the result set column list, changing the original ‘[1]’ and ‘[2]’ column names to more descriptive names. This screenshot shows the result set:

This query pivoted the DOCUMENT_TEXT values into the NEW_DOCUMENT_TEXT and OLD_DOCUMENT_TEXT columns. Unfortunately, it did not “merge” the rows. The result set we want would likely need a separate result set involving a DATE_TIME_STAMP column pivot, which would have the same problem. Then, we’d have to somehow combine both pivoted, merged result sets into one result set. The whole thing would become really complex. The actual solution shown earlier clearly becomes the better way to go.

Conclusion

For SQL Server data versioning, we need a flexible, effective technique that covers the gaps that the PIVOT operator can’t handle. As we saw, the Tsql MAX and MIN aggregate functions, combined with IIF, will solve the problem with fine-grained control.

• Author
• Recent Posts

Frank Solomon

Frank Solomon started out building Microsoft stack products, and he gradually focused on SQL Server. Some years ago, he began a parallel shift to writing and technical writing. He wrote published articles, he blogs at Bit Vectors, and he co-wrote The SQL Workshop for Packt Publishing, with SQL Shack writer Prashanth Jayaram.

See more about Frank at LinkedIn.
Remove the non-standard characters from this address: fb} <s.author@gmail.com to reach him by email.

Latest posts by Frank Solomon (see all)

• Lever the TSQL MAX/MIN/IIF functions for Pinpoint Row Pivots - May 16, 2022
• Use Kusto Query Language to solve a data problem - July 5, 2021
• Azure Data Explorer and the Kusto Query Language - June 21, 2021