April 23, 2010 at 1:38 pm
Does anyone know if the default (out of the box) SQL server collation is different for SQL Server 2000 Enterprise and SQL Server 2005 Standard?
I have both in my environment and all of the SQL Server 2000 Enterprise Instances have a server collation of SQL_Latin1_General_CP1_CI_AS and all of the SQL Server 2005 Standard Instance have a server collation of Latin1_General_CI_AI
Some of the installs I did.. some I did not. as far as I know, the server collation was not changed from whatever the default is on install.
I've googled this and can't come up with an answer.. can anyone shed some light?
April 23, 2010 at 2:51 pm
2 words, Regional Settings!!
The default collation out of the box is dynamic 😉
The collation is defined by the regional settings on the server, you can change but if you accept the default it is based on the Windows regional settings.
Looking at what you have posted it looks as though your servers could have different Windows regional settings
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 26, 2010 at 6:16 am
Thank you
DO you know how I can get a list and explaination of the differnt collations: I can't find and explaination for
Latin1_General_CI_AI
Thanks.
April 26, 2010 at 6:48 am
Jpotucek (4/26/2010)
Do you know how I check my Windows Regional settings: or do I need to engage my Server guys?
You would check the same way you would in XP, Setting --> Control Panel --> Regional Settings
Check this out http://msdn.microsoft.com/en-us/library/ms144260.aspx
It should also be pointed out that when you setup an instances, for collation there are two options 'Windows Linked' ie Latin1_blah_blah_blah OR 'SQL' ie SQL_Latin1_blah_blah_blah. This is determined at setup time and not easy to change.
Thats not to say it cant be 😉
Adam Zacks-------------------------------------------Be Nice, Or Leave
April 26, 2010 at 7:20 am
Jpotucek (4/26/2010)
DO you know how I can get a list and explaination of the differnt collations: I can't find and explaination forLatin1_General_CI_AI
http://msdn.microsoft.com/en-us/library/ms143515(v=SQL.90).aspx
Actually took me a while to Google this, I had to resort to using MSDN search :w00t:
Edit: This is actually within the article (link) Adam noted.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 27, 2010 at 3:22 am
The default collation has changed from SQL Server 2000 to SQL Server 2005.
In SQL Server 2000, the default collation was a SQL Server specific collation. For SQL Server 2005 and above, the collation value uses the current Windows default. Windows does not know about the SQL Server specific collations, so the default collation will always be different between these two SQL Server versions.
The main feature of the SQL Server specific collations is they use a single byte character set, whereas (I think) all Windows collations are Unicode compliant and use a multiple byte character set.
If you are character data that only needs a SBCS, then you will use less disk space and typically get faster search performance if you use a SQL Server specific SBCS character set. If you know you need a MBCS, then it is probably better to use the same collation set as Windows.
If you pass a lot of character data between Windows and SQL Server and the collation sets are different, you will go through a collation set conversion process for all data as is enters or leaves SQL Server. The CPU cost for this is normally far lower than the cost of the SQL Query, but it is not zero.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 30, 2010 at 12:07 pm
Thank you for the explaination!!! makes sense now!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply