SQL Select with WHERE condition, what happens in the background?

Question

Assume I do not have text indexing on. Let say I have:

SELECT * FROM myTable WHERE myTable.columnA LIKE 'bobo'

Will the SQL engine go through every row and only return those matching conditions or is SQL smarter and does do some hidden native indexing?

I'm on MSSQL 2000, 2005, 2008 but if other versions of SQL have different approaches I'm all ears.

Is there a good online doc or even book that goes into how operations are carried out?

Answer 1

Put that query into an SQL Server Management Studio query, and enable the "Include actual execution plan" option before you run it. It will give you a detailed diagram of all the steps that are undertaken to execute the query, along with all of the indexes that are being used and how.

Answer 2

If you look into the execution plain for this query:

SELECT  *
FROM    mytable
WHERE   columnA LIKE 'bobo%'

you will see that it will be rewritten as:

SELECT  *
FROM    mytable
WHERE   columnA >= 'bobo'
        AND columnA < 'bobP'

, using INDEX SEEK over an index on columnA, if any.

Answer 3

Section 3 of this book, "Query Execution" covers the main elements that you'll see when you view the execution plan, as Welbog suggested.

Answer 4

I am not exactly sure about MSSQL, but I can tell you how LIKE clauses work in other databases and I assume MSSQL is similar.

You don't have any wildcards in your select so in effect the query becomes:

SELECT * FROM myTable WHERE myTable.columnA = 'bobo'

If you have a traditional non-fulltext index on myTable.columnA then

SELECT * FROM myTable WHERE myTable.columnA LIKE 'bobo%'

can use the index, however

SELECT * FROM myTable WHERE myTable.columnA LIKE '%bobo'

and

SELECT * FROM myTable WHERE myTable.columnA LIKE '%bobo%'

cannot use the index and will cause a full table scan where the server will examine every row of the table. The difference is the wildcard is in the front: '%bobo'. The DB can convert LIKE 'bobo%' to a range scan on the index, but it cannot with LIKE '%bobo' because of the nature of tree style indexes.

If you really need to to a LIKE '%bobo' on you data then consider making a derived column myTable.columnB with content reverse(myTable.columnA) and do a LIKE 'obob%' on it.

If you really need to do LIKE '%bobo%' then you are using your database like a search engine and either need to use full text indexing or consider using an external full text search engine.