which example is best at using indexes

Using mssql server 2008. I need to compare performance. I don't have access to query analyzer until next week. It is just a 开发者_运维技巧crazy thought, I want to know which script will perform best.

I am wondering which script(s) can use the index on txt and why.

The 2 tables has a total of 3 indexes and both tables has approximate 1 mil rows

table1(id int(clustered index), name varchar(10))          
table2(t1_id int(non-clustered index), txt varchar(10)(non-clustered index)) 

SQL1: (2*joins)

SELECT *
FROM table1 t1 LEFT JOIN
table2 t2 ON t1.id = t2.t1_id
AND NOT t2.txt LIKE 'blue%'
AND NOT t2.txt LIKE 'green%'
INNER JOIN table2 t3 ON t1.id = t3.t1_id
WHERE t2.id is NULL    

SQL2: (1*join 1*'or')

SELECT *
FROM table1 t1 LEFT JOIN
table2 t2 ON t1.id = t2.t1_id
AND (t2.txt LIKE 'blue%' or t2.txt LIKE 'green%')

---

In your first sql query you should really not use two joins and instead us a NOT EXISTS, because this will not affect the number of columns thrown around in the temp DB.

SELECT *
FROM table1 t1 
  INNER JOIN table2 t3 ON t1.id = t3.t1_id
WHERE NOT EXISTS (
  SELECT TOP 1 1 
  FROM table2 t2 
  WHERE t1.id = t2.t1_id
    AND NOT t2.txt LIKE 'blue%'
    AND NOT t2.txt LIKE 'green%')

It's much more readable and does the exact same work, however you should avoid double negatives ("NOT EXISTS", "NOT LIKE") because it makes readability harder.

If the performance of the second query is usable or almost the same, I would suggest using that.

Also have you thought of a full text index instead?

---

I don't think you are really comparing apples with apples here; using a Not Like in one query and a like in the other is going to give a significant difference in behaviour. the optimiser is unlikely to use an index the for txt column on SQL1 due to the not like. It generally gives a very non-distinct (or non-selective) query. That is you are in effect asking the optimiser to find every thing in the txt column except those values starting with either blue or green. This is likely to mean you are asking for most values in that column.

Also I don't see that joining to table2 twice gives you anything.

Of course while what I say may be of interest the only real opinion that matters is that of the explain plan.