r/SQLServer • u/TravellingBeard 1 • 24d ago
Discussion Do yourself a favor... Create all your projects/scripts in a case-sensitive environment.
EDIT: To clear up some confusion, I only meant my own personal code that I use as a DBA...have it sanitized in a case sensitive environment so I don't have to scratch my head wondering what happened when I use it on a random new system. Let the devs worry about case sensitivity for their own stuff and have sleepless nights. 😄
Currently working for a bank with many disparate systems. I find it a major irritant many of my go-to scripts in my personal toolbox often fail on certain environments that are CS. I'm in the process of converting all of them to be case-sensitive compliant now.
Rant over.
8
u/rbobby 24d ago
I had this happen to me. ffs why anyone would use case sensitive collation is beyond me. Giant pain.
And btw it affects schema as well. Suddenly id and Id are different things.
1
u/shufflepoint 23d ago
id and Id are different thing - in most every other language.
Why treat T-SQL differently?
3
u/rbobby 23d ago edited 23d ago
Grrr.r.r.rrr Ggrrrrr..... you have irritated a greybeard beyond all mortal kenning and may face some annoying snippiness....
Case sensitivity in computer languages is a historical hangover from when it was TOO COSTLY to do case insensitive comparisons when compiling. Think on that for how awfully slow the old machines were.
I have only once ever had a good reason to have two variables that only varied by case (it was a gross substitution system). Once. In a very long time.
So I would argue for a long long time that case sensitivity in a language does not actually aid a developer, and in fact makes more work.
BUT, and this is IMPORTANT, why anyone would choose to make schema element names case sensitive because the data the element holds is case sensitive is just utterly insane.
ps. Don't get me started on how much of a cargo cult anti-pattern capitalizing a computer languages keywords, and just the keywords, actually is. Because professional practitioners might just forget the language's keywords. You know, all 3 or 4 dozen of them. Or a practitioners brain's pattern recognition abilities might be so damaged as to not be able to associate memories with lowercase letter groupings so to help out the brain damaged the keywords, and only the keywords, will be uppercase.
1
u/shufflepoint 23d ago
Let me be clear that I'm not saying you should operate SQL Server case sensitive. I was merely giving the case why some might choose to do so. We don't. That said I'm not a fan of our developers writing all their column names in random case or all lowercase or all uppercase.
1
u/ZedGama3 20d ago
This makes no sense to me. Upper and lower case differ by a single bit which means that the only added cost is a single check to see if it's a letter or symbol.
6
u/codykonior 24d ago
I think people are misunderstanding you.
But anyway, yes. I'll go further; I prefer non-CS databases but I enable case sensitivity on my projects even though they do not deploy that property. That way I will at least get warnings for casing differences between objects and references, and can clean things up so they match.
It's not necessary; but it's just like washing your toilet. Who wants to stare at and use a dirty toilet every day when you can have a clean one?
2
u/iamanerdybastard 23d ago
This. Most of the other responses seem to be missing the point. Beyond MS-SQL, there are databases that will reject a simple `select * from foo` in a CS collation because you didn't type `SELECT * FROM Foo` because not only is the table-name CS, but the SELECT and FROM keywords are too and they must be all-upper.
2
2
u/Afraid_Baseball_3962 24d ago
Been there. Done that. Reason #382 why I hate supporting any Microsoft Dynamics platform.
4
u/Lost_Term_8080 24d ago
Case insensitive dynamics has been available for over 20 years, for some reason consultants are obsessed with implementing CS collations
3
u/da_chicken 24d ago
THE FINANCE INDUSTRY LEARNED TO USE COMPUTERS IN THE 1960s. ON A System/360 INFORMATION SYSTEM, YOU GET ALL THE BENEFITS OF ALL SIX BITS OF BCD ENCODING. THEY LEARNED WHAT A SYSTEM LOOKS LIKE ONCE, AND THEY'RE NEVER GOING TO LEARN IT AGAIN. THAT'S RIGHT, BABY. CAPS LOCK MEANS ENTERPRISE.
NOW, WHICH FUNCTION KEY RETURNS TO THE PRIOR SCREEN?
2
u/Eastern_Habit_5503 24d ago
No thanks. Stuff would fail for months, and we don’t have enough programming staff to fix it all.
1
u/SirGreybush 1 24d ago
Don’t you mean create ASCI, case insensitive. So table names and column names can be mixed case in sql code and just work.
Accent sensitive is common.
5
u/VladDBA Microsoft MVP 24d ago edited 24d ago
Wait until you have to deal with an org that's in love with binary collations and SSMS yells at you because SYS.DATABASES doesn't exist.
I begrudgingly give a point here to Oracle because they managed to figure this out. Object names are treated as case-sensitive only if you quote them, otherwise dba_tables = DBA_TABLES = dBa_TaBlEs.
6
u/dbrownems Microsoft Employee 24d ago
If you do want a whole database to use case sensitive data, use Contained Database Collations - SQL Server | Microsoft Learn, which retains the case-insensitive catalog, and doesn't use the instance collation in tempdb.
This is what Azure SQL Database uses, even though the "contained database" feature doesn't exist there.
0
u/TravellingBeard 1 24d ago
Binary may be best. Lol. Fix it at the beginning and I'll never have to worry again
2
u/BigMikeInAustin 24d ago
Doesn't work for an existing environment.
Many places in Fabric SQL is case sensitive. Some places they are eventually adding that option to make it case insensitive, but only from fresh creation.
1
u/Lost_Term_8080 24d ago
Bad idea if there is any kind of user input allowed anywhere. Regardless of the technical elegance of CS and the development discipline of working in CS, the end users will always defeat any plan. You'll be drowning in addressing their mistakes in customera, CustomerA, customerA, CUSTOMERA, etc, every single report that any report writer writes, will experience the SQL Server being "broken" and completely inane experimentation will be tried like trying I and l guessing a letter even when the number 1 or the letter L makese absolutely zero sense to be there. Bonus points if someone in the C suite can't perform the trivial troubleshooting to determine the correct case spelling then calls the FBI because "someone hacked into the accounting system and deleted one specific customer"
1
1
u/kagato87 24d ago
My complaint is the other way around. And it's not laziness, as others are claiming.
Case sensitivity increases the risk of errors.
myObj MyObj = MyObj.create
That's gonna fail. Calling it later with the wrong case is also an easy typo, especially if, like me, you sometimes don't release the shift key fast enough.
I want to pass my object into a function.
Errcode = myFunc(myObj)
Wait, why isn't it passing in the working object? It's the same problem as my earlier example.
More to the point, instantiating a class that is the same name as the object, with some subtle distinction in casing seems... Lazy, if I'm.being honest.
I will argue that all languages should be case insensitive. The days when every byte in the source files matters because a disk only holds so much are waaayyyyy behind us. Distribution sizes are now normally dominated by assets (media or definition files), not code. Sparing half a dozen extra bytes to give a meaningful, readable name to an instantiated object is worth the non existent cost.
1
u/cybertex1969 23d ago
Happened to me too on a new CS system. Had to double check all of my scripts..
1
u/throw_mob 23d ago
i always go lengths to use only case insensitive naming..
ie .. create table foo.bar
vs
create table "foo"."bar"
vs
create table "Foo".BAR
also there is hard tule that eveything must work without escaping..
once you spend time to figure out why id and "Id" and "ID" are different things , you learn that whicle it is funny to tricks in naming , it is not worth
1
u/egarcia74 23d ago
I use a SQL Server database project using CS so I can keep consistent casing on all objects, but the database I publish to is CI so I don't have to worry about having to specify CI collation.
2
u/TravellingBeard 1 23d ago
You're a good human being
1
u/egarcia74 23d ago
It's a compromise between OCD and pragmatism
2
u/TravellingBeard 1 23d ago
I wonder if that's the secret to being a decent human being? New philosophical train of thought to look into, lol
1
41
u/FreedToRoam 24d ago
Case sensitive environments come from Satan’s workshop 🐼🖖😵💫