can we use option (recompile) with update statment in sql server -

declare @tbl table (id int , name varchar(100))  insert @tbl(id,name) value(1,'ravi')  update @tbl  set name = 'shyam' id = 1 

here question if have 1 million records in table , want update of those, if use option (recompile) update optimized query or not. if not best?.

can use option (recompile) update statment in sql server

yes,you can use option(recompile) update statement...

here question if have 1 million records in table , want update of those, if use option (recompile) update optimized query or not. if not best?.

option(recompile) not optimize statements,it instructs sql server construct new plan , dont store in cache..

if running lot of queries same predicates, shown in question , if data had increased lot ,this results in inaccurate statistics.so option(recompile) instructs sql use new plan based on current data , instructs not store in cache...

what other options ?

this query believe sample 1 , said having million records..

update @tbl  set name = 'shyam' id = 1 

the above query efficiency depends on
1.number of indexes...more indexes less efficiency
2.locking,blocking on system
, many factors ram,is table memory or whether sql should read disk..