THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

Check the settings when installing SQL Server

Hi! - Great that you've found this page, but it's no longer here! You can find the content over at:

Published Tuesday, July 14, 2015 12:26 PM by Rob Farley



mark said:

That connect proposal is really horrible. What bad language design.

The real problem is that temp tables have tempdb collation. That is never useful. They should have the collation of the database that creates the table. This behavior is so bad and broken that it borders on a bug.

It certainly is *not* best practice to specify COLLATE DATABASE_DEFAULT on all temp tables. It clutters the code. This should be done if necessary. The better solution is to ensure that all databases have the same sane collation. If that is not possible you might need COLLATE DATABASE_DEFAULT.

July 14, 2015 8:03 AM

Rob Farley said:

I'm sorry you don't like the Connect item, Mark. The point of the Connect item is to have an option that means that a collation error can resolve with a warning.

There is no issue specifying a collation when creating a temporary table, and DATABASE_DEFAULT is better than nothing.

July 14, 2015 8:59 AM

mark said:

I did not realize it was your item. I should have elaborated: Settings that influence the valid T-SQL that you can write or its behavior are evil because it's a trap waiting to hit. You can no longer use code on the web or publish code to the web because you might accidentally depend on some setting. That's why the ANSI NULL setting is deprecated although it clearly makes the language better. Bad language design.

It's action from a distance. It breaks stuff without you being able to audit for it.

This is like the VB language options which I'm sure the team regrets.

Also, this is a little like ON ERROR RESUME NEXT.

July 14, 2015 9:26 AM

Rob Farley said:

You already can't execute code and just assume it's going to work. A different collation is a common cause of that. The Connect item is trying to increase the amount of code that will simply work, although of course indexing strategies need to consider the collation settings carefully.

July 14, 2015 10:03 AM

mark said:

It also will increase the amount of code that runs but has wrong results and the amount of code that depends and requires a certain database setting. In fact what if you want to run different pieces of code with different settings? Often a database hosts multiple apps or clients.

One nasty aspect I did not mention before. The ticket breaks the fact that (a=b) is identical to (b=a).

Again, the real fix is to not make temp tables depend on internals of tempdb. We have a clean fix, there is no need to add bad language design.

July 14, 2015 10:28 AM

Rob Farley said:

All code that created columns without an explicit collation is dependent on database settings already. My suggestion in the Connect item was to have a way of avoiding the error, and a few different options were provided. And the real fix is to have people always specify the collation explicitly, so that even the database collation isn't relevant, let alone the tempdb collation.

July 14, 2015 8:28 PM

Marcel said:

"The real problem is that temp tables have tempdb collation. That is never useful. They should have the collation of the database that creates the table."

Agree with Mark here. The default behaviour should work for the 99% of cases. If I ship an enterprise produce internationally, the first problem encountered is restoring our base db onto a server with a different collation. When the customer supplies the db server, this is always a problem (in our case this is 100% of the time). The default collation for them is directly dependent on their machine locale. They have other systems on their db servers. Our application operation is directly dependent on the collation we have built our app upon and regression tested against. Having to specify database_default everywhere is poor design.

July 26, 2015 8:38 PM

Rob Farley said:

Yes, I agree with that aspect too. But that connect item is not just about tempdb, but anywhere that collation errors occur. System tables use the default instance collation, and we should take care to avoid errors when dealing with them too.

July 26, 2015 9:32 PM

Trevor said:

I'm sure you're already aware, but it's worth noting that this behaviour changes when you use a DB that is set for partial containment. Once set it defaults to creating temporary tables in the database default collation.

So while the primary reason for containment is security, and it's a sledgehammer approach to contain a DB for this reason the option is there.

August 13, 2015 12:01 AM

Rob Farley said:

Yes, Trevor, and I really wish they had've developed the "contained database" concept more.

August 13, 2015 12:23 AM
Anonymous comments are disabled

This Blog



No tags have been created or used yet.


News? Haven't you read my blog?

My Company

Can't find something?

Contact Me

Twitter: @rob_farley
Skype: rob_farley

MVP (SQL Server)


Adelaide SQL UG

Privacy Statement