Does the length of the fields play a role in index access in MS-SQL? 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?
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.