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)?

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

or simply replace the value:

UPDATE  MyTable
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)

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s