SQL server collation with widest data range for character support

• puneetw

  SSC Journeyman

  Points: 89

  More actions

  December 12, 2012 at 2:33 am

  #271824

  Hello All

  I want to know which is the collation in MS SQL server that supports the largest possible character set for foreign languages including english

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  December 12, 2012 at 9:25 pm

  #1568136

  There is no one-size-fits-all collation. If you are asking because some of your characters appear to have been eaten by SQL Server and replaced with question marks when you store them as CHAR or VARCHAR then switch to Unicode, i.e. use NCHAR or NVARCHAR. Unlike the ANSI types SQL Server will not escape any characters with a question mark when storing as Unicode even if the codepage does not define a character for the codepoint you are trying to store. That said, in Unicode collation is still important when it comes to comparisons (e.g. with range searches LIKE 'A%'), sorting and depending on the collation supplementary characters. If you need to stay with ANSI datatypes then you'll have to pick a collation and deal with the fallout when there is data that just doesn't fit the collation. Why are you asking?

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato