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”

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.

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

Global variables

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):

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).

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