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?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With