August 3, 2016 at 8:04 pm
Hi all ,
I have issue with collation . my PRODUCTION server collation is SQL_Latin1_General_CP1_CI_AS and database collation is Latin1_General_CI_AS
And also when I check in 1 database that we use the most , it has different collation for different tables .
For example :
Table A , B, and C -> Latin1_General_CS_AS ( I suppose CS refers to Case Sensitive )
Table D -> SQL_Latin1_General_CP1_CS_AS
Table E, F, and G -> Latin1_General_CI_AS ( I suppose CS refers to Case In Sensitive )
I am suspicious with it . Could it be an issue ? The application behaves abnormally these day
Any input are highly appreciated
August 4, 2016 at 12:39 am
WhiteLotus (8/3/2016)
Hi all ,I have issue with collation . my PRODUCTION server collation is SQL_Latin1_General_CP1_CI_AS and database collation is Latin1_General_CI_AS
And also when I check in 1 database that we use the most , it has different collation for different tables .
For example :
Table A , B, and C -> Latin1_General_CS_AS ( I suppose CS refers to Case Sensitive )
Table D -> SQL_Latin1_General_CP1_CS_AS
Table E, F, and G -> Latin1_General_CI_AS ( I suppose CS refers to Case In Sensitive )
I am suspicious with it . Could it be an issue ? The application behaves abnormally these day
Any input are highly appreciated
Well, it may be a problem, and it may be not.
It's only a problem if the code is not aware of different collations and does include features to work with them correctly.
In this case you should see a lot of error messages on logs.
Actually, you may have different collations on different columns within the same table.
If the code which works with such a table is written correctly then there should not be any trouble.
But only if it's written correctly.
BTW, did you check default collation for tempdb?
_____________
Code for TallyGenerator
August 4, 2016 at 5:32 am
Thx for the reply ! Really appreciate it .
Temp db has the same collation like server collation which is SQL_Latin××××××
I dont see any error log in sql tho...
I do check DBCC checkb with purity and the result is 0 error
Does it mean the collation is not a problem ?
August 4, 2016 at 5:33 am
FYI .. temp db has different collation with database collation
August 5, 2016 at 3:47 am
different collation between instance, tempdb and other databases is not a problem but it does require that the code is aware of it and deals with it.
Not doing the code taking in consideration the collation can cause it to fail.
regarding your server the SQL_xxx is an older setting - current setting normally will not have the SQL_ and will use instead Latin1_General_xxx. This will depend on your own company and DBA's / Developers should agree on a standard.
As for using CS/CI and/or AS/AI it all depends on the application and data. no hard rule there.
so if your application is giving undesired results, with no error being issued, it may be that some code was done correctly, and other was done assuming that all data would be of a particular type and has made no validation on case and/or accent types.
You will need to investigate and see where the "error" is done and fix accordingly.
August 5, 2016 at 4:01 am
WhiteLotus (8/3/2016)
The application behaves abnormally these day
What do you mean by this? Has the application's behaviour changed, and if so in what way? Did anything change in the code, database structure or amount of data to cause that? What does "abnormal" mean - slow, unexpected results, error messages?
Having your database collation differ from the tempdb collation isn't necessarily a problem - if you don't create temp tables and join or union database tables with them, you might live happily for ever without it being an issue. If, however, you do use temp tables in that way, you need to code round it, as others have mentioned, with COLLATE clauses.
John
August 5, 2016 at 4:03 am
You'll have to care about the code you're writing all the time.
If you're using collation-property different columns in comparison you may have performance slow downs because of it.
From the other side, changing the collation of a database is not an easy task.
But it's possible to equate all collations into the one of the sql server instance; doing it carefully.
Igor Micev,My blog: www.igormicev.com
August 5, 2016 at 4:05 am
Sergiy (8/4/2016)
BTW, did you check default collation for tempdb?
Does this question make sense?
Igor Micev,My blog: www.igormicev.com
August 5, 2016 at 3:23 pm
Igor Micev (8/5/2016)
Sergiy (8/4/2016)
BTW, did you check default collation for tempdb?
Does this question make sense?
When you create a temp table all char columns get the default tempdb collation.
Unless you explicitly specify otherwise.
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply