Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Dev Null
Starting Member
39 Posts |
 Posted - 2007-09-04 : 17:51:21
|
| I am given a database that uses a case-insensitive default collation; I would like t swap it over to using a binary collation.Changing the default collation in the database has no effect on existing tables. Changing the collation of each individual char column would be an enormous pain, especially given that I'd have to remove and re-create all of the indexes and foreign keys as well.Since we have restricted access to this database through a collection of stored procedures and queries, the simpler option would seem to be to simply leave the database alone, and enforce a binary collation on all data comparisons, a la:WHERE tab1.name = tab2.name COLLATE Latin1_General_BINSo, here's my question: would doing the latter mean that no such comparison would make use of the indexes on the old table, since the collation of the comparison and the index do not match? Or does it manage some cleverness behind he scenes to make the index useful?Thanks for any advice, - rob. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-04 : 17:55:33
|
| Why do you want to change the collation? Have you ever worked with collations that weren't the default? If you have, then you know what a major pain it is! I recommend always using the default. It makes life so much simpler for DBAs.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
Dev Null
Starting Member
39 Posts |
Posted - 2007-09-04 : 18:41:51
|
| Well the data coming into the system from an external source that we can't control is case-sensitive, and the default collation is not. Now you or I might have the sense not to name two different devices in the same system device1 and Device1, but unfortunately its bad for business to make the potential customer take an intelligence test before letting them buy your program...Of course I'd prefer to just build the database with a binary default in the first place, but unfortunately there are people out there with data collected already using whatever collation they had set up. If I have to convert it, I'll build a whole new database and just copy the data across, but if I could get the same effect and no major loss of efficiency by just enforcing binary comparisons, well that would be a truck-load less work. I'm guessing it'll screw up my index usage, but it seemed worth an ask. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-05 : 03:47:44
|
| "If I have to convert it, I'll build a whole new database and just copy the data across"That's what I would do. I don;t know how big your application is, but changing all the comparisons sounds like a lot of work to me, and lots more QA testing to make sure you didn't miss any - now, or in the future.And I reckon that your assessment is right and it will prevent indexes being used.Why do you think its a big deal to change the collation (by copy-to-new-database method)? maybe we can come up with some steps that make that pretty straightforward (not sure what your level of skill is in that area)Kristen |
|
|
|
Dev Null
Starting Member
39 Posts |
Posted - 2007-09-05 : 13:34:33
|
| Well its a bit of a long story, but I welcome any advice.Essentially there are a bunch of fiddly changes being made to this revision of the database which I've already written the code for. Adding/removing columns, combining tables, changing indexes; that sort of thing. I did the changes in place, rather than the recreate-and-copy method, mostly because the changes started out fairly minor. And then they added a few more requirements. And then a few more. And then a few more still... and THEN we found out that we wanted to change the collation. *sigh*I'm pretty sure the correct answer is to do the recreate-and-copy thing, but I hate throwing code away and I didn't want to have to re-write the conversion... which is a pretty lame excuse now that I spell it out. If there had been an easy way around it, sure, but this will be better from a maintenance point-of-view.Thanks for making me spell that out; it makes the answer rather obvious... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-05 : 14:35:54
|
| "Thanks for making me spell that out; it makes the answer rather obvious..."Sorry about that!, but if you would like to make yourself a life-sized cardboard-cutout of your perceived "average SQLteam contributor", stand it in the corner of your office, and pass these sort of conundrums by it in future then ... feel free!!Kristen |
|
|
|
|
|
|
|
|
Forcing collation in comparison stop index use?