SQL_Latin1_General_CP1_CI_AS versus Latin1_General_CI_AS
in [Server Configuration]
|
Prev: Class not registered error in Business Intelligence Dev Studio
Next: Warning: Failed to get MSFTESQL indexer interface for full-text catalog
From: ben brugman on 13 Jun 2008 05:18 Dear reader Our database is rolled out to several locations. Normally because of the default and some specified typing, all text fields (varchar etc.) are created as SQL_Latin1_General_CP1_CI_AS. But at one location the instance was different and the default was changed to Latin1_General_CI_AS, this was not noted. Because up te a problem I did not differentiate between the two collation orders. (Bummer). So now we have a database where MOST text field are the Latin1_General_CI_AS, but not all because SOME fields are specific made the SQL variant. Now the problem is that there is one database which is different from the others. So all test should be run on a version with these types as wel. One problem we noticed is that a view could not be created, because the fields where of a different collation order. What is the best strategie to get the one database inline with all the other databases ? What are the differences (and consequences) of SQL_Latin1 and the Latin2 versions. To my knowledge, accents are not used in any of the fields which we use for indexing, comparing or foreign key field. Some free text fields might contain accents. The database is not very large about 300 Mb. So please advise. How to reset the database? What differences (problems) to expect with the 'wrong' database ? Thanks for your time and attention, Ben Brugman
From: John Bell on 13 Jun 2008 08:05 "ben brugman" <ben(a)niethier.nl> wrote in message news:%23QEJnZTzIHA.4040(a)TK2MSFTNGP04.phx.gbl... > Dear reader > > Our database is rolled out to several locations. Normally because of the > default and some specified typing, all text fields (varchar etc.) are > created as SQL_Latin1_General_CP1_CI_AS. > > But at one location the instance was different and the default was changed > to Latin1_General_CI_AS, this was not noted. Because up te a problem I did > not differentiate between the two collation orders. (Bummer). > > So now we have a database where MOST text field are the > Latin1_General_CI_AS, but not all because SOME fields are specific made > the SQL variant. > > Now the problem is that there is one database which is different from the > others. So all test should be run on a version with these types as wel. > One problem we noticed is that a view could not be created, because the > fields where of a different collation order. > > What is the best strategie to get the one database inline with all the > other databases ? > What are the differences (and consequences) of SQL_Latin1 and the Latin2 > versions. > > To my knowledge, accents are not used in any of the fields which we use > for indexing, comparing or foreign key field. Some free text fields might > contain accents. The database is not very large about 300 Mb. > > So please advise. > How to reset the database? > What differences (problems) to expect with the 'wrong' database ? > > Thanks for your time and attention, > Ben Brugman > > Hi Ben I assume that the database has the same collation as the instance in that circumstance, therefore your problems have been when the collation has been specified, rather than when it was left to default? In which case removing the collation for the DDL would make everything consistent. If your database is different to the instance collation, then you can have issues when joining to temporary tables or to tables in other databases, to get around this you can specify the collation of columns when creating temporary tables or force a collation using the COLLATE clause when comparing (joining) two text columns. Using the option database_default for the collation will make this independent of what the database collation actually is. See more about the COLLATE clause in Books Online. It's always easier if collation was consistent through out everywhere you have your application, but if you are installing one a shared instance at a customer it is not always possible. Therefore in general it is best not to force or assume a collation except where it is necessary e.g. you may want a binary collation to force order and case sensitivity. Throughout your application you should see little difference if the collation is SQL_Latin1_General_CP1_CI_AS or Latin1_General_CI_AS, but both have instances where they are faster or slower than the other. John
From: ben brugman on 13 Jun 2008 10:43 "John Bell" <jbellnewsposts(a)hotmail.com> wrote in message news:859F12DE-7CDD-47AD-842B-4A25B9B9FB29(a)microsoft.com... > >> > Hi Ben > > I assume that the database has the same collation as the instance in that > circumstance, therefore your problems have been when the collation has > been specified, rather than when it was left to default? In which case > removing the collation for the DDL would make everything consistent. If > your database is different to the instance collation, then you can have > issues when joining to temporary tables or to tables in other databases, > to get around this you can specify the collation of columns when creating > temporary tables or force a collation using the COLLATE clause when > comparing (joining) two text columns. Using the option database_default > for the collation will make this independent of what the database > collation actually is. See more about the COLLATE clause in Books Online. > Before the building of the database the Collation for the database was set to Latin1_General_CI_AS, the instruction was IF the default is not CI and not AS set to Latin1. With all other databases the default was always used which was SQL_Latin1_General_CP1_CI_AS. So our 'instruction' was not completely clear. For most fields user defined data types where used they all have the default type. So if not changed this is SQL_ but in the 'wrong' database it is Latin1_.... The other fields are explicitely specified. (SQL_ etc.) Our aim was to have the implemented database the same everywhere. In our standard database, views and applications we do not use the COLLATE clause and do not want to introduce this clause, because then we have to test against to different implementations of the database. I am aware of the Tempdb issues. But except for maintenance we do not use Tempdb in the normal running. So we tried to have consistency within our database definitions over different instances. So we have (and do) prefere(d) the database to be consistent even if the instance is not. > It's always easier if collation was consistent through out everywhere you > have your application, but if you are installing one a shared instance at > a customer it is not always possible. Therefore in general it is best not > to force or assume a collation except where it is necessary e.g. you may > want a binary collation to force order and case sensitivity. This is a new insight to me. (Following the instance and not the 'database' in other instances). For production systems I would think that databases should be defined as 'strong' as possible, for me this would include the collation order. > > Throughout your application you should see little difference if the > collation is SQL_Latin1_General_CP1_CI_AS or Latin1_General_CI_AS, but > both have instances where they are faster or slower than the other. > Still looking how to get the 'rouge' database in line. This would save on testing time, because then we do not have to test for 'different' database. Remark the collation order are both Latin1, so the dataset does not differ, also we don't or hardly use any 'strange' symbols or accents. So we would in our application not note diffecences in the differens collation sets. But in the above situation SQL-server notices a difference and refuses to implement the view as is. Thanks for your time and attention. Ben > John
From: John Bell on 13 Jun 2008 14:30 "ben brugman" <ben(a)niethier.nl> wrote in message news:O3WsPPWzIHA.5820(a)TK2MSFTNGP04.phx.gbl... > > "John Bell" <jbellnewsposts(a)hotmail.com> wrote in message > news:859F12DE-7CDD-47AD-842B-4A25B9B9FB29(a)microsoft.com... >> > >>> >> Hi Ben >> >> I assume that the database has the same collation as the instance in that >> circumstance, therefore your problems have been when the collation has >> been specified, rather than when it was left to default? In which case >> removing the collation for the DDL would make everything consistent. If >> your database is different to the instance collation, then you can have >> issues when joining to temporary tables or to tables in other databases, >> to get around this you can specify the collation of columns when creating >> temporary tables or force a collation using the COLLATE clause when >> comparing (joining) two text columns. Using the option database_default >> for the collation will make this independent of what the database >> collation actually is. See more about the COLLATE clause in Books Online. >> > Before the building of the database the Collation for the database was set > to Latin1_General_CI_AS, > the instruction was IF the default is not CI and not AS set to Latin1. > With all other databases the default was always used which was > SQL_Latin1_General_CP1_CI_AS. So our 'instruction' was not completely > clear. > > For most fields user defined data types where used they all have the > default type. So if not changed this is SQL_ but in the 'wrong' database > it is Latin1_.... > The other fields are explicitely specified. (SQL_ etc.) > Our aim was to have the implemented database the same everywhere. > > In our standard database, views and applications we do not use the COLLATE > clause and do not want to introduce this clause, because then we have to > test against to different implementations of the database. > > I am aware of the Tempdb issues. But except for maintenance we do not use > Tempdb in the normal running. So we tried to have consistency within our > database definitions over different instances. So we have (and do) > prefere(d) the database to be consistent even if the instance is not. > >> It's always easier if collation was consistent through out everywhere you >> have your application, but if you are installing one a shared instance at >> a customer it is not always possible. Therefore in general it is best not >> to force or assume a collation except where it is necessary e.g. you may >> want a binary collation to force order and case sensitivity. > > This is a new insight to me. (Following the instance and not the > 'database' in other instances). > For production systems I would think that databases should be defined as > 'strong' as possible, for me this would include the collation order. > >> >> Throughout your application you should see little difference if the >> collation is SQL_Latin1_General_CP1_CI_AS or Latin1_General_CI_AS, but >> both have instances where they are faster or slower than the other. >> > > Still looking how to get the 'rouge' database in line. This would save on > testing time, because then we do not have to test for 'different' > database. > > Remark the collation order are both Latin1, so the dataset does not > differ, also we don't or hardly use any 'strange' symbols or accents. So > we would in our application not note diffecences in the differens > collation sets. But in the above situation SQL-server notices a difference > and refuses to implement the view as is. > > > > Thanks for your time and attention. > Ben > > >> John > Hi Ben If a customer said we wish to buy your application but only if we can stick it on our existing instance which is already Latin1_General_CI_AS what would you do? SQL Server is only giving you issues because you have used a specific collation in some off the DDL, without these you would not have had any issues and in fact may not have even noticed the difference. If you feel that you have to test your application for each collation, then you may want to make sure that it works for all 4 combinations of the two collation and hopefully your testing is automated! John
From: John Bell on 13 Jun 2008 14:32
"ben brugman" <ben(a)niethier.nl> wrote in message news:O3WsPPWzIHA.5820(a)TK2MSFTNGP04.phx.gbl... > > "John Bell" <jbellnewsposts(a)hotmail.com> wrote in message > news:859F12DE-7CDD-47AD-842B-4A25B9B9FB29(a)microsoft.com... >> > >>> >> Hi Ben >> >> I assume that the database has the same collation as the instance in that >> circumstance, therefore your problems have been when the collation has >> been specified, rather than when it was left to default? In which case >> removing the collation for the DDL would make everything consistent. If >> your database is different to the instance collation, then you can have >> issues when joining to temporary tables or to tables in other databases, >> to get around this you can specify the collation of columns when creating >> temporary tables or force a collation using the COLLATE clause when >> comparing (joining) two text columns. Using the option database_default >> for the collation will make this independent of what the database >> collation actually is. See more about the COLLATE clause in Books Online. >> > Before the building of the database the Collation for the database was set > to Latin1_General_CI_AS, > the instruction was IF the default is not CI and not AS set to Latin1. > With all other databases the default was always used which was > SQL_Latin1_General_CP1_CI_AS. So our 'instruction' was not completely > clear. > > For most fields user defined data types where used they all have the > default type. So if not changed this is SQL_ but in the 'wrong' database > it is Latin1_.... > The other fields are explicitely specified. (SQL_ etc.) > Our aim was to have the implemented database the same everywhere. > > In our standard database, views and applications we do not use the COLLATE > clause and do not want to introduce this clause, because then we have to > test against to different implementations of the database. > > I am aware of the Tempdb issues. But except for maintenance we do not use > Tempdb in the normal running. So we tried to have consistency within our > database definitions over different instances. So we have (and do) > prefere(d) the database to be consistent even if the instance is not. > >> It's always easier if collation was consistent through out everywhere you >> have your application, but if you are installing one a shared instance at >> a customer it is not always possible. Therefore in general it is best not >> to force or assume a collation except where it is necessary e.g. you may >> want a binary collation to force order and case sensitivity. > > This is a new insight to me. (Following the instance and not the > 'database' in other instances). > For production systems I would think that databases should be defined as > 'strong' as possible, for me this would include the collation order. > >> >> Throughout your application you should see little difference if the >> collation is SQL_Latin1_General_CP1_CI_AS or Latin1_General_CI_AS, but >> both have instances where they are faster or slower than the other. >> > > Still looking how to get the 'rouge' database in line. This would save on > testing time, because then we do not have to test for 'different' > database. > > Remark the collation order are both Latin1, so the dataset does not > differ, also we don't or hardly use any 'strange' symbols or accents. So > we would in our application not note diffecences in the differens > collation sets. But in the above situation SQL-server notices a difference > and refuses to implement the view as is. > > > > Thanks for your time and attention. > Ben > > >> John > Hi Ben If a customer said we wish to buy your application but only if we can stick it on our existing instance which is already Latin1_General_CI_AS what would you do? SQL Server is only giving you issues because you have used a specific collation in some off the DDL, without these you would not have had any issues and in fact may not have even noticed the difference. If you feel that you have to test your application for each collation, then you may want to make sure that it works for all 4 combinations of the two collation and hopefully your testing is automated! John |