Server collation "poll" - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Server collation "poll"

Author

Topic

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-11-30 : 09:28:09

Hmm, so, in an effort to clean-up our shop, I have tracked down differences between machines, and found that about 60% of our developers have the LATIN1_GENERAL_CI_AS code page on their "local" SQL server instances, while our servers are all (excepting one black sheep) on SQL_LATIN1_GENERAL_CP1_CI_AS.

That got me wondering, what do you use on your server's? Specifically, those of use with english data

As I said, we are standardizing on SQL_LATIN1_GENERAL_CP1_CI_AS on SQL200 on windows 2000.

CiaO

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-30 : 10:42:31

SQL_Latin1_General_CP1_CI_AS is the one I have on english OS.
( how did they get the other 1 ?, default or by choice )

rockmoose

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-12-01 : 04:56:20

It seems to be a product of the OS you have installed. Because developer's just go ahead and "take the defaults" when installing on their own machines. Interestingly enough, I notced about 1 1/2 years ago that SQL will get different code pages when following a "efault" install (i.e. not specifying the code page under custom installation), and the problem seems to come around SQL 2000 / Windows 2000, when it chooses the windows default code page (Latin) and not the SQL default code page (SQL_Latin).

That is my un-scientific theorey, based on my experiences. A proper explanation would be nice, if someone can confirm.correct my intrepretation.

I do say that ALL SQL install's should be done via custom, to ENSURE that your code pages, etc. etc. are consistent - you would have thought people would have learnt by now NOT to use "default" installs...

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Kristen
Test

22859 Posts

Posted - 2004-12-01 : 05:08:55

SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS

I think this is because our servers were upgraded from SQL7 (or maybe it was the upgrade from 6.5), and some are Default Installations

We put collation of SQL_Latin1_General_CP1_CI_AS on all "text" columns in CREATE TABLE

Kristen

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-12-01 : 06:51:22

Kristen,

Seen that (the SQL 7 upgrade effect). I take it you don't have the need to join between your different DB's...

that's why I am trying to move everything here to standard, and enforce it.

CiaO

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-01 : 08:07:35

We are also striving to have all servers and all databases on the same collation.
Saves trouble.
Of course we have some exceptions ( older installations and some 3rd party apps ).

rockmoose

Kristen
Test

22859 Posts

Posted - 2004-12-01 : 12:04:31

"I take it you don't have the need to join between your different DB's"

Problem doesn't arise; all our tables are created using explicit COLLATE statements, so they are all SQL_Latin1_General_CP1_CI_AS. Anything created with DEFAULT would be at risk!

Note that even Temp tables use explicit COLLATE - for when they wind up in a TEMPDB defaulting to a different COLLATE sequence.

JOINING to Client data is a nightmare though - so we always put COLLATE statements on any JOIN to databases external and out of our control.

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-01 : 13:04:25

quote:
Originally posted by Kristen
JOINING to Client data is a nightmare though - so we always put COLLATE statements on any JOIN to databases external and out of our control.

yuck

Do you use dynamic for that?

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-12-01 : 14:02:11

"dynamic"

You're having a Giraffe! - as in Rhyming Slang for "Having a laugh"


SELECT *
FROM MyTable M
  JOIN TheirTable T
    ON T.TheirPK COLLATE SQL_Latin1_General_CP1_CI_AS = M.MyPK COLLATE SQL_Latin1_General_CP1_CI_AS

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-01 : 14:19:07

I don't ever change this setting in the installation. I always keep it at the default. And we always get the same results on every machine. No variances.

Tara

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-01 : 14:45:14

[code]SELECT *
FROM MyTable M
JOIN TheirTable T
ON T.TheirPK = M.MyPK COLLATE SQL_Latin1_General_CP1_CI_AS[/code]
Can't You save some typing ?
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=42033&SearchTerms=collation[/url]

BOL states that BINARY sort gives best performance, any comments to that ?

rockmoose

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-12-01 : 16:31:55

It's barely enought to worry about. In addition, it's a real pain. There are ZERO vendors you will be able to interface with. We are running CP437_BIN as our standard here because some genius decided it was better back in the day. Now we live with this nightmare everyday. In addition, we "officially" support three different collations. It's BRILLLLLLLIANT!!!!

The difference is the map that SQL has to look at. There was a bigger difference in performance on large systems back when we were on 6.5 and earlier. It's now not really worth worrying about compared to the problems it causes now.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-12-01 : 18:57:52

"Can't You save some typing"

I have been doing Moose, ever since you pointed out I didn't need to type COLLATE both sides of the "=" !

Kristen

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-12-02 : 04:03:56

Tara,

quote:
I don't ever change this setting in the installation. I always keep it at the default. And we always get the same results on every machine. No variances.

I take it your machines are built consistently, and you typically install SQL on a clean install? I defintely had this problem on 3 different server's that had been built in "different" ways ... one clean, one upgraded, and the other that was just an old mess...

BTW, Tara, doing it by default, are you get SQL_Latin or LATIN ?

Jeez Derricke, you do get all the fun at your site, don't you. Imagine if you and steve (elwoo's) could have your site combined... :-)

CiaO 4 NoW

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-02 : 12:32:38

Yes are machines are built consistently. We've got a document that is followed for each server. Ours is SQL_Latin1_General_CP1_CI_AS.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources