• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 114
  • Last Modified:

Since changing collation, MSSQL now requires case sensitive statements

We have a SQL 2014 instance that is dedicated to one application database.  While upgrading the application we learned there were several collation conflicts, causing the upgrade to fail.

SQL instance:  SQL_Latin1_General_CP1_CI_AS
Application database:  Latin1_General_CI_AS
  • 800 columns:  SQL_Latin1_General_CP1_CS_AS
  • 200 columns:  Latin1_General_CI_AS

We took the path of least resistance and corrected collation on the SQL instance, database, and 200 columns to match the collation of the 800 columns in our database - everything is now SQL_Latin1_General_CP1_CS_AS.  Performance is noticeably better and the application upgrade succeeded.  

However, now when running SQL statements against the database, objects must be referenced with case sensitivity.  I knew this would happen because we're using a case-sensitive collation, but I didn't realize the inconvenience.  

None of my reports are working unless I convert all tables/columns in my query to the proper case.  We have a lot of reports that are completely lower-case and they no longer run.

Given our current scenario, is there anything we can do so SQL accepts our lower-case statements?  Is it because we changed collation of the SQL instance or was it collation of the database that caused this behavior?

Thanks for your expertise!
0
bmsande
Asked:
bmsande
  • 12
  • 7
  • 6
  • +2
3 Solutions
 
QlemoC++ DeveloperCommented:
The collation of the database determines the treatment of its metadata. SQL Server instance collation determines treatment of database names, SQL logins aso (not specific to a database).

If you change the DB collation, nothing changes inside of the tables, but the metadata of course. New varchar columns are using the new collation by default.

So your choices are
  • reset the DB collation, and make sure varchar columns are getting the correct collation different from the db one.
  • rename all objects in your DB to fit to the case you are using, and keep that exact notation everywhere
  • change usage to proper case.
0
 
bmsandeAuthor Commented:
@Qlemo, thanks for your response.  Sounds like changing our reports to use proper case is the best option for us.

Is there a tool that I can ingest my SQL statements into and it will fix the case sensitivity?  Didn't know if I can automate this with SQL Mgmt Studio somehow?  We have tons of reports that are all lowercase and need to be corrected.  

Thanks!
0
 
Olaf DoschkeSoftware DeveloperCommented:
To state the obvious CS and CI abbreviate case sensitive vs case insensitive, so you could have seen what you did before.

The easiest option would be to revert to the CI collation, wouldn't it?

Bye, Olaf.
1
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
QlemoC++ DeveloperCommented:
Sorry, I don't know of such a tool. Text literals have to stay as-is, or be converted if used as a key. The app can't decide that,  just to show one issue.
1
 
bmsandeAuthor Commented:
@Olaf, if we revert the 200 columns to CI collation, then we would continue to face performance problems.  Or are you saying revert the SQL instance to CI collation?  

I thought tempdb and the application database should have the same collation for best performance - is this true?
0
 
ZberteocCommented:
Next to every setting or comparing expression you can add the collation to be used:

Select * from table where column=@variable COLLATE SQL_Latin1_General_CP1_CI_AS

select case when column = 'abcd' COLLATE SQL_Latin1_General_CP1_CI_AS then something else something_else end as val from table where column='xyz' COLLATE SQL_Latin1_General_CP1_CI_AS

obviously you will use the CI , case insensitive, collation in order to ignore the cases.
0
 
bmsandeAuthor Commented:
Would this config work?  Or is this against best practice?  I'm not a dba so I don't know but I was thinking, was if you have several database's on a SQL instance - must all db's have the same collation as the SQL instance?

SQL instance:  SQL_Latin1_General_CP1_CI_AS
Application database:  SQL_Latin1_General_CP1_CS_AS
ALL columns:  SQL_Latin1_General_CP1_CS_AS
0
 
ZberteocCommented:
The problem is at the server level. All system databases will have the server collation, including tempdb. SO if you have a database of different collation even operations with temporary tables could fail because when you create a temp table it will use by default the tempdb collation, which in turn was created with the server collation determined at the installation moment. You can NOT change a server collation but you could change a database collation.:

http://sqlmag.com/database-performance-tuning/seven-step-process-changing-database-collation
0
 
bmsandeAuthor Commented:
@Zberteoc, I followed the suggested article to align the database collation across instance>database>tables>objects.  Everything is now the same collation.  It took a lot of work to arrive at this point and everything is working great, but we'd like to be able to use lower-case syntax in our SQL statements.  We have hundreds of reports that would need to be corrected with case sensitivity.  

Sounds like we must stick to the collation we landed on because going through and changing collation within the database could take months.  Arriving at this point took several weeks alone.  It's an enormous db which is why we corrected the 200 columns to match the other 800 columns - path of least resistance.  Case-sensitive sql queries stink, though.
0
 
Olaf DoschkeSoftware DeveloperCommented:
You make me wonder, why this is an issue at all, because if this is some standard software and upgrades fails, it'd be an issue to solve by the vendor, if it's a self-written application then you make me wonder about how you mastered this so far.

I see you have better performance now, but a working application is more important than a fast and not working application, isn't it? You came from somewhere and changed collations, you can change all that back. As Qlemo said:
If you change the DB collation, nothing changes inside of the tables

I don't know how much you fiddled with everything, but it should be possible to revert maybe even while still keeping the new version of the software, then tackle that problem without the pressure of keeping things operative.

Such staging of a new upgrade should rather be done sandboxed in a test staging area.

Bye, Olaf.
0
 
bmsandeAuthor Commented:
We are in a test environment.  Performance and a working application are equally important.  This is standard software that the vendor would not support because collation conflicts were too numerous.  The customer does not have a DBA and everyone in IT has access to SQL.  Too many hands in the cookie jar and not enough database expertise.

Since aligning collation across the instance>database>objects, the app upgraded successfully and the performance gains are extremely noticeable.

I was hoping there was a way to preserve our legacy lower-case reports but that does not seem to be possible unless we run with mixed collation.  We don't want to sacrifice the perf gains that we've uncovered.
0
 
QlemoC++ DeveloperCommented:
I really can't tell about the effects on tempdb and having different collations. But as already has been said, if you only change the DB collation back, content will not change, but you get case-insensitive metadata usage back. You need to care about new varchar columns getting the proper (non-db) collation, but that is all I know of as a con.
0
 
funwithdotnetCommented:
If you want to leave it case-sensitive, perhaps Zberteoc's comment about adding to collation to the end of the affected queries is the best way to go.
0
 
QlemoC++ DeveloperCommented:
If I understood correctly, the SQLs to benefit from the collation boost are inside of an application the OP has no control about.
0
 
ZberteocCommented:
Please explain this, an example would help:
but we'd like to be able to use lower-case syntax in our SQL statements.
0
 
bmsandeAuthor Commented:
Sorry for the wall of text.  Any and all expertise is greatly appreciated!

@Zberteoc, we have hundreds of reports that were developed with lower-case syntax.  Lazy man's approach, yes.  We can not add collation to the end of the queries as some of these are internal to the application and can not be modified.  

Here's a small example of a common report we run:

SELECT dt.name, dv.datasize, ki.inivalue, pd.providerdata
FROM dversdata dv, dtree dt, providerdata pd, kini ki
WHERE dv.docid=dt.dataid
AND dv.providerid=pd.providerid

Open in new window


The statement above is no longer functional since resolving collation conflicts.  Corrected statement:

SELECT dt.Name, dv.DataSize, ki.IniValue, pd.ProviderData
FROM DVersData dv, DTree dt, ProviderData pd, KIni ki
WHERE dv.DocID=dt.DataID
AND dv.ProviderID=pd.ProviderID

Open in new window


RECAP:
We learned of collation conflicts during our application upgrade because it kept failing with collation errors.  We uncovered the following:

SQL 2008 instance:  SQL_Latin1_General_CP1_CI_AS
Application database:  Latin1_General_CI_AS
  • 800 columns:  SQL_Latin1_General_CP1_CS_AS
  • 200 columns:  Latin1_General_CI_AS

We corrected collation conflicts with the following high level process:
1. Change database collation
2. For columns to be altered, determine dependencies and relationships
3. Drop column dependencies/relationships (objects)
4. Alter column collation
5. Recreate dropped objects
6. Backup database
7. Uninstall/reinstall SQL with desired collation
8. Import database

We changed everything to SQL_Latin1_General_CP1_CS_AS because a majority of the database objects were already confg'd as such, and we have limited time in our project.  Current config:

SQL 2008 instance:  SQL_Latin1_General_CP1_CS_AS
Application database:  SQL_Latin1_General_CP1_CS_AS
  • 1000 columns:  SQL_Latin1_General_CP1_CS_AS

SUMMARY:
Pro: The application upgrade completed successfully!
Con: SQL queries/reports must be case sensitive,  Existing lower-case reports are no longer functional.

QUESTIONS:
1.  If I reinstall MSSQL with SQL_Latin1_General_CP1_CI_AS, will lower-case queries be acceptable again?  Or is it the database collation that enforces case-sensitive queries?

2. Are there any available resources that state tempdb must be confg'd with the same collation as the application database?

Thanks again, everyone!
0
 
QlemoC++ DeveloperCommented:
Again: Server (instance) collation determines names above the DB level, but including the DB names.
DB collation determines the meta-data and default data collation.

So no, reinstalling MSSQL does not help.
0
 
bmsandeAuthor Commented:
I think I understand......

Prior to our changes, lower case (case-insensitive) statements ran successfully because the database collation was CI... it had nothing to with the SQL instance collation - correct?

Collation prior to corrections:
SQL 2008 instance:  SQL_Latin1_General_CP1_CI_AS
Application database:  Latin1_General_CI_AS
  • 800 columns:  SQL_Latin1_General_CP1_CS_AS
  • 200 columns:  Latin1_General_CI_AS

Do you see any problems with the collation scenario below?  It's similar to the mess we were originally in, which is going on 8+ years with 5k daily users hitting the application -- and no issues to report during that time.

SQL 2008 instance:  SQL_Latin1_General_CP1_CS_AS
Application database:  SQL_Latin1_General_CP1_CI_AS
  • 1000 columns:  SQL_Latin1_General_CP1_CS_AS
0
 
QlemoC++ DeveloperCommented:
I don't like the SQL_ collations, and always use Latin1_General_CI_AS. But your suggestion should work fine.
0
 
Olaf DoschkeSoftware DeveloperCommented:
statements ran successfully because the database collation was CI... it had nothing to with the SQL instance collation - correct?

I would say no.

If a name like [inivalue] now only works as [IniValue], then this only has to do with master DB (more precisly it contains the INFORMATION_SCHEMA views but anyway system collation makes names case sensitive or not), with the collation applied to it, as it contains the names of any database. So it has to do with the server collation.

Bye, Olaf.
0
 
bmsandeAuthor Commented:
@Olaf,
What if I change the collation of the master DB?  Is the following scenario possible.... would lower-case syntax be accepted when querying the application DB?

  • SQL 2008 instance:
  •  SQL_Latin1_General_CP1_CI_AS

  • master, msdb, tempdb, model:
  •  SQL_Latin1_General_CP1_CI_AS

  • Application database and ALL columns within:
  • SQL_Latin1_General_CP1_CS_AS
0
 
Olaf DoschkeSoftware DeveloperCommented:
Yes, that should work. You may try it this way: https://msdn.microsoft.com/en-us/library/dd207003(v=sql.105).aspx

System databases must be rebuilt to ... or to modify the default server-level collation

Bye, Olaf.
0
 
bmsandeAuthor Commented:
Good to know that collation scenario should be okay to run with.  I'm in a test environment so reinstalling SQL is easier than making any changes.  After I reinstall with SQL_Latin1_General_CP1_CI_AS collation, I will migrate the database with SQL_Latin1_General_CP1_CS_AS collation.  Hopefully case sensitivity is not an issue for my SQL queries. Finding out soon!
0
 
bmsandeAuthor Commented:
Ok, I reinstalled SQL 2014 with collation SQL_Latin1_General_CP1_CI_AS

I migrated my application database to the SQL instance, DB name is contoso.

SELECT CONVERT (varchar, SERVERPROPERTY('collation')) as "SQL Instance"
SELECT DATABASEPROPERTYEX('master', 'Collation') as "master"
SELECT DATABASEPROPERTYEX('model', 'Collation') as "model"
SELECT DATABASEPROPERTYEX('msdb', 'Collation') as "msdb"
SELECT DATABASEPROPERTYEX('tempdb', 'Collation') as "tempdb"
SELECT DATABASEPROPERTYEX('contoso', 'Collation') as "contoso"

Open in new window


Results:
SQL Instance:  SQL_Latin1_General_CP1_CI_AS
master:  SQL_Latin1_General_CP1_CI_AS
model:  SQL_Latin1_General_CP1_CI_AS
msdb:  SQL_Latin1_General_CP1_CI_AS
tempdb:  SQL_Latin1_General_CP1_CI_AS
contoso:  SQL_Latin1_General_CP1_CS_AS

I have a table in contoso named [KIni].  

select * from kini - FAILS
select * from KIni - WORKS

From our dialog I was under the impression that using case-insensitivity on my system DB's would allow for case-insensitive queries against my application DB.

Any idea why it's not working... or is it not supposed to work?

I keep harping on this because case-insensitive queries used to work with the following collation:

SQL 2008 instance:  SQL_Latin1_General_CP1_CI_AS
Application database:  Latin1_General_CI_AS
  • 800 columns:  SQL_Latin1_General_CP1_CS_AS
  • 200 columns:  Latin1_General_CI_AS

This leads me to believe the database collation property is what allowed case-insensitive queries, and not the SQL instance or system DB's.
0
 
QlemoC++ DeveloperCommented:
That is where Olaf and I gave contradicting info. You proofed that I'm correct.
0
 
bmsandeAuthor Commented:
::sigh:: well I guess I learned something, and you were right @Qlemo.  

I just changed collation at the database level;

/****** Set single-user mode & change collation ******/
USE contoso;
GO
ALTER DATABASE contoso SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE contoso COLLATE SQL_Latin1_General_CP1_CI_AS;

/****** Add users again ******/
ALTER DATABASE contoso SET MULTI_USER
GO

Open in new window


Now my lowercase sql queries are accepted.  But all objects within the database are _CP1_CS_AS so I'm back to a collation conflict.  At least the collation corrections got us through the application upgrade, but I hate putting us back to a collation conflict.

Current config which accepts lowercase queries as desired:

SQL instance:  SQL_Latin1_General_CP1_CI_AS
Application database:  SQL_Latin1_General_CP1_CI_AS
ALL database objects:  SQL_Latin1_General_CP1_CS_AS

With the current config anything new added to the db will receive CP1_CI_AS collation, which conflicts with the objects in the db.  Now I have a new mess on my hands :)
0
 
Olaf DoschkeSoftware DeveloperCommented:
If you changed the same way, that's not how you do it. You merely put another label on the jar. But if you label a peanut butter jar to be honey, it still contains peanut butter.
It seems you did and now reversed that and so things work again, as they should.

I think I don't follow you on the problems you face adding new objects to the db. Maybe you didn't yet understood the context. If you want your data to be case insensitive, that's fine, that means your data is case insnsiive, nothing speaks against that, a query for name='smith' will find the same data as name='Smith'. Case sensitivity requires exact match, and that's also true for names of tables and columns, because such names also are just data, data of the system master db, not of your user/application db. So I don't see a problem.

Bye, Olaf.
0
 
bmsandeAuthor Commented:
@Olaf not sure what you mean.  I created a new mess but it satisfies my current objective and will revisit later with more time.  What do your mean that's not how you do it ?
0
 
Olaf DoschkeSoftware DeveloperCommented:
Well, if you really want to change collation, that requires more than just ALTER DATABASE ... COLLATE ...
that does not change the collation of already existing data, therefore it's just relabelling a jar, it doesn't change content.

Bye, Olaf.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 12
  • 7
  • 6
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now