When building complex stored procedures that span multiple tables, you will probably need to store some intermediate results in a local buffer and process them before returning the final output. SQL Server lets you do this through temporary table and table variables.

Usually table variables offer faster performance (less locking and logging are required), but they have morerestrictionsthan temporary tables. Common to both types is where they are located. When you create a table variable or temporary table, it gets created in the tempdb database, not in the current database. This can affect the collation sequences applied to character fields If the SQL Server was installed using one collation and your database uses a different collation, joins from tables in your database with temporary tables will fail if you join on character fields. The tempdb database will use the server default collation sequence. That collation may not be the same collation used by your database if your created your database on a different server that used a difference collation. The server collation is used for all of the system databases (including tempdb) and for any newly created user databases. Databases that are attached or restored from a backup keep the collation that they were created with.

Cannot resolve the collation conflict between “Latin1_General_BIN” and “Latin1_General_CI_AS” in the equal to operation.

That example is used to show the type of error you would get in your code. The actual code that would throw that error would be comparisons between character fields in a temporary table/table variable and with a permanent table in your database. If you are deploying databases to servers where the server’s default collation sequence could be different than the collation sequence used by your database, then you want to add “COLLATE database_default” to all of your character field definitions when you define a temporary table or table variable. Using “COLLATE Database_Default” will assign the collation sequence of the current database to the field. This will allow field comparisons between character fields in temporary tables/table variables and permanent tables to execute with triggering the “collation conflict” error.

This will work no matter what the collation sequence for either tempdb or your database. You don’t need to query the server to check what the sequence. The big limitation is that you have to explicitly define the columns in the temporary table. If you were using SELECT INTO syntax to create the temporary table implicitly by the columns in the SELECT statement, the temporary table will use the collation of tempdb. You would need to structure the SQL. For example, if you were using syntax like this: