indexing - Update bigdata table in SQL Server -

i need update 1 table column table, here situation.

• hardware: 8gb ram, xeon processor, 1 tb internal harddisk
• software: sql server 2014

i have 2 sql server database , each has table.

1. table1 (db1) 275mn+ row count, 350 columns, unique column = id (18 char in each id), updated columns = u_col1, u_col2

2. table2 (db2) 230mn+ row count, 8 columns, unique column = id (18 char in each id), set columns = _col1, _col2

here query update:

update db1.table1 set t1.u_col1 = t2._col1, t1.u_col2 = t2._col2 db1.table1 t1, db2.table2 t2 t1.id = t2.id 

i had not added indexes because of performance issues.

so suggest how run columns updated.

give me answers of questions.

1. can run update without using indexes?
2. suggest index use on column(s) in both tables (clustered, non-clustered, clustered column store)
3. or other index or update trick

i ok if query runs 24 hours, 36 hours or may more want complete query successfully.

let me know if want know else?

can run update without using indexes?

yes, performance may prohibitive updating 200m+ rows in single statement 8gb ram, single spinning disk, , no supporting indexes.

more importantly, might run log space issues updating many rows in single batch. safe, best make sure database recovery model simple , perform updates in batches mitigate log space requirements.

suggest index use on column(s) in both tables (clustered, non-clustered, clustered column store)

unique clustered index on id column of each table.

or other index or update trick

specify tablockx hint.

here's update hints using ansi-92 join syntax.

update t1 set       t1.u_col1 = t2._col1     , t1.u_col2 = t2._col2 dbo.table1 t1 with(tablockx) join dbo.table2 t2 with(tablockx) on t1.id = t2.id; 

add where clause update in smaller batches clustered index key in loop. don't know format of char(18) id data can't more specific how determine key ranges. goal update no more few gb of data per batch. updates faster when data updated in single transaction fit in memory.

update t1 set       t1.u_col1 = t2._col1     , t1.u_col2 = t2._col2 dbo.table1 t1 with(tablockx) join dbo.table2 t2 with(tablockx) on t1.id = t2.id t1.id between @startkey , @endkey;