Search code examples
sql-serverperformanceindexingrdbms

SQL Index performance ,, which is better?

Is the following SQL good or bad practice from a performance perspective?

Two queries, searching by a common column:

CREATE INDEX tbl_idx ON tbl (a, b);

SELECT id, a, b
 FROM tbl
WHERE a = @a
  AND b = @b;


SELECT id, a, b
 FROM tbl
WHERE b = @b;
Solution

  • This index

    CREATE INDEX tbl_idx ON tbl (a, b);

    Will be useful for these queries

    where a= and b =  
where a= and b>
where a like 'someval%' and b=

    but not useful for these queries:

        where b=
    where a> and b=
    where a like '%someval%' and b=
    where isnull(a,'')= and b=

    In summary, in a multicolumn index, if SQL Server was able to do a seek on first key column then the index would be useful..

    Coming to your question, the first query would benefit from index you created whereas second query may tend to do a scan on this index..

    There are many factors which dictate whether seek is good or bad.In some cases SQL Server may tend to not use the index available like bookmark lookup cost exceeds limit..

    References: