How do I determine the correct index on MS-SQL-Server based on a select?

I have two questions about the index management of MS-SQL-Server 2019

-
If the term "table scan" appears in the execution plan, is that an indication of a "full table scan", and that means that no index has been used here?

-
I'm never sure if MS-SQL will use my indexes. But I also don't know how to find out which index it actually uses.

For this join, I create an index on the F_Skonto table with the three columns gueltig_ab, gueltig_bis and CD_Kunde. Is that correct?

LEFT OUTER JOIN dbo.F_Skonto 
    ON S631_BSA.SPTAG >= dbo.F_Skonto.gueltig_ab 
    AND S631_BSA.SPTAG <= dbo.F_Skonto.gueltig_bis 
    AND S631_BSA.PKUNRG = dbo.F_Skonto.CD_Kunde

And for this join I create an index with the two columns Monat and CD_Kunde on the table F_Umstellkosten. Is that correct?

LEFT OUTER JOIN F_Umstellkosten AS u1 
    ON CONVERT(varchar(6), v1.Tag, 112) = u1.Monat 
    AND v1.CD_Kunde = u1.CD_Kunde

You are unlikely to ever see index use because of this in the query:

CONVERT(VARCHAR(6), v1.Tag, 112)

Because it has to calculate the values from v1.Tag, it can't simply use the histogram in the statistics to determine row counts. It must scan the entire table to find all the values that match, and it has to do the conversion each time.

If u1.Monat and v1.Tag are not the same data type, adding a column that is the same data type might be necessary (on either u1 or v1, whichever).

However, the bigger issue is, you're moving all the data. The query has no WHERE clause to filter anything. Without a filter, the most efficient way to move everything is through scans. Index seeks wouldn't make sense. You might see some index usage on the JOINs between tables (once you fix the CONVERT problem), but possibly not since all the rows are being moved.

StackExchange Database Administrators Q#286080, answer score: 5