March 23, 2011 at 12:00 am
Comments posted to this topic are about the item Changing collations
March 23, 2011 at 1:12 am
Thanks for the question, I learned something. (However I hope I don't have to deal with multiple collations very often.)
March 23, 2011 at 2:01 am
In these days, I am changing collate on third party's database.
March 23, 2011 at 3:20 am
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 23, 2011 at 3:25 am
Good question!
I missed it - I thought about potential blockers for changing collation of existing columns; I didn't stop to consider that ALTER DATABASE COLLATION doesn't affect those, but only the default setting. With that in mind, the correct answer is obvious.
March 23, 2011 at 6:21 am
This is a good question and definetly worth more than a point.:-)
M&M
March 23, 2011 at 8:55 am
This was removed by the editor as SPAM
March 23, 2011 at 9:20 am
Learned something new. Thanks for the question.
March 23, 2011 at 10:33 am
Thanks for the question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 23, 2011 at 5:25 pm
This one was a pretty hard one.
I never heard of "Collate" and I was converting "Collate" with "collation" wich mean "small lunch" in french... but I don't think that SQLserver needs to lunch any time :laugh: so I had to search for what it means before, then find the microsoft site... then tried to understand it... then ... well, you know...I lost patience and answered the only one that looked logic to me, wich was wright, but not enough.
Finally, now that I have read the answer, I just realised that I loosed my patience only one paragraph before the answer!! Well... yup! I deserved loosing that point !
Thank you for the nice question. I learned two things today: the failing of a collate command and never loose patience 🙂
March 24, 2011 at 3:00 am
Thanks for the kind comments everyone.
This question was written after I'd spent a day or so changing the collation on a database and found that before I could even change the DB collation I had to drop all the computed columns, check constraints and un-schemabind any schemabound functions and views.
As to then changing individual columns, I had to drop/disable indexes and foreign keys, change the collation on the columns and then rebuild everything. All quite labour intensive.
The reason I did it was because I'd restored a SQL collated database with some SQL and some Windows collated columns to a Windows collated server. It was then complaining about stored procedures which created temp tables and joined them to existing tables on character columns. I assume that the collation in the tempdb must have been the same as the server collation, which would explain the problem. The mix of collations (I've no idea how it got like that by the way) had always bugged me in the past so I decided to sort it out once and for all.
If anyone is interested, Erland Sommarskog has raised a suggestion on Connect to make it easier to change the collation on a database.
March 26, 2011 at 11:03 am
Good question. I found it easy because I've been through the pain of changing collations a couple of times (right to the point of rebuilding master to fix the server default collation).
Actually SQL Server is a a bit of a pain in its collation handling. Erland's Connect suggestion will go some way towards alleviating that pain (if MS takes it up - so everyone please vote for it) but only a small part of the way.
There is no good reason for a schemabound view to constrain the database default collation is it makes no use of it, its select statement uses no CTE that defines a column without an explicit collation being specified. Similarly for a check constraint, or a computed column, if no reference is made to the database default collation (so for example no string literals with default collation) there's no reason to object to a default collation change. So life could be made a little easier if MS would change the code to check whether the various objects are impacted by the default collation, and if none are allow a change of the default collation (perhaps with a warning, if MS thinks it's needed).
Then there's changing the collation of a column involved in one or more indexes: can we have an ALTER...COLLATION WITH REINDEX option? Having to drop the index and recreate it isn't too much of a pain, but it begins to get interesting when the column concerned is part of a key constraint, because if the column involved is part of a key there's the foreign key issue. Couldn't we have "on collation change cascade" as an option on foreign key constraint declaration? (OK, bad terminology, it's not cascading to lots of affected rows it's changing metadata -and that may need rebuilding some indexes.) Or is that going too far? Even without the foreign key issue there's the basic key issue - changing the collation may remove uniqueness - I need to be able to have the system tell me that my key will no longer be a key if I make the suggested collation change, not fine out the hard way (I recall that there's an issue with some digraphs and their single character equivalents for example is <thorn> equal to "th").
An interesting bunch of issues to be suggested by a QoTD.
Tom
March 9, 2012 at 2:15 pm
I learnt something new and thanks for the great question.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply