Next, next, next, next, next… you know the drill.
Most things are fine, but there is one page which you really shouldn't ignore. And it's not even obvious. But if you're just clicking Next, next, next, then you might easily miss it.
When you reach this page:
please make sure you don't just hit Next without looking at the Collation tab. It looks like this:
Now, I don't particularly care what you decide to put here. But you shouldn't just set it blindly. If you are installing a new server and you need it to be consistent with what you've used before, go and look at the collation setting on your old instance. This is ridiculously important.
You see, people are lazy. And when I say people, I mean developers. And when I say developers, I mean bad developers. (There was a thing recently that said that "Women's Soccer" should now be called simply "Soccer". I agree with this. There shouldn't have to be a differentiation between a game played by a particular type of people, except perhaps "American Football", which is obviously called "football" because they use their feet so much. Oh right, about developers. I hope as time passes, developers become better. But for now, when I say "Developers", I mean bad developers.)
So when a developer creates a temporary table, they do something like:
CREATE TABLE #resultset (col1 varchar(20) NOT NULL, col2 int NOT NULL);
And then they populate it with some data, and then they use it in another query. Like this:
SELECT t.* FROM dbo.SomeTable t JOIN #resultset r ON t.code = r.col1;
This is code that has worked for years. But if you have ignored the collation setting and the instance collation is different to the database collation, you will get an error. The system won't know whether two strings are supposed to be the same or not. Is 'abc' the same as 'àbç', if one tells you to ignore accents and the other says not to? The system can't decide. It's even more complex than that, because two strings might be identical, but it won't know how to look them up if the alphabet orders letters differently. It can't decide and gives an error. It goes in the too-hard basket.
Of course, a nice responsible developer will have created the temporary table like this, and then the problem never occurs:
CREATE TABLE #resultset (col1 varchar(20) <strong>COLLATE DATABASE_DEFAULT </strong>NOT NULL, col2 int NOT NULL);
But let's face it – this is rare. Most people write database code without thinking about the collation settings, and that's a problem. Most of us are bad developers.
The error you get is the one described here: https://connect.microsoft.com/SQLServer/feedback/details/324910/collation-error-behaviour-option. This is a Connect item which I raised in January 2008 (7.5 years ago), which has 47 upvotes, and which was closed in 2011 as "Won't Fix". It was looking likely for a while (at least when using tempdb), but then they must've realised it wasn't particularly important. Ultimately, it could give unexpected results if you're not confident about which language you're using, and it's potentially better to give an explicit error than to let your query work but give the wrong results.
SELECT * FROM dbo.Sports WHERE Name = 'football';
Msg 468, Level 16, State 9, Line 1 Cannot resolve the collation conflict... 😉