Optimising a sql correlated subquery statement I am updating a column on tableA with data from tableB like this:

update tableA
  set bKey = select bKey from tableB B where B.colB = tableA.colB


bKey has a unique index on tableB.

Which is the better optimisation for this query, indexing tableB on ColB or (ColB,bKey) and why?

I am interested in answers for any database.
Neither.

Index ColB INCLUDE (bKey)

Since you aren't filtering on bKey you don't need it at all the nodes of the index, just the leaf level.

If you only index on ColB then you will still have to pay for a key lookup to get the value of bKey from the clustered index.

Also is there a reason this isn't using a JOIN instead of a correlated subquery?

UPDATE a
SET a.bKey = b.bKey
FROM TableA A
INNER JOIN TableB B
ON A.ColB = B.ColB