I use one table to update another table. Both the tables have a clustered index where the first 4 columns are the same type of data.
CREATE UNIQUE CLUSTERED INDEX [i109139_I_109139CLI_44C9176D64C84ECB959C0EAA6AE7A4F4] ON [dbo].[t109139_44C9176D64C84ECB959C0EAA6AE7A4F4] ( [PARTITION] ASC, [DATAAREAID] ASC, [ITEMID] ASC, [INVENTSERIALID] ASC, [RECID] ASC ) GO ALTER TABLE [dbo].[INVENTSERIAL] ADD CONSTRAINT [I_1204ITEMSERIALIDX] PRIMARY KEY CLUSTERED ( [PARTITION] ASC, [DATAAREAID] ASC, [ITEMID] ASC, [INVENTSERIALID] ASC )
The following is the query I use to update the tables
UPDATE T1 SET NOTFORPROJECTS = T2.LANNOTFORPROJECTS FROM tempdb."DBO".t109139_44C9176D64C84ECB959C0EAA6AE7A4F4 T1 CROSS JOIN LANINVENTSERIALVIEW T2 WHERE T1.PARTITION = 5637144576 AND T1.DATAAREAID = N'lan' AND T2.PARTITION = 5637144576 AND T2.DATAAREAID = N'lan' AND T2.INVENTSERIALID = T1.INVENTSERIALID AND T2.ITEMID = T1.ITEMID AND T2.DATAAREAID = T1.DATAAREAID AND T2.PARTITION = T1.PARTITION
When I look at the queryplan, I notice that it does use the clustered indexes of both the tables, but it performs a hash match instead of a merge join. I find this unexpected as the used data should both be sorted on the equality operators.
So then I try to force a Merge join, and it seems that SQL Server still uses both the clustered indexes, but first sorts the data from the two tables from ItemId,InventSerialId to InventSerialId,ItemId and then uses that to perform a merge join. So it basically swaps the two columns.
Do note that in the table InventSerial, only 1 InventSerialId is an empty string, and in the other table that I update, there will be many empty-string (not null) InventSerialIds. I have no idea if this is relevant.