Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
NeilF
Starting Member
2 Posts |
 Posted - 2011-07-08 : 14:53:15
|
| Trying to gauge how clever SQL is...1) If you define an index, is it automatically efficient in both directions? ie: If you wanted to read a million entries in date order or reverse date order, paging through 10 at a time, will an index on date be enough for both orders to be efficient? Or do you need to define two indexes, one for each order?2) If you define an index down a name, and want to search on it (in an case insensitive way), should you define an index down a lower case version of the name, and search down that instead (using lower case)? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-08 : 15:03:18
|
| 1) One index is enough2) With the default collations, SQL is case insensitive, so the index is usable no matter what case you use. If your DB is case sensitive, you have some fun as you can't define an index on a function of a column, only on a column. If you wanted an index on LOWER(<somecolumn>) in a case-sensitive database you'd have to define a computed column and index that.--Gail ShawSQL Server MVP |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-08 : 15:04:33
|
1) Best way to find out is to test performance. It's hard to say because it's dependent on how many rows are being returned and how the query optimizer processes the query (scan, seek, bookmark lookups, etc.)2) No. Case (in)sensitivity is managed by the collation of the column, it's independent of the index. If you use a case-insensitive collation then it won't matter. If you want to force a case-sensitive search on an insensitive column, use the COLLATE keyword to coerce it to a case-sensitive collation. , what she said |
|
|
|
NeilF
Starting Member
2 Posts |
Posted - 2011-07-09 : 02:49:20
|
| Thanks, I'm initially talking about mysql, but may be talking longer term about full SQL.So if I'm right, regarding my two questions:-1) If I define an index on "transaction_date" and want to show transactions in date order, or reverse date order, that index WILL be efficient in BOTH directions, so defining an reverse date field as an additional index will be of no benefit.2) If I have a table with a name in, with the following entries "Bill", "Bob", "BOD", "Brad" as far indexing and searching, these entries will behave as if they were all lower case. So "Bob" and "BOD" will appear next to each other, and will both be LIKE "bo%". And there is no overhead in this, so it is not worth creating another dedicated field for the index. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-09 : 05:27:00
|
| These answers are for MS SQL Server. What MySQL does, I have no idea.2) Providing your database is defined with a case-insensitive collation, yes.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Couple of NOOOB SQL questions...