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?

3
  • I think that is very unlikely, do you get any warnings on the execution plans for your queries ? – Stephen Morris - Mo64 Mar 1 at 9:45
  • 1
    Please share your unwanted execution plan and the DDL for the index you’re expecting it to use – Andrew Sayer Mar 1 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 Mar 1 at 13:07
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.

6
  • Thanks for the detailed answer. – SipCat Mar 1 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 Mar 1 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 Mar 1 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 Mar 1 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. Mar 1 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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