January 28, 2008 at 2:02 am
Hi
I wonder what better to my data base case sensitive or case insensitive?
and what the criterions to decide?
thanks
Ido
January 28, 2008 at 2:58 am
Ido Amrani (1/28/2008)
HiI wonder what better to my data base case sensitive or case insensitive?
and what the criterions to decide?
thanks
Ido
Hi Ido,
in terms of object names using case sensitive collation ensures your code is consistent (a stored procedure name is not used with different casings). So this is great when you write plenty of stored procedures :).
However, this is far not as important as the data in your tables. If your collation is case sensitive, then comparisons like: 'a' like 'A' will return false, whereas on a case insensitive 'a' like 'A' will return true. So the question is whether your queries will be depending on such case differences?
Regards,
Andras
January 28, 2008 at 5:09 am
It's not a question of which is better in general. It's a question of which is better for your particular circumstances.
Or in common DBA parlance, "It Depends." @=)
What language(s) are you storing your data in? What sources is the data coming from? Do you need to have the ability to separate out lowercase from uppercase? Are you using Unicode or non-Unicode collations?
These are just some of the questions you have to ask yourself when designing your database. They determine whether you use accent-sensitive / case-sensitive collations or not. If you're still not sure, sit down with your business users and see if it matters to them or not. Usually, none of them have thought about it and you can leave things insensitive.
But be sure of all your options and internal requirements before you decide. Don't just go with whichever you think is better from a generic standpoint.
January 28, 2008 at 5:49 am
Everything everyone else said is 100% true. Just understand that if you don't need case sensitivity, using it just to be cool can add an incredible amount of overhead to your work because CREATE TABLE MyTable is different than CREATE TABLE MYTable or CREATE TABLE mytable. Typo's could make for a very long & tedious day. Be sure it matters before you make the choice.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 28, 2008 at 8:09 am
Hi
thanks for all of you.
i have a little application with a lot of rows but i want to be flexible with my customers.
If someone search of "bilbi" or "Bilbi" it doesn't matter. therefore, I thing to go with case insensitivity thought.
there are more benefit in case sensitivity with security?
Thanks again
Ido
January 28, 2008 at 8:52 am
The only security issue I can think of with case sensitivity is Ye Ole Password issue. It depends on whether you want to use case sensitivity on logins.
January 29, 2008 at 6:38 am
The deciding factor for me is that you can make a query on a case-sensitive database act like a case-insensitive database by adding an UPPER clause.
I may be wrong, but I don't think you can change a query on a case-insensitive database to act like a case-sensitive database. If someone has a way around this, please post it. I have not looked into it much, so there may be a way that I'm not seeing. I'm talking about an EASY way, like the UPPER function.
This is why I make all of my databases case-sensitive.
I think it's kinda the same issue and having Explicit Variables in VB. Sure you can turn that option off, but you will code better (my opinion) if you leave it on.
January 29, 2008 at 6:48 am
It would work for string comparisons though, but a case insensitive database wouldn't need to force the strings to all UPPER case in any regard. It doesn't address the case for object names though. If the table was named mytable or MyTable, you couldn't use MYTABLE in the code.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 29, 2008 at 7:16 am
There are ways around case insensitivity, but they aren't easy. There are usually cursors involved with Substrings or PatIndex/CharIndex used in the code.
We were forced to do a case sensitive scrub on our case insensitive database a while back. I think we used a COLLATE statement in the WHERE clause and the SELECT list. That was the easiest method we could come up with.
January 29, 2008 at 9:02 am
In my opinion, I wouldn't want a developer writing code that can reference mytable and MyTable as the same table. To me that just seems like sloppy coding, just my opinion not trying to offend anyone.
January 29, 2008 at 10:09 pm
i think, the conclusion depends on what you need and the way you like. 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply