1

I am looking for a technical term that describes the type of query in SQL that makes use of indexes. I completely forgot the name and can not look it up anymore (there's even a Wikipedia article about that, but I can't find it). It has (AFAIK) two or three syllables.

It basically denotes the type of query that makes use of indexes, and constitues of best practices to use when querying the database.

Some of the best practices are these:

  • do not search by perform functions or computations on column names
  • do use LIKE operator with trailing wildcards, not leading
  • compare column value to fixed value (constant),

Eg.

SELECT column, date FROM table (..)

(..) WHERE column LIKE 'A%' -- OK, because looks up the column
(..) WHERE column LIKE '%A%' -- INCORRECT, expensive
(..) WHERE SUBSTRING(column, 1, 3) = 'abc' -- INCORRECT

Do you know what's the term describing these types of queries?

Thanks.

  • 2
    Note that Postgres has an index type that can be used for the expression LIKE '%A%' but neither Oracle nor SQL Server can do that. And you can define an index in Oracle and Postgres that makes SUBSTRING(column, 1, 3) = 'abc' "Sargable" – a_horse_with_no_name Oct 10 '14 at 13:54
  • @a_horse_with_no_name In SQL-Server as well, with index on persistent computed columns. – ypercubeᵀᴹ Oct 10 '14 at 17:44
  • @ypercube: but the query would need to reference the computed column, correct? So you would need to change the query, or does SQL Server rewrite the query when it detects that the expression is the same as the definition of the computed column? – a_horse_with_no_name Oct 10 '14 at 18:14
  • @a_horse_with_no_name - It doesn't rewrite the query but it matches the computed column during query compilation. – Martin Smith Oct 17 '14 at 12:01
6

Sargable (or sometimes sargeable). It's not really a word, it's made up of Search ARGument, and when a WHERE clause is sargable, that mean's it's possible for it to use an index. It doesn't mean it will use the index, and it doesn't mean it will seek, either. A lot of factors go into the optimizer's choice, and the rules can clearly differ between different platforms, and even different versions and editions of the same platform.

References:

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.