0

I have an old data table in SQL Server 2017 containing a VARBINARY(max) column, say [doc].[final], the size of the value could be as large as 500MB. Now I want to create the VARBINARY(max) value in a different table first, say [doc_prepare].[content], and then update the [doc].[final] value using a UPDATE statement. My question is, for VARBINARY(max) in SQL Server, does the full content of a VARBINARY(max) get copied over, or just a link/reference to the storage of the binary value? If the former, I'll probably add a new column in [doc] table linking to the [doc_prepare] table so I don't want this UPDATE statement locks the table or slows down the performance. But if the latter, I guess the UPDATE should be fine?

The query is just something like this -

UPDATE A 
SET [final] = M.[content], [date] = M.finished
FROM [doc_prepare] M 
INNER JOIN [doc] A on m.user_id = A.id;

Thanks!

D.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Diesel S
  • 1
  • 1
  • The data would be copied. As it is a `MAX` value, then it would be stored outside of the table itself, but it would still be duplicated. As such, you are better off using a foreign key relationship to your existing data unless you are happy with significant database growth is you need to do this with a lot of large size values. – Thom A Aug 21 '20 at 15:34
  • Thanks Larnu - makes sense, if just the reference gets copied and the value of the other column is updated, still have to make a copy of the data. – Diesel S Aug 21 '20 at 15:52

0 Answers0