What's the sort order with MSSQL

  • What's the sort order?
  • The sort order will decide many things including:
    • The order the data is sorted
    • The order of the indexes
    • How text string comparisons are done. Is 'Artsi Fartsi' the same as 'artsi fartsi' or not?
  • Microsoft has created a stored procedure: sp_helpsort that will let you know what SQL Server uses.
  • The sort order is decided by the collation.
  • You select the default collation during the install.

A collation is made of:

  • Which characters are allowed
  • Language
  • Sort order
  • Accent marks
  • Character width
  • Code page

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

sp_helpsort

  1. SP_HELPSORT;
  2. go
Server default collation
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

You can create databases, tables and columns with different collations on the same server with the collate attribute.

  1. CREATE DATABASE albaniandb COLLATE Albanian_CI_AI_KS_WS
  2. go