WHERE
  (uID = @uID) AND
  (sID = @sID) AND
  (gID = @gID)

WHERE
  (uID = @uID) AND
  (sID = @sID) AND
  (gID = @gID) AND
  (info1 LIKE '%'+@info1+'%' OR @info1 IS NULL) AND
  (date1 < @date1 OR @date1 IS NULL)

quote:

---

Originally posted by X002548

So..are you saying it dosn't use INDEX INTERSECTION anymore?

---

quote:

---

Originally posted by namman

For queries with condition as the red part, the index will be useless, doen't matter columns in the index. Index is not that simple, I think .

---

quote:

---

Originally posted by GilaMonster

quote:

---

Originally posted by X002548

So..are you saying it dosn't use INDEX INTERSECTION anymore?

---

It can. Doesn't often but can. That's why I said 'typically' and 'usually'.

--
Gail Shaw
SQL Server MVP

---

quote:

---

Typically (not always but usually) SQL uses one index per table in a query.

---

quote:

---

A lookup occurs when a nonclustered index was used to locate rows for a query, but the nonclustered index did not contain all of the columns required for the query.

---

  WHERE someCol = @val OR @val IS NULL

quote:

---

...there’s a single nonclustered index on all three columns, in the order ID, SomeDate, SomeString. If there’s an inequality predicate, then then the index is only fully seekable for the following queries

…  WHERE ID = @ID AND SomeDate = @dt AND SomeString > @str
…  WHERE ID = @ID AND SomeDate > @dt
…  WHERE ID > @ID

---

WHERE ID = @ID AND SomeString > @str AND SomeDate = @dt

IF @Param1 IS NOT NULL
INSERT INTO #TEMP (T_MyPK) SELECT MyPK FROM MyTable WHERE Col1 = @Param1
ELSE
IF @Param2 IS NOT NULL
INSERT INTO #TEMP (T_MyPK) SELECT MyPK FROM MyTable WHERE Col2 LIKE '%' + @Param2 + '%'
...
SELECT Col1, Col2, Col3, ...
FROM #TEMP
     JOIN MyTable
         ON T_MyPK = MyPK
WHERE     (@Param1 IS NULL OR Col1 = @Param1)
      AND (@Param2 IS NULL OR Col2 LIKE '%' + @Param2 + '%') 

DEFINE @strSQL nvarchar(4000)

SELECT @strSQL = 'SELECT Col1, Col2, Col3, ...
FROM   MyTable
WHERE  1=1'

IF @Param1 IS NOT NULL 
SELECT @strSQL = @strSQL + ' AND Col1 = @Param1'

IF @Param2 IS NOT NULL 
SELECT @strSQL = @strSQL + ' AND Col2 LIKE ''%'' + @Param2 + ''%'''

EXEC sp_ExecuteSQL @strSQL,
                   N'@Param1 int, @Param2 varchar(100)',
                   @Param1 = @Param1,
                   @Param2 = @Param2

quote:

---

Originally posted by R

quote:

---

Typically (not always but usually) SQL uses one index per table in a query.

---

What's the reason for this please? This obviously has a huge impact on the effectiveness of my indexes!

---

quote:

---

In another post on SQLTeam, I read that I shouldn't use query code that contains...

  WHERE someCol = @val OR @val IS NULL

...even if someCol is contained within an index because SQL reverts to performing a scan. Is this correct?

---

quote:

---

If so (and I apologise for my ignorance here), why hasn't SQL been developed to recognise if @val is null, and then execute the query more efficiently using a seek?

---

quote:

---

If the first example above was written as

WHERE ID = @ID AND SomeString > @str AND SomeDate = @dt

(with second and third predicates swapped around) would SQL be smart enough to execute the query using a seek only, or is column order essential in WHERE predicates?

---

quote:

---

Originally posted by Kristen

Yeah, but there might be a test on another, indexed, column that narrows the effort.

---