SQL Index Question: Why does SQL Server prefer this NONCLUSTERED index to CLUSTERED?

Question

I have the following query:

SELECT
    COUNT(*)
FROM
    FirstTable ft
        INNER JOIN SecondTable st ON ft.STID = st.STID

As you can guess, "STID" is the primary key in "SecondTable" ... and "FirstTable" will have a pointer to this second table. Here are the indexes I have:

FirstTable: CONTINUOUS INDEX in the "STID" column

SecondTable: CLUSTERED PRIMARY KEYINDEX on "STID"

The request above gives me the subtree cost of 19.90 and takes 2 seconds .

After starting the database configuration advisor for this query, they suggested making the same index that I had in the second table ... but not clustered. So I tried this with these results.

FirstTable: CONTINUOUS INDEX in the "STID" column

SecondTable: NONCLUSTEREDINDEX on "STID"

10.97 < 1 second!

100% ... NONCLUSTERED , CLUSTERED ?

Answer 1

, .

( , ), , , MERGE JOIN.

. , .

:

SELECT  SUM(first_table_field + second_table_field)
FROM    FirstTable ft
INNER JOIN
        SecondTable st
ON      ft.STID = st.STID

, .