Steve said: >Why should case sensitive data comparisons be maintained ? > >It seems to me that it's only in the most obtuse examples where it is of any >use e.g. algebra, formulae, equations etc. Even then, how many people will >search for a formula ? >However, I would agree, that there is an absolute need to maintain the case >of the actual stored data. But we shouldn't confuse the storage of data >with the querying of data.
Another reason is if people use character strings for keys (a common activity), and those keys are also referenced in a programming language. We would want an equality comparison to return the same result whether it is done in the database or the program. >By forcing people to use UPPER/LOWER functions to remove the case >sensitivity, you are condemning them to considerably poorer performance. As >I understand it, UPPER/LOWER and LIKE operators will cause SQLite NOT to use >indexes if there are any defined for the column. > >Can't we solve this in the index ? I wasn't exactly advocating the use of UPPER/LOWER. Rather, I suggest having smart comparison operators in combination with column indexes that are customizable to be a case-sensitive index or non-sensitive index. Comparisons between columns with the same kind of index will do as you expect; comparisons between a column and a string literal, or a caller bind variable, would also follow the rule of the column index. Comparisons between a senstive and a non (not that this would ever happen in a good design) would be sensitive if the non preserves case. Regardless, it should be possible and easy to do case-sensitive comparisons and/or be able to declare that indexes and unique or foreign key constraints are case-sensitive, even if this isn't the default behaviour when unspecified. -- Darren Duncan --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
