collation issue

Hi
I have a problem with collation orders as follows.
The server collation order is set to Latin1_General_CI_AS.
How ever a few databases, a small number, have the collation order of
SQL_Latin1_General_CI_AS.
Now, when some operations are requested from the interograting App,
involving tempDB temp via sprocs and functions, I get a collation error, no
surprise there.
I have tried DTS, opps not the right tool, and scripts. I have nearly got
there with the scripts, but not quite.
So, to the question. What is the best way to amend the collation order of
the 'rogue' databases. And are there any *foolproof* solns that would do
this without the need for Extensive regression testing?
TIA
ChrisIn your select try something along the lines of
Select ...
From ..
Inner join tab1.Col1 = Tab1.Col2 Collate Database_Default
Can't remember the exact syntax but you can tell your query to use the
coillation of of the current database.
"Chris" <.@.> wrote in message
news:DNv7f.3599$iZ4.1238@.newsfe2-gui.ntli.net...
> Hi
> I have a problem with collation orders as follows.
> The server collation order is set to Latin1_General_CI_AS.
> How ever a few databases, a small number, have the collation order of
> SQL_Latin1_General_CI_AS.
> Now, when some operations are requested from the interograting App,
> involving tempDB temp via sprocs and functions, I get a collation error,
> no surprise there.
> I have tried DTS, opps not the right tool, and scripts. I have nearly got
> there with the scripts, but not quite.
> So, to the question. What is the best way to amend the collation order of
> the 'rogue' databases. And are there any *foolproof* solns that would do
> this without the need for Extensive regression testing?
> TIA
> Chris
>

Code Page Headaches

Hi

I am looking at upgrading some SQL 6.5 servers to SQL 2000. The 6.5 servers run a mixture of code page 850 & ISO 8890 and sort orders of 42 & 52.

We want to ensure all the 6.5 databases are migrated to 2000 BUT want to future proof the SQL 2000 databases by supporting unicode characters, as the SQL 2000 databases will be avialable world wide to all languages. That said, we want to ensure the existing table structures are preserved and run at 6.5 compatibility level. I am a little concerned as I am unsure if unicode support will alter table structures and require a re-write of the database schema or table structures.

Can someone help by giving me a straight forward explanation of the implications of my plans please?

Many thanks,I would just one thing at a time.

Do the upgrade to 2000 and make sure that is ok...

when your happy it is then make a copy of the db(s) and change the char and varchar types to be nchar and nvarchar respectively.
Test and when your happy take the live server offline and backup the server (optionally restore it to another machine or have tested that your backups work) and then do the upgrade.

One thing at a time (and slowly) or your asking for trouble.

good luck

Marc|||Unicode versions of chartacter fields are nchar and nvarchar which probably won't be available in 6.5 comapatability mode.
Using these fields will mean a change to the database structure.
If you want the database to be specific to a language then it's not too much trouble but if you want it multi-language you will have to include language versions of all the presentation data on separate tables.
To give true international support you will also have to consider the code pages for the sort order - i.e. you will need the alphabetical order for the language selected which will mean either coercing the columns in the query or having a database installed with the correct collation.

Clustered Index or NonClustered Index

Hello I want to learn disparity clustered index or nonclustered index and in queries which one run better.

example

select * from orders where orderID=5

to this query clustered or nonclustered

thanks

Big topic. Simple answer, yes, you probably want to cluster on the surrogate key, especially when using a smaller datatype surrogate like an int. The way SQL Server uses clustered indexes it is almost always the best choice.

Better answer. Read indexing topics in books online, and the start here one the web: http://www.sql-server-performance.com/optimizing_indexes.asp. Or just start there if you wanna. Books online has good information that is crucial to understanding indexes, however.

|||

Thank you very much