0

Last week I had already started two threads dealing with slow SQL selects that probably did not use the index.

Today I noticed the following: The customer and the material are involved in almost all index accesses.

Customer and material are nvarchar fields everywhere, and the customer and material numbers are the same in all tables.

Only the length of the nvarchar fields is different in different tables. The customer sometimes consists of an nvarchar (10), sometimes of an nvarchar (20) and sometimes of an nvarchar (30). This is because these tables were created by external consultants who each used a different length for the customer-fields.

However, the customers are only seven characters everywhere in all tables.

Could that be a reason that the index access is not working?

Does the nvarchar length play a role when linking different tables via fields for which an index exists?

Improve this question
3
  • I think that is very unlikely, do you get any warnings on the execution plans for your queries ?
    – Stephen Morris - Mo64
    Commented Mar 1, 2021 at 9:45
  • 1
    Please share your unwanted execution plan and the DDL for the index you’re expecting it to use
    – Andrew Sayer
    Commented Mar 1, 2021 at 10:16
  • 2
    More likely its all the type conversions in that query. There are 30 PlanAffectingConvert warnings in the query plan.
    – David Browne - Microsoft
    Commented Mar 1, 2021 at 13:07

1 Answer 1

Reset to default
1

Length of the fields does not determine if the index is used for a query in SQL Server. But it could affect how performant that index is when it is used to serve the data. It's the same idea why indexing a UUID field is a little less performant than indexing an INT, one reason being the difference in the number of bytes that the values use when indexed.

When and how an index is used is solely based on which columns and their order in how you define in the index, the columns you use in your predicates (JOIN, WHERE, HAVING clauses), the current statistics on those columns at the time you run your query, and how you are comparing those columns in the predicates (e.g. in your linked thread you're using a function in one of your predicates which likely is preventing an index seek from happening and instead an index scan occurs).

Things like using functions in predicates, or type conversions when comparing two different data types could cause a sub-optimal plan, and definitely cause performance problems like cardinality estimate issues.

Improve this answer
6
  • Thanks for the detailed answer.
    – SipCat
    Commented Mar 1, 2021 at 15:42
  • 1
    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
    – SipCat
    Commented Mar 1, 2021 at 15:42
  • 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
    – SipCat
    Commented Mar 1, 2021 at 15:42
  • 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?
    – SipCat
    Commented Mar 1, 2021 at 15:43
  • @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.
    – J.D.
    Commented Mar 1, 2021 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.