BulkInsertAsync not using compound PK index #638
Description
Hello,
I catched the following command that matches a bulk insert operation on LOT table.
CREATE TABLE #ZZZProjects_512e2641_424b_4666_813f_744702acbed7z ( [$action] VARCHAR(100) NULL, ZZZ_Index INT NULL, [LOT_ID] [sys].[int] NULL );
MERGE INTO [LOT] AS DestinationTable
USING
(
SELECT TOP 100 PERCENT * FROM #ZZZProjects_512e2641_424b_4666_813f_744702acbed7
WHERE ZZZ_Index >= @IndexStart AND ZZZ_Index <= @IndexEnd ORDER BY ZZZ_Index
) AS StagingTable
ON 1 = 2
WHEN NOT MATCHED THEN
INSERT ( [SPE_CODE], [BUN_CODE], [... tons of columns ...] )
VALUES ( [SPE_CODE], [BUN_CODE], [... tons of columns ...] )
OUTPUT
$action,
StagingTable.ZZZ_Index,
INSERTED.[LOT_ID]
INTO #ZZZProjects_512e2641_424b_4666_813f_744702acbed7z
;
SELECT A.* ,B.[LOT_ID] AS [LOT_ID_zzzinserted] FROM #ZZZProjects_512e2641_424b_4666_813f_744702acbed7z AS A
INNER JOIN [LOT] AS B ON A.[LOT_ID] = B.[LOT_ID]
;
DROP TABLE #ZZZProjects_512e2641_424b_4666_813f_744702acbed7z;
Problem is that primary key is compound (SPE_CODE, BUN_CODE, LOT_ID) and, in INNER JOIN clause, the 2 leading columns((SPE_CODE, BUN_CODE) are not mentioned, so query is scanning the primary key index, instead of seeking properly as expected.
a) What am I supposed to do to make this Z-generated TSQL command reference the 2 missing columns in INNER JOIN clause ?
b) Why isn't there any clustered index on #ZZZProjects temporary table, as it is expected bulk operation, hence with high cardinality ?
Thanks,
Metadata
Metadata
Assignees
Labels
No labels