ahmed.net (10/9/2010)

---

Whether or not changing the order of columns in where clause can effect performance(using OR in where)?

ahmed.net (10/9/2010)

---

Witch query is more efficient??
Table Structure:
CREATE TABLE [dbo].[tbl_test](
[ID] [int] NOT NULL,
[Title] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_tbl_test] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
Query1:
SELECT ID, Title
FROM tbl_test
WHERE (ID = 1) OR (Title LIKE '%abc%')
Query2:
SELECT ID, Title
FROM tbl_test
WHERE (Title LIKE '%abc%') OR (ID = 1)

I just want to know
Whether or not changing the order of columns in where clause can effect performance(using OR in where)?

comparing ID field is faster than the like statement on Title column. so if the first statement in OR is true next statement should not be checked..
I think Query1 is faster as Like statement on title will not always be checked. and in query2 Like statement on title will always be checked...
What u say ??

harag (10/11/2010)

---

Now if it was an AND clause then maybe it might make a difference, especially if you have indexed the ID column but not the title column.

If you have ID=1 AND title LIKE '%text%' then if the first condition returns false then why would SQL need to check the next condition?