Using .WRITE on VARCHAR(MAX)? – Part 1

Are you using .WRITE when updating the contents of a VARCHAR(MAX) or NVARCHAR(MAX) column and are you replacing the whole contents (using @Offset = 0 and @Length = NULL)?

SET     col1.WRITE (‘Lorem ipsum’, 0, NULL)

or simply replace the value:

SET     col1 = ‘Lorem ipsum’

Depending on your app, you may want to try using a classic update rather than .WRITE. Although .WRITE can provide significant performance enhancements, if you are not appending data or changing a subset of the MAX column, then I’ve found a classic update to be faster. Marginally! Note the query plan for a .WRITE when updating a single column via a PRIMARY KEY is more complex (I’m still getting to the bottom of this)


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s