Post reply

collation which to use

  • July 6, 2011 at 5:41 pm

    #239199

    I read from :http://support.k2techno.com.au/?W471

    It says:

    More often than not you'll find the SQL_Latin1_General_CP1_CI_AS on US and older SQL systems and the Latin1_General_CI_AS on European + Aussie + Kiwi and newer SQL systems

    Is this true, but why I found SQL server 2008 default is SQL_Latin1_General_CP1_CI_AS.

    Our standard in using Latin1_General_CI_AS, I dont' understand why, just want to figure out which is newer, and which should be most adopted?

    THanks

  • July 6, 2011 at 5:49 pm

    #1349370

    This might be of help.

    http://msdn.microsoft.com/en-us/library/ms143508.aspx

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • July 6, 2011 at 5:51 pm

    #1349371

    http://social.msdn.microsoft.com/Forums/en/transactsql/thread/bfdc32d3-3d36-4d63-8d87-6ee972fd8130

    I was just reading this the other day.

    It will either help or muddy the waters 😉

  • July 6, 2011 at 5:59 pm

    #1349374

    Thanks,

    But I did search about those msdn articles about collation.

    Our existing standard setting up by someone already left is Latin1_General_CI_AS.

    But whenever now I install a new instance it is default to SQL_Latin1_General_CP1_CI_AS.

    I can certainly change it when install, but default usually is most people or company chose.

    if we want to intall a database server for third party software, if we ask them what collation they use, they often don't know. and I guess they usually use whatever the default is.

    So how can I avoid conflict of this?

    sam.dahl 's link seems very related with what I concerned.

    I will read it later for now I have to leave.

    Thanks

  • July 6, 2011 at 6:02 pm

    #1349376

    Standards do not always follow defaults from software installations. It also depends on the installation media you are installing as well as the locale being used for the media and installation.

    If your standard says to use a specific collation, then change it after install. It is quite likely your counterpart was doing it that way.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • July 7, 2011 at 12:58 am

    #1349451

    Generally if you skip the collation setting when running setup, it will default to the windows code page currently defined by the servers regional settings.

    I would check to see if your server thinks its in the country you are actually in.

  • July 7, 2011 at 9:31 am

    #1349780

    That's what I'm concerned.

    We are in USA, and in our windows system, in region and language, the format is set up like English(United states.), -which is correct.

    Does that mean if we chose default installation for SQL server colllation, it defaults to SQL_Latin1_General_CP1_CI_AS.But our standard is Latin1_General_CI_AS. I can change it to Latin1_General_CI_AS when installation.

    But I think most people will go with what is default.

    For 3 rd party software dbs, I think they may go with default.

    So how can I avoid it won't confilct with future use?

    thanks

  • July 7, 2011 at 9:37 am

    #1349786

    you can't, you will always have to remember to set the collation to you'r default, so if it is Latin1_General_CI_AS and your in the USA, then you will always have to set it at install if you want to apply it at server level.

    alternatively you can set collation at database and or column level so I guess it all depends what you need it to be, but best practise is to have your collation the same as tempdb which will take it from the server level.

  • July 7, 2011 at 11:55 am

    #1349906

    So in my case as described above, should I stick with what our standard is (not sure why it is this standard) or should I use what is the default which is SQL_Latin1_General_CP1_CI_AS.

    thanks

  • July 7, 2011 at 12:00 pm

    #1349909

    sqlfriends (7/7/2011)

    So in my case as described above, should I stick with what our standard is (not sure why it is this standard) or should I use what is the default which is SQL_Latin1_General_CP1_CI_AS.

    thanks

    You should continue to use your company standard. If you don't know why that is the standard, then you should ask around and find out if there are specific business reasons to use that collation.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • July 7, 2011 at 12:26 pm

    #1349928

    the below link gives an idea of the difference between the two collations

    ie one for UK-en and one for us-en

    http://msdn.microsoft.com/en-us/library/ms143508.aspx

    Naturally for most cases collation affects sort order and how strings are treated for non unicode data. Except in this case the data is still English It might impact the query results when using full text indexes but I need to try this myself.

    Jayanth Kurup[/url]

  • July 7, 2011 at 1:54 pm

    #1350008

    still confused.

    Our windows setting is using English(United States), and sql installation's default to English (united states) collation is SQL_Latin1_General_CP1_CI_AS.

    In our enviroment we have our database servers that hosts in house developement and also hosts third party softwares.

    For in house software database, we use Latin1_General_CP1_CI_AS, should I also using it for third party softwares like those for IT network.

    Before I installed that, I tried to ask the third party what collation they use, they don't know. I guess they just use what default collation is.

    I know once collation is setup when installation ,if want to change it , it is going to have alot of pain.

    So want to make this clear and correct at front.

  • July 8, 2011 at 12:45 am

    #1350159

    You said the standard is Latin1_General_CI_AS for servers and your saying your using Latin1_General_CP1_CI_AS within the software database, this must mean that someone created the databases as the CP1 collation if the server is set to Lat1_Gen_CI_AS.

    It is entirely up to yourself which collation you use, you just need to know the differences between the collations to help you choose which collation will be best for your particular requirements.

    Usually 3rd parties have two ways of setting up their databases in my experience. They either tell you to create a database and run a script in to create all the objects or they give you a blank database, be it a BAK or MDF/NDF/LDF etc to which you restore or attach depending on what they send you.

    If they provide a script and tell you to create a database and dont tell you it needs a specific collation, then we double check with them and if they dont know it gets created as the server standard as it musn't be collation important, and defaults to whatever your company uses. If they provide you with a BAK or MDF/NDF/LDF then the database is already set up with a collation and we usually dont change it from what they send us unless they tell us to.

    We run a mixture of collations but all databases on a server match the servers collation as we follow the best practice of all databases match the collation of TempDB to ensure that there are no collation differences when using #temps or sorting indexes in tempdb etc.

  • July 8, 2011 at 9:19 am

    #1350414

    Thanks. For third party software database, we never used bak, or mdf/ldf file, it always intall application from front end, and during the process, install database.

    I've tried to call them collation, they don't really have an answer, so I think you are right at this, it doesn't matter, it will install by following whatever the collation is on server default.

    Just feel confused Microsoft recommend to use windows collation, but when install they give default is SQL collation if we are in U.S and (because in windows system- region language we use English(United States)).

    This will easily cause a mixed collation enviroment.

  • July 8, 2011 at 9:29 am

    #1350422

    yeah the app creating the database is the same as running a script so yeah leave it as what ever it installs

    i can understand your confusion, just best to keep all the collations the same, but sometimes it cannot be helped.

    our estate is over 200 servers which is a right mix of collations so its remembering which are CS and CI as it trips me up half the time but thats life.

    the main thing is that if you have mixed collations on the same server that if you do anything in tempdb you implicitly set the collation

    so server is set to use Lat1_Gen_CI_AS and database is using Lat1_Gen_CP1_CI_AS, then I would always create any #temp tables which are string data types with the COLLATE key word and specify Lat1_Gen_CP1_CI_AS to ensure that it forces it into the user database collation not the system collation to avoid any collation differences.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply