1

I haven’t yet tried out SQL Server on Linux, but I have a question regarding case sensitivity.

In MySQL and Oracle (as far as I am aware) table names and database names are case insensitive on Mac & Windows, but case sensitive on Linux. I understand this has something to do with the case sensitivity of the underlying OS.

PostgreSQL, on the other hand, doesn’t work that way.

The question is: are database and table names in SQL Server on Linux case sensitive?

I ask this because I always recommend sticking to the case in which names are defined, but I want to check whether it is also future-proofing the project.

  • Maybe that's with the default collation on those platforms - given the lazy MySQL coders I've seen I'd be surprised if there weren't a way to turn case sensitivity off. :-) – Aaron Bertrand Feb 9 '17 at 21:11
  • 1
    Oracle's behaviour does not depend on the operating system. – a_horse_with_no_name Feb 9 '17 at 21:12
  • @a_horse_with_no_name Thanks for confirming, I didn't think that it did, just suspected that perhaps the default collation there is different. – Aaron Bertrand Feb 9 '17 at 21:14
  • The default collation has no influence on the case-sensitivity of object names. – a_horse_with_no_name Feb 9 '17 at 21:18
  • @a_horse_with_no_name Ok, I was just going by what he said, that he observed case sensitivity on Linux, and if that's possible, it's controlled by something. I know that Oracle recommends upper case for all identifiers, but I don't know if there is a way to force it. I can spell Oracle, but that's about it - thankfully the question isn't about Oracle directly. – Aaron Bertrand Feb 9 '17 at 21:24
1

It has nothing to do with the underlying OS. It has to do with the collation of the instance and the collation of the database. Here is an example that shows that database names, table names, column names, and data are not subject to case sensitivity by default.

enter image description here

If you want those things, you can turn them on, by specifying a case-sensitive collation for the column, for the database, or - I think - for the instance. (The images I've been using for SQL Server Docker containers don't have the traditional setup package where you can pick instance collation prior to setup, and changing instance collation after the fact might not be fun.)

Perhaps you're thinking about physical files? I know when I was moving my stuff from IIS to a Unix host I had to change references to things like file.css because the file on disk was actually File.css and the server wouldn't be able to locate it. SQL Server doesn't work that way.

Regardless

Yes, you should absolutely stick to the proper casing of things, and that future-proofs you no matter what operating system you use to host SQL Server. Anybody can come along and change the collation of various bits of your installation (or deploy your code to a place where the collation is already different). See these articles:

  • Just checking — you’re demonstrating that table names are indeed case insensitive on (Ubuntu) Linux? In MySQL at least individual tables are stored in physical files, and that certainly affects is case sensitivity. I know other databases don’t necessarily do things the same way. – Manngo Feb 9 '17 at 21:07
  • @Manngo That's right - this isn't MySQL and I have no idea why a table name would have any dependency on a file name. You'll notice I created a table called dbo.floob and then successfully referenced it as DBO.Floob and Dbo.FLOob. – Aaron Bertrand Feb 9 '17 at 21:09
  • One more: Is this also the case for database names: can you use USE aNyCaseDb with impunity? BTW, you don’t need to convince me that it’s a bad idea to get careless about case. I’m just trying to establish what the actual rules are. – Manngo Feb 9 '17 at 21:12
  • No, same thing, notice I say USE TEMPDB; and even the message after doing that reflected that the actual database name is tempdb. Anyway, IMHO, the "rules" should always be that you match case with the metadata. – Aaron Bertrand Feb 9 '17 at 21:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.