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.