How to use index hint at Update statement

1

How i can use the hint WITH (index = PK_Index_LogID) at the following update statement:
WHILE 1=1
UPDATE dbo.Table
SET columnA = getdate(),
columnB = getdate(),
columnC = ISNULL(1, 322)
WHERE columnD= 123 AND columnB IS NULL

2 

UPDATE T
SET columnA = getdate(),
columnB = getdate(),
columnC = ISNULL(1, 322)
FROM dbo.[Table] T WITH (INDEX=PK_Index_LogID)
WHERE columnD= 123 AND columnB IS NULL
1 Like
3

To use the hint WITH (INDEX = PK_Index_LogID) in the UPDATE statement, you can specify it after the target table in the following manner:

sqlCopy code

WHILE 1=1
UPDATE dbo.Table WITH (INDEX = PK_Index_LogID)
SET columnA = getdate(),
    columnB = getdate(),
    columnC = ISNULL(1, 322)
WHERE columnD = 123 AND columnB IS NULL

By adding WITH (INDEX = PK_Index_LogID) after the table name, you are instructing the SQL Server to use the index PK_Index_LogID when searching for rows to update. This hint can be helpful in optimizing the UPDATE statement's performance if there is an appropriate index that can speed up the search and retrieval process. Make sure the index PK_Index_LogID is indeed a suitable index for the WHERE clause conditions in the UPDATE statement.