MS SQL Server 2008 - UPDATE a large database

Question

I'd like to UPDATE just one data in a large TABLE What would be the most efficient way to do this?

  SELECT * from TABLE WHERE status='N'
  UPDATE TABLE set status='Y' where status='N'

Answer 1

I assume table is very very large. Then may be you should create temp/permanent Filtered index on table

CREATE NONCLUSTERED INDEX Temp_Table_Status
ON dbname.dbo.Table(Status)
WHERE Status='N'
GO

else your query is correct.

UPDATE TABLE set status='Y' where status='N'

Answer 2

"Most efficient" is much like "most beautiful". It has no absolute meaning. How do you measure "efficient". IMO, by far the most efficient mechanism is to use a single update query. Your query should actually be written to avoid pointless updates:

update table set col = 'Y' where col <> 'Y';

The where clause will make it "most efficient". And note that you might need to account for null values in the where clause - know your data. Some might argue for batching the updates in order to save space. If you do this on a regular basis, then you should have generally have sufficient space in the database and log to do this without attempting to pointlessly manage bits of diskspace.