SQL Server GUID and UuidCreateSequential sorting

Recently I worked on an issue which was related with SQL Server uniqueidentifier columns. Almost all tables in our system used a uniqueidentifier column as the primary key and clustered index, but we didn't use NEWSEQUENTIALID as the default value for the column. Instead we was using UuidCreateSequential() to generate sequential IDs in the .NET code. However, the generated Guids were not really properly sorted in SQL Server. I started to look into how these sorting algorithms work.

1. SQL Server Guid sorting

I created a list of Guids in SQL Server and this is the sorting result, from less to more:

01000000-0000-0000-0000-000000000000
00010000-0000-0000-0000-000000000000
00000100-0000-0000-0000-000000000000
00000001-0000-0000-0000-000000000000
00000000-0100-0000-0000-000000000000
00000000-0001-0000-0000-000000000000
00000000-0000-0100-0000-000000000000
00000000-0000-0001-0000-000000000000
00000000-0000-0000-0001-000000000000
00000000-0000-0000-0100-000000000000
00000000-0000-0000-0000-000000000001
00000000-0000-0000-0000-000000000100
00000000-0000-0000-0000-000000010000
00000000-0000-0000-0000-000001000000
00000000-0000-0000-0000-000100000000
00000000-0000-0000-0000-010000000000

Alberto Ferrari's great post How are GUIDs sorted by SQL Server listed the sorting order. I think the opposite way, i.e. from the most important to least important, can help me understand the order better. When we compare things, the logic way is comparing from the most important to the least important. So I slightly modified the order as this:

• A..F are evaluated in left to right order and are the most important, then
• 8..9 are evaluated in left to right order, then
• 6..7 are evaluated in right to left order, then
• 4..5 are evaluated in right to left order, then
• 0..3 are evaluated in right to left order and are the less important

2. UuidCreateSequential() Generated Guid sequence

I wrote a test program to generate a series of Guids with UuidCreateSequential(), and here are some examples:

afee48fe-9abe-11e6-9697-005056c00008
afee48ff-9abe-11e6-9697-005056c00008
afee4900-9abe-11e6-9697-005056c00008
afee4901-9abe-11e6-9697-005056c00008

fe89491d-9adc-11e6-9697-005056c00008
fe89491e-9adc-11e6-9697-005056c00008
052ccf38-9add-11e6-9697-005056c00008
052ccf39-9add-11e6-9697-005056c00008

We can see in bytes 0..3 and 4..5, the significance is from left to right, and the right is less important. Since it was difficult to generate Guids to reflect other bytes, I referenced some posts, Unraveling the mysteries of NEWSEQUENTIALID and How to generate sequential GUIDs for SQL Server in .NET, to get the conclusions that the sequence is like the following:

• A..F are evaluated in left to right order and are the most important, then
• 8..9 are evaluated in left to right order, then
• 6..7 are evaluated in left to right order, then
• 4..5 are evaluated in left to right order, then
• 0..3 are evaluated in left to right order and are the less important

So basically in each group, the order always is from left to right, from most important to least important.

3. Guid comparison in .NET code

When I worked on getting the sequence generated by UuidCreateSequential(), I thought I could use Guid.CompareTo() to compare a list of Guids.  I used the Bubble Sort to sort the list and got the following order, from small to big:

00000000-0000-0000-0000-000000000001
00000000-0000-0000-0000-000000000100
00000000-0000-0000-0000-000000010000
00000000-0000-0000-0000-000001000000
00000000-0000-0000-0000-000100000000
00000000-0000-0000-0000-010000000000
00000000-0000-0000-0001-000000000000
00000000-0000-0000-0100-000000000000
00000000-0000-0001-0000-000000000000
00000000-0000-0100-0000-000000000000
00000000-0001-0000-0000-000000000000
00000000-0100-0000-0000-000000000000
00000001-0000-0000-0000-000000000000
00000100-0000-0000-0000-000000000000
00010000-0000-0000-0000-000000000000
01000000-0000-0000-0000-000000000000

To surprise me, this order is totally different with previous two.  It seems just simply compare byte by byte, from left to right, from more important to less important. We can check this MSDN to see the explanation.  This means when we compare 2 Guids generated by UuidCreateSequential(), the later one could be less than the former one.  It looks weird to me.  The simple conclusion to me is that we cannot use Guid.CompareTo() to decide the sequence generated by UuidCreateSequential().

4. Generate sequential GUIDs for SQL Server

After understanding these different sorting, it is easy to generate sequential IDs for SQL Server in .NET code. The post How to generate sequential GUIDs for SQL Server in .NET has the C# code.  Basically we just need to switch 0..3, 4..5 and 6..7 bytes and regenerate the Guid.

A difference between Table and List in SSRS

In our project, we needed to create SQL Server Reporting Service reports for users to view tabular data and graphs. We decided using Report Builder 3.0 to build the report templates.

On 2008 when I worked on another project, we had a requirement to support reports. At that time, I wrote an article to compare Crystal Reports, Report Designer in Visual Studio, and Report Designer in SQL Server. I used Report Designer in Visual Studio in that project.

The Report Builder tool is effective and fun, but sometimes it's annoying, so occasionally I would rather directly open the rdl file to modify the XML syntax. 

Problem

One of our reports has the following requirement.  First, it should be grouped under Product.  Every product will have a table to show its related information. The table has two different formats depending on the product type. What I did was to drag a table, add a group, and inside the group, add 2 rows. Then I added one tables to each row, and the row visibility would be decided by the product type.

It worked fine and showed the result users want. However, I noticed that the product name always showed in a separate page if the details table for that product crossed over one page. Report Builder has some properties such as KeepTogether which will push the content to a new page if they cannot be showed in one page. This time we did want the details table to be in the same page as the product name even it could not fit into one page. We didn't want a silly empty page only having the product name showed.

Solution

First and obviously, we thought it should be a easy task and the properties of the report should do the trick. We tried different properties such as KeepTogether, PageBreak, Group Properties, and changed them for both the parent table and children table, but neither of them worked.

I searched around and couldn't find a good solution or anything which claims this is a known issue. I did get this and this stating sub report has this issue but it seems already fixed at some point.

My final solution is totally out of my expectation.  I just changed the parent tablix from a table to a list and the problem was resolved. Lists give you more freedom to control the layout, and probably have less restriction. In the list, I can easily put 2 tables anywhere and control the visibility of them.

Second Thoughts

I was curious about what is different between a table and a list, and why they have different behaviors. So I created 2 new reports and put a list and a table to them respectively, and I used Beyond Compare to compare them. There was really not much difference. As we know, both table and list are called Tablix in the .rdl XML file. The only significant difference is inside the TablixCell, list puts a Rectangle, whereas table puts a Textbox. So I guess that the behavioral difference of List and Table is caused by the design feature of Rectangle and Textbox. KeepTogether probably is not working on a textbox. In the other hand, Rectangle is a loose layout control, and provides more freedom to end users.