Temporary Tables: Different Type of Sorting Order in SQL Server Environment


Probably most of the partners have already noticed this and changed the C/AL code accordingly in order to let their customized application works properly.

In ALL versions of NAV up to now (NAV 2009 R2) the SORTING of Temporary Tables / Temporary record variables has always been determined using a C/AL sorting “collation” type. Even in SQL Server environment, where it is possible to select the proper collation (File > Database > Alter > Collation) for the database, Temporary Tables / Temporary record variables use always C/AL sorting order. This is very important to know since it may lead to a wrong business processing of data when using e.g. record looping (REPEAT .. UNTIL cycles).

This behavior has been maintained like this for 2 main reasons:
1) Temporary Tables / Temporary record variables get created independent on the server, so they cannot read the collation and sorting from SQL Server and use this.
2) If MS NAV Development team change the way that Temporary Tables / Temporary record variables are sorted, then this might break compatibility with a lot of existing solutions that would suddenly sort differently.


Below you will find a simple demonstration of this assumption. For completeness, I have made the example both for Classic Client (Forms) and RoleTailored Client (Pages) but the behavior is the same.

1. Install a CRONUS database (W1 or whatever localized version)

2. Add these records in Table 6 “Customer Price Group”

Code Description
1 Code 1
2 Code 2
2.12 Code 2.12
200 Code 200
3 Code 3
300 Code 300
C3 Code C3

3. Add and enable one key to table 18 Customer with “Customer Price Group” field (if this is not already active). Save and compile (CTRL+S) table 18 Customer.

4. Add those codes to Customers.

No. Name Customer Price Group
01121212 Spotsmeyer’s Furnishings 1
01445544 Progressive Home Furnishings 2
01454545

New Concepts Furniture 2.12
01905893 Candoxy Canada Inc. 200
01905899 Elkhorn Airport 3
01905902 London Candoxy Storage Campus 300
10000 Cannon Group SpA C3

5. Create a New Codeunit, e.g. Codeunit 50000 “Test TempTable” with these global variables and code snippet:

Global variables

Name DataType Subtype Length
CustTemp Record Customer
Cust Record Customer

Set the Temporary property of the CustTemp variable to Yes.

C/AL code snippet:

// Copyright © Microsoft Corporation. All Rights Reserved.

// This code released under the terms of the

// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.) 

CustTemp.DELETEALL;

Cust.RESET;               // Populate CustTemp Table

Cust.SETCURRENTKEY(“Customer Price Group”);

Cust.SETFILTER(“Customer Price Group”,'<>%1′,”);

IF Cust.FINDSET THEN REPEAT

  CustTemp.INIT;

  CustTemp.TRANSFERFIELDS(Cust);

  CustTemp.Name := ‘TEMP ‘ + COPYSTR(CustTemp.Name,1,25);

  CustTemp.INSERT;

UNTIL Cust.NEXT = 0;

 

Cust.RESET;               // Run Page/Form – Normal table

Cust.SETCURRENTKEY(“Customer Price Group”);

Cust.SETFILTER(“Customer Price Group”,'<>%1′,”);

Cust.FIND(‘-‘);

IF ISSERVICETIER THEN

  PAGE.RUN(PAGE::”Customer List”,Cust)

ELSE

  FORM.RUN(FORM::”Customer List”,Cust);

 

CustTemp.RESET;           // Run Page/Form – Temporary table

CustTemp.SETCURRENTKEY(“Customer Price Group”);

CustTemp.FIND(‘-‘);

 

IF ISSERVICETIER THEN

  PAGE.RUN(PAGE::”Customer List”,CustTemp)

ELSE

  FORM.RUN(FORM::”Customer List”,CustTemp);

6. Save and compile (CTRL+S) the codeunit.

7. Run the Codeunit. (You can also add this Codeunit as an action in RTC. The results within Forms and Pages is the same).

8. Now compare the 2 Forms opened (they are one up in front the other) and their different sort order (show column “Customer Price Group” to clearly see the difference in sorting order):

Temporary (C/AL type dependent) Normal (SQL Server collation dependent)
1 1
2 2
3 2.12
200 200
300 3
2.12 300
C3 C3

Since from the next version there will not be any support for Native database (where this C/AL sorting coming from) there have been speculations about changing this behavior in order to have the SORTING for Temporary Tables / Temporary record variables equal to the SQL Sorting (in short, for SQL Server based environments to have the same sorting order for normal tables and temporary tables).

If you think this is a remarkable feature that could / should to be changed in a future version, please log your request into MSCONNECT:

https://connect.microsoft.com/dynamics

These postings are provided “AS IS” with no warranties and confer no rights. You assume all risk for your use.

Best Regards,

Duilio Tacconi (dtacconi)

Microsoft Dynamics Italy

Microsoft Customer Service and Support (CSS) EMEA

Comments (4)

  1. Jakub Vanak says:

    Hello, I don`t know whats wrong, but I can`t agree with this article… Some times ago I was trying this example and my conclusion is little bit different. Maybe I some made mistake somewhere. In this case I welcome any explanation…

    Pure SQL:

    01

    02

    03

    1

    10

    1A

    2

    20

    2A

    3

    30

    3A

    A1

    A2

    A3

    SQL with TEMP:

    1

    2

    3

    01

    02

    03

    10

    20

    30

    1A

    2A

    3A

    A1

    A2

    A3

    Native

    1

    2

    3

    01

    02

    03

    10

    20

    30

    A1

    A2

    A3

    1A

    2A

    3A

  2. dtacconi says:

    No mistake, Jakub. This is definitely a very wise and good comment. The basic scope of this article, to summarize, is to let partner be aware that Temporary Table sorting order is different than the others and follow independent sorting rules (that may slightly differ, as you correctly demonstrate, even from the ones derived from Native database). The purpose of this blog is to encourage partner that need to have a specular sorting order between temporary and normal tables, in log this into the MSCONNECT wish list. Since Native database will be dismissed in the next version, new partners may find this Temporary Tables sorting behavior a bit strange and not so easy to be managed.

  3. Hristo Valev says:

    Thank you so much for the info. It is so usefull for my work now – trying to order Serial No's in the Item tracking screens.

  4. David Machanick says:

    This explains the strange behavior in an upgraded C/Side database to NAV 2016.
    The temp table keeps the old version sort order, but the ranges follow the SQL sort order, so the summary totals no longer work.
    I don’t know if anyone monitors comments to old blogs, but it would be nice to add an option to allow ranges to use the temp table sort order.

Skip to main content