StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>So you have an index key with two or more columns. For such indices, the order of columns is important. </p> <p>For example: if the index key is <code>(ColA,ColB,ColC)</code> (<code>CREATE INDEX MyIndexName ON MySchema.Mytable(ColA,ColB,ColC) ...</code>) then SQL Server (by default) <a href="https://connect.microsoft.com/SQLServer/feedback/details/695044" rel="nofollow">can't skip</a> over the first column (<code>ColA</code>) to seek on the second column (<code>ColB</code>). For compound indices, SQL Server can seek on column if there is a equal predicate (having <em>usually</em> this template <code><Column> = <Constant></code> but read, also, the last paragraph of my answer) over the previous columns:</p> <p><em>[1] Are SARGable <=> index seek the following predicates <strong>(just a few examples)</em></strong></p> <pre><code>WHERE ColA=@p1 WHERE ColA<=@p1 WHERE ColA>@p1 WHERE ColA=@p1 AND ColB=@p2 WHERE ColA=@p1 AND ColB=@p2 AND ColC=@p3 WHERE ColA=@p1 AND ColB=@p2 AND ColC<=@p3 WHERE ColA=@p1 AND ColB=@p2 AND ColC>@p3 .. For a complete picture please read SQLMag article from the last paragraph </code></pre> <p><em>[2] Aren't SARGable or aren't fully SARGable the following predicates <strong>(just a few examples)</em></strong></p> <p><code>WHERE ColB=@p2</code> because is missing a equal predicate on the previous column: ColA In this case an index with this key <code>(ColB)</code> could be useful. </p> <p><code>WHERE ColA>=@p1 AND ColB=@p2</code> because there isn't an equal predicate on the first column: ColA. In this case an index with this key <code>(ColB,ColA)</code> could be useful.</p> <p><code>WHERE ColA=@p1 AND ColB<=@p2 AND ColC=@p3</code> because the predicate on the second column (<code>ColB</code>) is not equal. In this case, an index with one of these keys <code>(ColA,ColC,ColB)</code> or <code>(ColC,ColA,ColB)</code> could be useful.</p> <p>More on this subject (SARGable): <a href="http://sqlmag.com/t-sql/inside-search-arguments" rel="nofollow">link</a>;</p> <p>This means that if you have an index with this key <code>(ColA,ColB,ColC)</code> then these indices <code>(ColB)</code>, <code>(ColB,ColA)</code>, <code>(ColA,ColC,ColB)</code> or <code>(ColC,ColA,ColB)</code> <strong>could be, also, useful.</strong></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload