Case sensitive variables in SQL Server

by

You need to change the server collation to case sensitive to get the behavior you want. Just changing the collation for the db is not enough.

The default collation of a SQL Server installation, SQL_Latin1_General_CP1_CI_AS is not case sensitive.

It sounds like you want to modify the collation of your server to one that is case-insensitive. Choose one with _CS. The _CI means “case insensitive“, and case-sensitive is _CS. Maybe you’ll want Latin1_General_CS_AS.

This is a great answer to a previous question on Changing SQL Server collation to case insensitive from case sensitive?.

From the SQL Server Books Online:

COLLATE (Transact-SQL)

The collation of an identifier depends on the level at which it is defined.

  • Identifiers of instance-level objects, such as logins and database names, are assigned the default collation of the instance.

  • Identifiers of objects within a database, such as tables, views, and column names, are assigned the default collation of the database.

    For example, two tables with names different only in case may be created in a database with case-sensitive collation, but may not be created in a database with case-insensitive collation. For more information, see Database Identifiers.

  • The identifiers for variables, GOTO labels, temporary stored procedures, and temporary tables are in the default collation of the server instance.

    Variables, GOTO labels, temporary stored procedures, and temporary tables can be created when the connection context is associated with one database, and then referenced when the context has been switched to another database.

You can check your server collation using:

SELECT SERVERPROPERTY('collation');

SQL_Latin1_General_CP1_CI_AS
(1 row(s) affected)

More Related Contents:

  1. Export from SQL Server to Excel
  2. How to get cumulative sum
  3. How to check if a column exists in a SQL Server table
  4. SQL Server query to find all permissions/access for all users in a database
  5. Infinite loop CTE with OPTION (maxrecursion 0)
  6. How Can I Log and Find the Most Expensive Queries?
  7. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  8. SQL Server add auto increment primary key to existing table
  9. What is the maximum characters for the NVARCHAR(MAX)? [duplicate]
  10. How do I view the full content of a text or varchar(MAX) column in SQL Server 2008 Management Studio?
  11. INSERT INTO vs SELECT INTO
  12. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  13. Query times out when executed from web, but super-fast when executed from SSMS
  14. TSQL: How to convert local time to UTC? (SQL Server 2008)
  15. How to: Create trigger for auto update modified date with SQL Server 2008
  16. How to update primary key
  17. error when inserting into table having instead of trigger from entity data framework
  18. Transpose rows and columns with no aggregate
  19. SQL Server Configuration Manager cannot be found
  20. SQL Server recursive query
  21. How do I move a table into a schema in T-SQL
  22. nvarchar(max) vs NText
  23. How to display two digits after decimal point in SQL Server
  24. How to find the boundaries of groups of contiguous sequential numbers?
  25. In SQL Server, how can I find everywhere a column is referenced?
  26. What are row, page and table locks? And when they are acquired?
  27. Error converting data types when importing from Excel to SQL Server 2008
  28. CHECKSUM and CHECKSUM_AGG: What’s the algorithm?
  29. SQL Server 2008 Spatial: find a point in polygon
  30. Implementing one-to-zero-or-one relation in SQL Server

Leave a Comment