May 31, 2011 at 3:13 am
Hi friends,
I have some queries on reagding collations. Can anyone guide me in the right direction??
1.We are in process in building new SQL Servers to host multiple application Databases. Do you recommend that a SQL Server of a collation only hosts application DBs of the same collation?
2.Would like to know what is the impact if I communicate between two sql server having different system/application database collation settings.
3.Is there any harm, if I have system database collation as different and application database collation different?
4.What is the difference between
Latin1_General_CI_AS, SQLSortOrder=0
SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52
5. What kind of impact in sql quries and distributed quries will have with database with different collation settings.
Any thoughts or links would be greatly appreciated.
Thanks in Advance.
May 31, 2011 at 5:50 am
well I wouldn't use SQL_ collations as they're only there for backward compatibility with SQL Server 7.
You may get some issues if your user databases have a different collation to your tempdb database.
You may have issues with cross database or cross server queries where collations are different.
That said if you know you can code your sql to avoid this - off the top of my head I can't remember the syntax ( many years since i've had to use it ) but I'm sure someone else will post the sql statement, if not I'll look it up and add another post.
The whole thing about collation choice is making sure you choose what fits your requirements.
That said I suspect most SSRS servers are not set to use the SSRS collation as default, but SSRS does have its own tempdb which matches collation.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 31, 2011 at 8:28 am
colin.Leversuch-Roberts (5/31/2011)
well I wouldn't use SQL_ collations as they're only there for backward compatibility with SQL Server 7.You may get some issues if your user databases have a different collation to your tempdb database.
You may have issues with cross database or cross server queries where collations are different.
That said if you know you can code your sql to avoid this - off the top of my head I can't remember the syntax ( many years since i've had to use it ) but I'm sure someone else will post the sql statement, if not I'll look it up and add another post.
The whole thing about collation choice is making sure you choose what fits your requirements.
That said I suspect most SSRS servers are not set to use the SSRS collation as default, but SSRS does have its own tempdb which matches collation.
Huh? You can use different collations in each and every database and column if you want. I have never heard that the reason they exist is for backwards compatibility. I think you may be confusing collation with something else.
Check out BOL for a complete description of collations.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 1, 2011 at 1:48 am
Thanks guys.
June 1, 2011 at 4:03 am
One doubt,
Does the SQLSortOrder is tied up with the collation name or the code page????
I have created a database with collation "SQL_Latin1_General_CP1_CI_AS" and when i say
sp_helpdb 'db1'
Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52
Similary i have created one more database "db2" with collation as "Latin1_General_CI_AS" and when i say
sp_helpdb 'db2'
Collation=Latin1_General_CI_AS, SQLSortOrder=0
Also, i have executed the following queries
select * from fn_helpcollations()
where name = 'SQL_Latin1_General_CP1_CI_AS'
/*
namedescription
SQL_Latin1_General_CP1_CI_ASLatin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data
*/
select * from fn_helpcollations()
where name = 'Latin1_General_CI_AS'
/*
Latin1_General_CI_ASLatin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive
*/
Now my question is Does the SQLSortOrder is tied up with the collation name or the code page????
Am slightly confused.
Can anyone help me out with some easy explaination???
Thanks in Advance.
June 1, 2011 at 4:31 am
I don't know what differences there are between the sort orders for those two collations - quite possibly none. However, I do know that if you attempt to join a character column from a database having one collation with a character column from a database having the other, you will get a conflict error.
Sean Lange (5/31/2011)
Huh? You can use different collations in each and every database and column if you want. I have never heard that the reason they exist is for backwards compatibility. I think you may be confusing collation with something else.
Sean, I think Colin referring to collations with names starting with "SQL_", not collations in general.
John
June 1, 2011 at 4:48 am
John, thanks for the quick response and that makes sense. I have checked with a join query and fail's and i had to specify the COLLATE DEFAULT_DATABASE and it works fine.
Any other thoughts in detail explaination in simple words???
June 1, 2011 at 5:20 am
Here is where am getting confused?
Difference between windows collation and sql collation?
what is relation between collation, code page and sort order id ?
June 1, 2011 at 7:09 am
John Mitchell-245523 (6/1/2011)
Sean Lange (5/31/2011)
Huh? You can use different collations in each and every database and column if you want. I have never heard that the reason they exist is for backwards compatibility. I think you may be confusing collation with something else.Sean, I think Colin referring to collations with names starting with "SQL_", not collations in general.
John
Right you are. Once again posting in the morning before fully reading (or finishing my first cup of coffee). :hehe:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply