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.

Login failed for user xxx on IIS 7

Recently I migrated one of my ASP.NET MVC projects to another machine.  I updated the Entity Framework .edmx file, modified the connection string, added a specific application pool PM in IIS, and added my application to use this application pool.  After I ran it, I got the exception "Login failed for user IIS APPPOOL\PM".

After some digging, I found some useful links, such as this and this.  I list all necessary steps to run an ASP.NET MVC application in IIS 7 here in case somebody needs helps.

1. In IIS, I like to create a dedicated application pool MyPool, and specify the correct .NET framework version and Managed Pipeline Mode, mostly Integrated.

2. Check the created pool, and we can see by default the Identity using ApplicationPoolIdentity.  This is the recommended way to use in a development environment.  Some blogs suggest changing this to "Local System" or others.  If this is changed to "Local System", then step 4 is not needed.  However, this change may bring some security risks.  You can check the links I listed and see some good arguments.  I will keep ApplicationPoolIdentity for my application.

3. In IIS, under Default Web Site, create an application for the website, give a proper Alias and point to the physical path of the project.  In a development environment, I just directly point to the web site project. Then let the application use the dedicated pool.

4. In SQL Studio, add new login IIS APPPOOL\MyPool.  You may check this link to see some good discussions.  Remember to assign proper roles to this login.  We don't want to give too much privilege for this login.

5. Sometimes we may need to enable MSDTC on the machine to support distributed transaction if the application uses distributed transaction.   On Win7, go to Control Panel -> Administrative tools -> Component Services, expand Computers -> My Computer -> Distributed Transaction Coordinator -> Local DTC, and right click Properties.  Under Security tab, check Network DTC Access, Allow Remote Clients, Allow Remote Administration, Allow inbound, Allow outbound.  Choose Manual Authentication Required.  Click OK.  A reboot may be needed here.

'Sorting' is not allowed during an AddNew or EditItem transaction

Recently I resolved one WPF GUI bug related with the exception:
'Sorting' is not allowed during an AddNew or EditItem transaction.

This stackoverflow discussion has the correct answer to my issue, but it isn't marked as the answer for now.  So I would like to list it here and explain a bit, and hope this can help somebody out.

Background

We had a datagrid in a customized control, which is used to select files.  The control has a ViewModel class as its DataContext and can load different ViewModel classes.  The control itself will stay in memory, but the ViewModel will get disposed every time the view is closed.  

It works fine most of the time.  However, if users sort the datagrid by clicking a column header and then select a file by double click, and next time when the view is opened, the exception:
'Sorting' is not allowed during an AddNew or EditItem transaction.
will be thrown.

I checked the stack trace of the exception and saw the exception came from System.Windows.Data.ListCollectionView.SortDescriptionsChanged().  When using ILSpy to check ListCollectionView class, we can see this code in SortDescriptionsChanged()

            if (this.IsAddingNew || this.IsEditingItem)
            {
                throw new InvalidOperationException(Resx.GetString("CollectionView_MemberNotAllowedDuringAddOrEdit", new object[]
                {
                    "Sorting"
                }));
            } 

Solutions

ptsivakumar listed 2 solutions which both are good in my case.  I listed the solutions here in case that thread is not accessable.

1)  Add CommitNew() and CommitEdit()
This should be put in a proper place suitable for your specific case.  In my case, I put it in the view Close event handler:

            var dataView = (ListCollectionView)CollectionViewSource.GetDefaultView(DataGrid_Files.ItemsSource);
            if (dataView.IsEditingItem)
                dataView.CommitEdit();
            if (dataView.IsAddingNew)
                dataView.CommitNew();

2)  Add IsReadOnly="True" to the datagrid control, also remember to add CanUserAddRows="False"

Both solutions are working well.  The reason is when the datagrid is double clicked, it will enter the edit mode, but the sorting feature doesn't allow the datagrid in Adding New or Editing mode when a sorting event happens.  So the first solution will commit changes and bring the datagrid to a normal mode.  The second solution works because the settings will keep the datagrid always in a normal mode.  Double click is ignored.

Using Stuff and For XML Path to format the string in one column

Recently in one project I needed to create some SSRS reports to present business data. One normal requirement is to use comma to join all the text in one column to one big string. I had experience to use Stuff and For XML Path to join columns, but this time I had a chance to know what is really going on behind For XML Path.  You can check this great blog for details to explain the syntax.

In my project, the first requirement was to join 2 columns with a space, and use a comma to separate each record, but leave a space after the comma.  So my SQL was like this:

stuff((
                    select ', ' + [Name] + ' ' + [Call] From DetailsView
                        for XML path('')
                ), 1, 2, '')

Later, the requirement changed.  User wanted to show each record in a separate line.  So I needed to replace the comma with a line feed.  Char(13) and Char(10) together can make a new line.  In my case, Char(10) is good enough to go.  So I changed my SQL to this:

stuff((
                    select Char(10) + [Name] + ' ' + [Call] From DetailsView
                        for XML path('')
                ), 1, 1, '')
Later, user still wanted to add the comma before the line break, so I just slightly changed to this:

stuff((
                    select ',' + Char(10) + [Name] + ' ' + [Call] From DetailsView
                        for XML path('')
                ), 1, 2, '') 

So now in my report, I can have a formatted string in multiple line.

Memory leak caused by RegisterClassHandler of EventManager in WPF

Recently I spent some time to fix performance and memory usage issues in one big WPF project.  One critical bug reported was that every time users open an editing dialog, the memory will jump up around 5M, even users immediately close the dialog without any meaningful operations.  The memory will continue growing and never gets released.

Investigation

First I spent some time to understand the related code.  The editor view has an embedded child view EditorContent, and the child view too has 2 grandchildren views embedded.  And every view has a ViewModel class paired.  My strategy was to temporarily remove some child classes and find which View or ViewModel brings the problem.

This time I used Telerik's JustTrace to do the memory profiling.  After several rounds of trying, finally I located the problem in the child view EditorContent.  After carefully comparing the 2 snapshots before and after opening the editor dialog, I noticed there were some suspected events and controls along the GC root.  The control is called BarcodeTextBox and derived from the standard TextBox.

At the same time, I searched around the Internet and tried to find some clues. These 2 articles from Jeremy Alles and Devarchive.net have good hints.  When I went back to check the BarcodeTextBox class, I did find EventManager.RegisterClassHandler() in the code and it did exactly what was mentioned in the above 2 articles.

            EventManager.RegisterClassHandler (typeof(BarcodeTextBox), PreviewKeyDownEvent,
                new KeyEventHandler(HandlePreviewKeyDown));

So the fix is easy.  Either move the above code to a static constructor, or declare the event handler HandlePreviewKeyDown() as a static handler.  After this was done, the memory was released just after the dialog was closed.

Afterthought

From MSDN, I haven't found that it's mandatory to use RegisterClassHandler() in a static constructor or declare the handler as static.  But the MSDN example does use in that way.  And from the name itself, the handler should be at class level, not at instance level.

Checking EventManager from ILSpy, we can see RegisterClassHandler() calls GlobalEventManager to add the handler to a global event handler array.  By the name, we can guess the handler (therefore the owner object) will be held forever if no explicit unregister method is called.  Unfortunately, the static EventManager class even doesn't have any methods to unregister an event handler.  So I guess by design, these handlers registered in this way should be like static functions, and shouldn't exist at the instance level.

From what I investigated, event is really dangerous in C# world.  Every time we use "+=" to add an event handler or register one, we should always try to find a way to unregister.  For example, I found this one.  The scenario is very tricky and could happen very likely.