SQL Server 2017 Enterprise - how to fast update entire columns I need to update all values in one column in a table. The table is large - nearly 420 000 000 rows.  Is there any clever way to conduct this operation quickly and without causing a lot of transaction log growth?
I know I can do that in batches, but maybe some sort of metadata trick is possible? Some clever way to use ALTER TABLE SWITCH maybe?
TIA
maybe some sort of metadata trick is possible?


Adding a new column with a default can be a metadata-only operation.
Dropping a column can be a metadata-only operation.
Renaming a column can be a metadata-only operation.

I'm not certian that all of these always are metadata-only for every type of table, but  try something like:
  use AdventureWorksDW2017
  go
  drop table if exists DimCustomer2 
  go
  select c.*
  into DimCustomer2
  from DimCustomer c
  cross join (select top 100 * from sys.messages) d
  go
  set statistics io on
  go
  begin transaction
  alter table DimCustomer2 add LastName2 nvarchar(200) not null default 'HIDDEN'
  alter table DimCustomer2 drop column LastName
  exec sp_rename 'Dbo.DimCustomer2.LastName2', 'LastName', 'COLUMN'
  commit
  go
  set statistics io off
  go
  select top 100 * 
  from DimCustomer2