SQL Server 2022 : unique index with two columns not working correctly

Question

I have a table with users with over 50000000 entries. I want to check for the presence of already registered users on the server and I execute this request.

SELECT *
FROM 
    (SELECT TOP (1000) * 
     FROM UserdatasTemp) AS ut
     JOIN Userdatas AS u ON u.Login = ut.Login 
                         AND u.Password = ut.Password
      -- ...OTHER JOINS...

I use 2 unique indexes on the login and on the password column. At the same time, I have 2 query plans in which the search for the first index is slow and the second one is fast.

First plan with index IX_Userdatas_Login_Password:

Second plan with index IX_Userdatas_Password_Login:

I use unique indexes with login and password columns in the first index and password and login in the second. In indexes, these columns are not in the included tab.

Who can prompt why so happens and how to execute the given request more quickly?

I've tried rebuilding indexes, resizing pages, resetting stats, and rebuilding indexes again, but the problem is still there

(btw: I cannot see your images as that site is blocked) Why are you using a "temp" table? Is this table indexed? if the query is ONLY the 2 tables shown above does the query use your expected index? — Paul Maxwell, Jan 09 '23 at 01:58
What is the compute scalar for? What are the datatypes? Is there a mismatch of nvarchar vs varchar between the tables? Are the tables partitioned? Can you upload the plan XML to https://www.brentozar.com/pastetheplan/? — Martin Smith, Jan 09 '23 at 02:00
I'm fine with images. This is a very long story, in short, I need to check in batches whether there are users or not. This table has no indexes other than Id. It is needed to quickly check multiple users — Sergey Neklyudov, Jan 09 '23 at 02:08
Your comments seem to have helped a lot, and indeed there was a data type mismatch. And because of this, there were problems. I'll check again and post what happens. — Sergey Neklyudov, Jan 09 '23 at 02:11
A side note: If you are storing passwords for your database as clear text or even as bidirectional encrypted values that can be compared, you have a severe security vulnerability. Best practice is at a minimum to store passwords as salted and hashed values that can be checked, but not compared and definitely not reversible. See [this article](https://cheatsheetseries.owasp.org/cheatsheets/Password_Storage_Cheat_Sheet.html). — T N, Jan 09 '23 at 02:18
Passwords are encrypted using the server's identity, so I don't worry about them, thanks for the note — Sergey Neklyudov, Jan 09 '23 at 02:21
So speculative answer is that `Login` is `nvarchar` in `UserdatasTemp` and `varchar` in `Userdatas` and that the compute scalar needs to call `GetRangeThroughConvert`. The range of rows read in the resulting seek can be much more than you would expect. [Especially if the values read from `UserdatasTemp` contains short strings or empty strings](https://stackoverflow.com/a/74682250/73226) — Martin Smith, Jan 09 '23 at 02:47
And you would only get any seek at all on the `Login` column with no seek on the `Password` component with this issue when the index on `(Login, Password)` was used — Martin Smith, Jan 09 '23 at 02:57
Friends, you were right, it's really about data types. Huge thanks to everyone :) — Sergey Neklyudov, Jan 09 '23 at 03:28
`GetRangeWithMismatchedTypes` then rather than `GetRangeThroughConvert` but rest is the same. By the way it seems strange to have a unique constraint on this combination of columns. So people can have the same login name but then get an error if they try and change their password to the same one that someone else sharing their login name has? — Martin Smith, Jan 09 '23 at 08:40

score 0 · Accepted Answer · answered Jan 09 '23 at 03:28

Many thanks to my friends for solving this problem. The problem itself was incorrect data types in the varchar(450) and varchar(MAX) columns. It is strange that I did not see this problem in the password and tried to find a problem with the index forgetting about different types

SQL Server 2022 : unique index with two columns not working correctly

1 Answers1