4

What are the semantics of the following query

UPDATE table
SET column .WRITE(NULL, NULL, NULL)

if column is of VARBINARY(max) type and its contents are not NULL?

A quick test suggests that the query is a no-op:

--DROP TABLE [table]
CREATE TABLE [table] ([column] VARBINARY(max))
INSERT INTO [table] VALUES (0x12345678)
UPDATE [table]
SET [column] .WRITE(NULL, NULL, NULL)
SELECT * FROM [table]

Executing it does not alter the data in the column. However, I can't seem to find evidence in the documentation. Have I overlooked something, or is this no-op behavior likely to change?

krlmlr
  • 25,056
  • 14
  • 120
  • 217
  • What does "a no-op" mean for you? Your syntax is incorrect, as the documentation you linked to shows (see the examples under "Updating Large Object Data Types"). This works for me: `update dbo.Mytable set MyColumn.Write(null, null, null)` – Pondlife May 20 '13 at 15:04
  • @Pondlife: Thanks, edited. It works for me too, but is this behavior documented? – krlmlr May 20 '13 at 16:00
  • I don't know what you're asking: what is "this behavior"? The parameters are documented, and the remarks say that you cannot set a value to NULL or update a NULL column value using `write`. It sounds like you're seeing something that you don't expect or understand, but since you haven't shown any query results or output I'm not sure what anyone can tell you. If you post a complete working example that shows exactly the behaviour that you're asking about, someone may be able to clarify it. – Pondlife May 20 '13 at 16:12
  • @krlmlr: Actually the doc that you linked to logically implies that `.WRITE(NULL,NULL,NULL)` will try to truncate the column to the length that it already is. I.E., effectively a no-op. – RBarryYoung May 29 '13 at 14:45
  • @RBarryYoung: Care to elaborate on this in an answer? I'll be glad to award a bounty for a logical explanation :-) – krlmlr May 29 '13 at 17:57

1 Answers1

8

Actually a careful reading of the doc that was linked to (here) logically implies that .WRITE(NULL,NULL,NULL) should try to truncate the column to the length that it already is. I.E., effectively a no-op.

Note the italicized sections in this extract from the Doc:

.WRITE (expression,@Offset,@Length):

Specifies that a section of the value of column_name is to be modified. expression replaces @Length units starting from @Offset of column_name. ...

expression: is the value that is copied to column_name. ... If expression is set to NULL, @Length is ignored, and the value in column_name is truncated at the specified @Offset.

@Offset: is the starting point in the value of column_name at which expression is written. ... If @Offset is NULL, the update operation appends expression at the end of the existing column_name value and @Length is ignored. ...

So if @Offset is NULL, then it is treated as though it were the current length of the column.

And, if expression is NULL, the column is truncated at the @Offset value.

Ergo, if both are NULL, then the column is truncated at its current length. Which I take to mean that it effectively does nothing.

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
  • So, `@Length` is ignored because `expression` is `NULL`, *and* because `@Offset` is `NULL`. So indeed the observed behavior is the only logical explanation. I'm just wondering why this isn't stated explicitly... Thanks for the careful analysis! – krlmlr May 29 '13 at 19:44