By definition, T-SQL is not case sensitive, but there are scenarios where it can exhibit partial case sensitivity.
Consider a scenario where you deploy a database for your application using a Case Insensitive (CI) collation. Initially, everything works fine, even if you occasionally deviate from naming guidelines or use different casings within your code, such as in stored procedures. However, problems arise when you deploy the same database to a server with a Case Sensitive (CS) collation.
Then on another day you deploy your database to a server and you start getting weird errors, like “Invalid column” or even “Must declare the scalar variable”. That’s the point when you find out the server’s default collation is Case Sensitive (CS) and it is affecting your code, that is working within a scope of your case insensitive database.
Let’s examine some examples using a SQL 2022 server with a CS collation.
Then I have my database using CI collation.
In this case, we attempt to create a procedure for our application. The situation is identical when using table variables or temporary tables.
At first glance, you may wonder why you haven’t encountered this error before or why it worked on other servers. The answer lies in checking the collation settings of the server and system databases. As the object is created in memory, it is likely that the object declaration passes through a temporary table or another system database controlled by the CS collation. In this case, the system recognizes “@table” and column “val” but does not recognize “VAL.”
While it is an assumption without delving into SQL Server internals, even variables likely pass through a system object with a CS collation.
Although the procedure is not compiled during creation, the parser must ensure that the code is valid. Therefore, errors are revealed during the creation stage rather than when the procedure is executed for the first time.
Finally, an evidence that if we are stay strictly within the scope of our database with CI collation we will be able to make it through with our “messy” code.
Upon further reflection, it is interesting to note that the parser does not physically create the object. To parse the code, it likely utilizes internal objects within the user database itself, which are not case sensitive. This explains why T-SQL code with inconsistent casing can still function within the scope of a case-insensitive database.
In conclusion, while T-SQL is generally not case sensitive, its behavior can be influenced by the collation settings of the server and system databases. When deploying a case-insensitive database to a case-sensitive server, unexpected errors related to object and variable names may arise. Therefore, it is important to consider collation settings and potential case sensitivity issues when working with T-SQL code.
How can you enforce casing during your development process? Finding an ideal solution can be challenging. In Visual Studio SSDT (SQL Server Data Tools), you have the option to set the database to a case-sensitive collation. However, it’s important to note that deploying your project with this collation will require adjustments during the publishing phase if you intend to maintain the original collation. It’s worth mentioning that this change applies to the user database and not the system, which means that altering the collation of reference databases like master or tempdb poses a challenge. In this situation, it is recommended to have a dedicated development or UAT (User Acceptance Testing) environment that utilizes a case-sensitive collation setup. This allows you to catch any errors related to case sensitivity during the development stage. Additionally, SQL Prompt from Redgate offers a feature to apply casing at the script level, but a method to apply it to an entire project or solution has not been found yet.
Did you know that you can change the server collation if it shows the one chosen during installation is not the ideal one?
You can also change the collation of your database. While changing the database collation may seem like a simpler process, it’s important to note that it does not automatically update existing objects and data. Therefore, changing the database collation can be a complex and potentially time-consuming endeavour.
Regards,
JD
Leave a Reply