simulate case sensitive collation in ssms

Question

I know that it is not possible to set a collation for a session in sql server.

I have different servers with different collations, however, one of them is a case sensitive collation and that can be more tough on the details as you can see on the picture below.

Is there any setting or anything I can do to develop my scripts as if the collation was a case sensitive one, so that when I need to port scripts to a case sensitive server all would be already fine?

I have been looking for manipulating the collation without changing anything, trying to limit it to my session so to say, but I could not find anything, is there anything I could do?

set transaction isolation level read uncommitted
set nocount on
set deadlock_priority low


use ReportServer
--select * from sys.tables
select top 10 Name, Path, CreationDate 
  from dbo.catalog 
order by path  COLLATE Latin1_General_100_CI_AS_KS_WS desc

exec sp_help 'dbo.catalog'

DECLARE @SQL nvarchar(max)

SET @SQL='use ReportServer;select top 10 Name, 
         Path, CreationDate from dbo.Catalog order by path  '

SET @SQL=@SQL + 'COLLATE Latin1_General_BIN DESC'

PRINT @SQL
EXEC sp_executesql @SQL


SET @SQL='use ReportServer;select top 10 Name, Path, 
          CreationDate from dbo.Catalog order by path  '

SET @SQL=@SQL + 'COLLATE SQL_Latin1_General_CP1250_CS_AS DESC'

PRINT @SQL
EXEC sp_executesql @SQL


use master
go




SELECT name, COLLATIONPROPERTY(name, 'CodePage') AS Code_Page, description 
FROM sys.fn_helpcollations()
ORDER BY name;

Answer 1

No, there is no way within SQL Server to do this, outside of restoring the DB under a different name on the same instance, or on a test instance, and changing the database's default collation to a case-sensitive one.

Nor do I think that this would be desirable as the database's default collation controls several things, such as database-level object names, variable/parameter values, and string literals. Some of those might be ok to theoretically simulate "on demand", such as parameter/variable/string literal values. But, others, such as object names can be problematic. For example, if you have a case-sensitive or binary collation currently and want to simulate case-INsensitive behavior, that would allow for multiple objects to appear to have the same name (e.g. "Sales" and "sales") and there would be no way for the optimizer to handle that ambiguity.

Another issue, if such a thing were possible, would be how to handle the instance (i.e. server)-level collation. That handles things such as instance-level object names and variable names. And, since instance-level collation is handled separately from database-level collation, there would need to be some way to set them separately. Not only that, but there might also be other down-stream "odd" behaviors.

Now, that being said, it might be possible that Visual Studio / SSDT can do some of the name resolution checking as database projects do some level of validation (for example, it should validate table/column names, parameter names for procs and functions, etc). I would try this to see if it works for your needs.