To Collate or not to Collate

-

Collation plays an important role within the database, as it sets out how the SQL Server has to manage string data within storage & queries, yet I don’t script out the collation when scripting out my databases.  So why don’t I script out the collation if it so important? Its because if the collation is specified but not used properly within the database it can cause some of most common problems, like “Cannot resolve collation conflict for EQUAL TO operation” or “Cannot resolve collation conflict for UNION operation”. These errors are caused when string data within different collations are being compared or combined into the same result set; this is nothing new so why talk about collation now? Well it’s to do with the release of Visual Studio 2005 Team Edition for Database Professionals (VSTDB) something I think needed to be taken in consideration if this is being used to manage the database side of the project.

 

A cause for theses types of problem is when a table is created on a database with the collation scripted out and deployed on another database which has a different collation to the create script.  With the VSTDB this will not occur as much because a project will be assigned a collation and the database will be created with this collation or if the database does not get recreated and has a different collation then the tables will be created with the project collation (This is the desired behavior when I saw this last and was confirmed when I spoke to the VSTDB Team in November). So I can see that VSTDB will help with reducing these types of errors.

 

Another cause of the error is when creating temp tables without setting the collation to the same as the database collation and the server collation is different.  There are 2 ways to resolve this issue. The first is to rebuild your system tables to match the collation of the database that has been created or another way is by using the collation clause within the create temp table script(s) within the T-SQL code.  These errors could be become more frequent when using the VSTDB; because as mentioned before the database or tables that are create under the VSTDB are assigned the project collation, which will have to be of a type collation as there is no server default option and this could be different to that of the server that is being deployed to. 

 

By taking the time to put in the extra collation clause in the temp table script(s) it reduce the risk of database code failing with “cannot resolve collation” errors on any SQL server configuration.  Also it reduce the temptation of people changing the VSTDB project collation to match there server collation if they can’t change their SQL server default collation and causing different behavior of SQL statements.

 

I can understand the reason for making the database project ask for a collation because it means that when you unit test the code on any server it will always behave in the same way. Also it means as stated before, the database can run on any server with less risk of collation errors.

Comments

Post a Comment

Popular posts from this blog

Azure SQL Server IaaS SSIS: A required privilege is not held by the client

-
Image
As mention in a previous post, we are using Azure SQL Server IaaS to help our transition to the cloud. The main reason for this is how some of our SSIS projects, which we inherited, were created. Some were using SSIS to manage the transactions, which uses MSDTC not supported in Azure. Use extra .Net libraries to read JSON and office files; external programs to do file operations like decompression. Migration timelines and priorities we didn't have time to refactor these projects. While ago we started to deploy our SSIS projects to our Azure SQL Server IaaS virtual machine, a Windows 2019 server with SQL Server 2019, to get the following error: SSIS Deployment Error 6522 "A required privilege is not held by the client". We able to resolve our problem by using the steps we found in the following blog posts: System.ComponentModel.Win32Exception: A required privilege is not held by the client while Deploying SSIS Project | Microsoft Docs . Fixing SQL 2012 SSIS Deployment Err
Read more

SSIS File handle leak in For Each Loop

-
I am currently working on a project which needs to load over a 1000 xml files. The files are stored across 10 subfolders. I am using a foreach loop with a file enumerator, which is configured at the top of the folder structure and traverses the subfolders. This loops through the files, load the data and then moves the file to another folder. The package executes fine for a few 100 files but then hangs; this happens after a different number of processed files each time the package is run. While trying to resolve the problem we ran performance counters and noticed that the number open handles increased significantly just about the time Dtexec looked like it had hanged and DTexec also then started taking a lot of the cpu processing time. Update: I put this on the Microsoft forums and got a intresting answer back See Here ; There seems to be a memory leak with foreach loop which should be fixed in sp1.
Read more

Cannot update a database that has been registered as a data-tier application

-
Within SQL Server 2008 R2 a new feature was introduced, which was the data-tier application database. This feature allow developers / database administrators to package a database schema so that it could be deployed to any other database server and versioned. More about this feature can be found here: http://msdn.microsoft.com/en-us/library/ee240739(v=sql.105).aspx or http://dacguy.wordpress.com/ This feature has been included, as I have just found out, to previous versions: SQL Server 2005 via Services Pack 4 and 2008 via Services Pack 2. However SQL Server Management Studio, for SQL Server 2008, hasn't been updated with the tools for the new feature, you have to use the SQL Server 2008 R2 version. The reason for including the support to the previous version might have something to do with the release of the new SQL Server Data Tools (SSDT), the replacement to visual studio database professional, which uses DAC Packs and the framework to deploy a database. I have been using the
Read more