SQL Database Sort Order

Put together in HTML as part of Richards Techy Pages

There are 2 sort orders the SQL database can have that Dynamics will work with. These are Binary (50) and Dictionary order case insensitive (52) which is the default. The sort order of SQL 7 can be checked by looking at the master database with the query: "Select value from sysconfigures where comment = 'default sortorder ID' ". The query will return a number which will need to be 50 or 52 for Dynamics. The sort order of the database needs to be the same sort order of the data being restored. Historically we have used binary as training on version 3.15 Dynamics C/S + indicated Binary sort order to be 20% faster than the default (52). If using SQL 8 (2000) the overriding sortorder constraints are the same. Refer the SQL 8 installation guide for further details.

Changing the Sort Order

If the sort order is incorrect I will get an error about wrong Unicode Locale when I try to restore a database. To change the sort order I use the following steps to rebuild the master database with the wanted sort order.

  1. Stop the SQL server using the batch file on Betty's desktop
  2. Delete the contents of the database folder d:\Mssql7\Data
  3. Run the utility C:\Mssql7\Binn\Rebuildm.exe
  4. In settings I choose �Binary Order� for the �Sort Order� and select O.k. Conversely if I am changing from Binary to Default sort order I would choose �dictionary Order case Insensitive� which is the top option above Binary.
  5. With the Browse button I select the folder �C:\Mssql7\CD Original Data�, see note below
  6. When I select O.k. the process of rebuilding a blank SQL database with the Binary sort order will take place
Note: Rebuilm requires the original CD data from the SQL install CD, so to speed things up I have copied the data folder from the CD and put it into �C:\Mssql7\CD Original Data�. If you do not have these files you can get them here


Back


Revised 4th October 2002

Hosted by www.Geocities.ws

1