Timeline for Does the length of the fields play a role in index access in MS-SQL?
Current License: CC BY-SA 4.0
7 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 1, 2021 at 16:29 | comment | added | J.D. | So to answer your question directly, your query above does use every field you say is in the index, and therefore is covered by that index. | |
| Mar 1, 2021 at 16:28 | comment | added | J.D. |
@SipCat the order of the fields listed in the JOIN clause don't matter, but the order the fields are listed in the index definition do and determine which combinations of fields will be covered by that index. In other words when you create an index on ColumnA, ColumnB (so ColumnA is first in the index field list) then that index can cover queries that use either only ColumnA or use ColumnA and ColumnB in there predicate clauses (JOIN, WHERE, HAVING clauses). But that index will never be able to cover ColumnB by itself since ColumnB's ordering depends on ColumnA.
|
|
| Mar 1, 2021 at 15:43 | comment | added | SipCat | The join on F_Bonus uses these fields in order: gueltig_ab, gueltig_bis and CD_Kunde. Therefore the table F_Bonus has a non-clustered index in exactly this order (gueltig_ab as index-field 1, gueltig_bis as index-field 2 and CD_Kunde as index-field 3). Would that be correct? | |
| Mar 1, 2021 at 15:42 | comment | added | SipCat | With this JOIN, on the other hand, I'm not sure which index the SQL server would use for the F_Bonus table: LEFT OUTER JOIN dbo.F_Bonus ON S631_000.datum >= dbo.F_Bonus.gueltig_ab AND S631_000.datum <= dbo.F_Bonus.gueltig_bis AND S631_000.PKUNRG = dbo.F_Bonus.CD_Kunde | |
| Mar 1, 2021 at 15:42 | comment | added | SipCat | By the function that prevents an index seek, you probably mean the CONVERT, right? LEFT OUTER JOIN F_UmstellkostenSWDS AS u1 ON CONVERT(varchar(6), v1.Tag, 112) = u1.Monat | |
| Mar 1, 2021 at 15:42 | comment | added | SipCat | Thanks for the detailed answer. | |
| Mar 1, 2021 at 13:12 | history | answered | J.D. | CC BY-SA 4.0 |