Indices for internal joins where clause

Question

If I had the following query:

select some cols 
   from tbl_a
INNER JOIN tbl_b ON tbl_a.orderNumber = tbl_b.orderNumber
   where tlb_b.status = 'XX'

Assuming that both tables only have clustered indexes by sequence number only, it would be better in terms of performance to expand the clustered index in table b to include the status column referenced by the where? Clause

Answer 1

• You expand tbl_b, to add a status after the order number create clustered index ... on tbl_b(orderNumber, status). For the request above there will be no noticeable difference. Anyway, the plan will have to scan tbl_b from end to end and match each order number in tbl_a (possibly merging).

• tbl_b, : create clustered index ... on tbl_b (status, orderNumber). . tbl_b, , "xx", tbl_a , .

(, "" ), . , ​​ "", , . , . , , orderNumber, ( PK).

. tbl_a tbl_b , . . tbl_a 10 10 , tbl_b 10M 10M, , 2. , tbl_a tbl_b 10 .

Answer 2

, . . tbl_b .

, , .

Answer 3

SQL Server where. SQL Server . :

(status, orderNumber)

, , orderNumber . .

, . "Failed", 1% , . , SQL Server .

Answer 4

, ... .

, . , ( / ) . - IO .

Answer 5

, , . , , - , , . , , , .

Answer 6

, , . , , .

( , ). , , , (, ).

Answer 7

MS :

... . , , , , .

Based on this, I would not add a status column to a clustered index and create a separate, nonclustered index, which can be a coverage index if there are other columns to consider.