Errata¶

Sort order differences between Dataflex .DAT files and MS-SQL tables¶

Description

Under certain conditions, the sort order of rows may be different for dataflex DAT files and the coverted MS SQL tables. The problem is limited to DF_ASCII columns and is caused by the different collation methods used by Dataflex and MS-SQL.

Example

Let’s use the Inventory table from the DataFlex Order Entry Example. In the classic edition, we would convert the table from DAT to MS-SQL using the CONVERT_DAT_FILE command with the inverse key option. However, inverse keys are no longer supported in the Unicode driver, so we have to do things a bit differently. First, we convert the table DAT to MS-SQL using the f2s_convert_dat_full command:

open invt
f2s_convert_dat_full invt.File_Number
close_invt

This will create a table in the MS-SQL back end called invt and copy all the data into the new table. In addition, the invt.int file will be created. There are three indexes for this table (besides index 0 which is just recnum):

We can duplicate the ignore case behavior in the VENDOR_PART_ID and DESCRIPTION Ascii fields by creating a computed column and using that column in the index. This will not work for ITEM_ID, because ITEM_ID is the primary key for the table and primary keys cannot be made on a computed column. For ITEM_ID, we would just have to insure in the code that the data is always capitolized. To create the computed columns, the following sql code can be used:

alter table dbo.invt
add DESCRIPTION_UC as (upper(DESCRIPTION))
GO

alter table dbo.invt
add VENDOR_PART_ID_UC as (upper(VENDOR_PART_ID))
GO

These will become columns 7 and 8 respectively (this will be important later). Now, we need to use these columns in their respective indexes. To do this, we need to recreate the indexes (drop and add):

DROP INDEX [invt_INDEX02] ON [dbo].[invt]
GO

DROP INDEX [invt_INDEX03] ON [dbo].[invt]
GO

SET ANSI_PADDING ON
GO

CREATE UNIQUE NONCLUSTERED INDEX [invt_INDEX02] ON [dbo].[invt]
(
       [VENDOR_ID] ASC,
         [VENDOR_PART_ID_UC] ASC,
         [ITEM_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [invt_INDEX03] ON [dbo].[invt]
(
          [DESCRIPTION_UC] ASC,
          [ITEM_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

In the invt.int file (created when we converted the DataFlex file to MS-SQL, see above) we need to make a change to let the driver know that we are now using the computed column in the index:

INDEX_NUMBER 2
INDEX_NUMBER_SEGMENTS 3
INDEX_SEGMENT_FIELD 3
INDEX_SEGMENT_FIELD 8
INDEX_SEGMENT_CASE IGNORED
INDEX_SEGMENT_FIELD 1
INDEX_SEGMENT_CASE IGNORED

INDEX_NUMBER 3
INDEX_NUMBER_SEGMENTS 2
INDEX_SEGMENT_FIELD 7
INDEX_SEGMENT_CASE IGNORED
INDEX_SEGMENT_FIELD 1
INDEX_SEGMENT_CASE IGNORED

At this point, it is expected that the ordering of search results will be the same. However, when sorting by index.1 (Item_ID) using the DAT file, the first found Item_ID is ACE-2, the second ACE1. When sorting by index.1 in the MS-SQL table, the sorting is reversed. The first found Item_ID is ACE1, the second ACE-2.

Inventory.dat ordered by index.1 (Item_ID)

MS SQL Inventory table ordered by index.1 (Item_ID)

This is because the collation method used by DataFlex and MS-SQL is different. DataFlex is using a binary search as where MS-SQL is using whatever the server is set at. Typically MS-SQL uses SQL_Latin1_General_CP1_CI_AI (please see Conversion Guide, Updating your Database, MS-SQL Server for a more detailed explanation). To get the MS-SQL Inventory table to sort the same way as the DAT file, we need to modify the Ascii fields in the indexes to use SQL_Latin1_General_BIN collation. To do this, we first have to:

1. drop all the indexes except for recnum

2. modify/recreate the the columns to use the new collation type

3. recreate the indexes.

First we need to drop the indexes:

ALTER TABLE [dbo].[invt] DROP CONSTRAINT [invt_INDEX01] WITH ( ONLINE = OFF )
GO

DROP INDEX [invt_INDEX02] ON [dbo].[invt]
GO

DROP INDEX [invt_INDEX03] ON [dbo].[invt]
GO

Then we need to modify ITEM_ID and drop and recreate our computed fields with the new collation method (computed columns cannot be altered):

alter table [dbo].[invt] alter column ITEM_ID nvarchar(10) COLLATE Latin1_General_BIN NOT NULL
GO

alter table [dbo].[invt] drop column DESCRIPTION_UC
GO

alter table [dbo].[invt] drop column VENDOR_PART_ID_UC
GO

alter table [dbo].[invt] add DESCRIPTION_UC as (upper(DESCRIPTION)) COLLATE Latin1_General_BIN
GO

alter table [dbo].[invt] add VENDOR_PART_ID_UC as (upper(VENDOR_PART_ID)) COLLATE Latin1_General_BIN
GO

Now that all the fields have the right collation method, we just need to recreate the indexes:

SET ANSI_PADDING ON
GO

ALTER TABLE [dbo].[invt] ADD  CONSTRAINT [invt_INDEX01] PRIMARY KEY CLUSTERED
(
  [ITEM_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [invt_INDEX02] ON [dbo].[invt]
(
       [VENDOR_ID] ASC,
         [VENDOR_PART_ID_UC] ASC,
         [ITEM_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [invt_INDEX03] ON [dbo].[invt]
(
          [DESCRIPTION_UC] ASC,
          [ITEM_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

Now, the MS-SQL and DAT files will sort the data the same.