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

"ben brugman" <ben@niethier.nl> wrote in message
news:%23QEJnZTzIHA.4040@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

"John Bell" <jbellnewsposts@hotmail.com> wrote in message
news:859F12DE-7CDD-47AD-842B-4A25B9B9FB29@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

"ben brugman" <ben@niethier.nl> wrote in message
news:O3WsPPWzIHA.5820@TK2MSFTNGP04.phx.gbl...
>
> "John Bell" <jbellnewsposts@hotmail.com> wrote in message
> news:859F12DE-7CDD-47AD-842B-4A25B9B9FB29@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

"ben brugman" <ben@niethier.nl> wrote in message
news:O3WsPPWzIHA.5820@TK2MSFTNGP04.phx.gbl...
>
> "John Bell" <jbellnewsposts@hotmail.com> wrote in message
> news:859F12DE-7CDD-47AD-842B-4A25B9B9FB29@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

>>
> 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?

Net been there yet, probably talk with the customer and (up until this
problem) if
the customer would stick to the above we would choose to have 'our' database
in
SQL_latin1.... (Only problem is then the tempdb ?).

>
> 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.

We might revert the specific DDL to the 'default' values, the number of
fields
specified in DDL is far less than the user defined types which are default.
(I have a look into this suggestion).

>
> 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!
>
It's not economic feaseble to do this so we might go with your suggestion
and
revert to completely compatibility with the instance and you are probably
correct that
if all is Latin1 that we do not notice any difference ever. I would not
expect any problems.
Most specified fields are not indexed either so the problem might be small
then.
(So this limites the fields which have to be corrected)

Thanks again for your support and attention,
Ben Brugman


> John
>