UPDATE で使用する .WRITE の紹介

SQL Server 2005 で追加されています。
SQL Server 2005 で追加された大きいデータ型( varchar(max), nvarchar(max), varbinary(max) ) の部分更新を行うために、UPDATE で .WRITE が使えるようになりました。
UPDATE (Transact-SQL)
SQL Server 2000 での text, ntext, image に対する UPDATETEXT の替わりの手段だと思います。*1
UPDATETEXT


早速クエリを見ながら解説していきます。

use [テスト]
-- 準備
create table [論文] ( 
  [Id] int not null Identity(1,1) primary key, 
  [内容] nvarchar(max) not null 
)
go
with [cte] ( [content] ) as ( 
  select cast('abcdefghijklmnopqrstuvwxyz' as nvarchar(max)) as [content] 
  union all 
  select [cte].[content] + [cte].[content] + [cte].[content] + [cte].[content] + [cte].[content] + [cte].[content] 
  from [cte] where len([cte].[content]) <= 12000
)
insert into [論文] output inserted.*, len(inserted.[内容]) select * from [cte] option (maxrecursion 0)
go


テーブルを作成して、テストデータを準備します。

-- WRITE
begin tran
update [論文] set 
  [内容].write('ABCDEFGHIGKLMNOPQRSTUVWXYZABCDEFGHIGKLMNOPQRSTUVWXYZ', 26, 52)
output 
  deleted.*, len(deleted.[内容]) as [更新前_長さ], 
  inserted.*, len(inserted.[内容]) as [更新後_長さ]
rollback


[内容] 列の値の 27文字目から長さ52文字分を'ABCD...'と大文字に置き換えています。
1行目のデータは、26文字しかないので、27文字目以降は追加扱いになります。

-- 長さを0 にすると...
begin tran
update [論文] set 
  [内容].write('ABCDEFGHIGKLMNOPQRSTUVWXYZABCDEFGHIGKLMNOPQRSTUVWXYZ', 26, 0)
output 
  deleted.*, len(deleted.[内容]) as [更新前_長さ], 
  inserted.*, len(inserted.[内容]) as [更新後_長さ]
rollback


置き換える長さを 0 にすると、元の文字列に挿入する動きになります。さきほどの結果とは「更新後_長さ」の値が変わっています。


大きいデータ型以外で同様の更新を行おうとすると STUFF (Transact-SQL) を使います。
STUFF は SQL Server 2000 でも使用出来る文字列関数です。MSDN SQL Server 2000 STUFF

-- STUFF を使用した場合
begin tran
update [論文] set [内容] = case when len([内容]) <= 26 
  then [内容] + 'ABCDEFGHIGKLMNOPQRSTUVWXYZABCDEFGHIGKLMNOPQRSTUVWXYZ' 
  else STUFF([内容], 27, 52, 'ABCDEFGHIGKLMNOPQRSTUVWXYZABCDEFGHIGKLMNOPQRSTUVWXYZ') 
end 
output 
  deleted.*, len(deleted.[内容]) as [更新前_長さ], 
  inserted.*, len(inserted.[内容]) as [更新後_長さ]
rollback
-- STUFF で挿入する場合
begin tran
update [論文] set [内容] = case when len([内容]) <= 26 
  then [内容] + 'ABCDEFGHIGKLMNOPQRSTUVWXYZABCDEFGHIGKLMNOPQRSTUVWXYZ' 
  else STUFF([内容], 27, 0, 'ABCDEFGHIGKLMNOPQRSTUVWXYZABCDEFGHIGKLMNOPQRSTUVWXYZ') 
end 
output 
  deleted.*, len(deleted.[内容]) as [更新前_長さ], 
  inserted.*, len(inserted.[内容]) as [更新後_長さ]
rollback


ここでは、大きいデータ型( nvarchar(max) )に対して、STUFF を使用していますが、varchar(n), nvarchar(n), varbinary(n) に対しても使用出来ます。


では何故 .WRITE を使うのかというと、データベースの復旧モデルによっては最小限のログしか記録しません。
最小ログ記録が可能な操作
このため STUFF を使うよりは、パフォーマンスやログサイズの肥大に対して有利になるのではと思います。

クエリ全文

use [テスト]
-- 準備
create table [論文] ( 
  [Id] int not null Identity(1,1) primary key, 
  [内容] nvarchar(max) not null 
)
go
with [cte] ( [content] ) as ( 
  select cast('abcdefghijklmnopqrstuvwxyz' as nvarchar(max)) as [content] 
  union all 
  select [cte].[content] + [cte].[content] + [cte].[content] + [cte].[content] + [cte].[content] + [cte].[content] 
  from [cte] where len([cte].[content]) <= 12000
)
insert into [論文] output inserted.*, len(inserted.[内容]) select * from [cte] option (maxrecursion 0)
go
-- WRITE
begin tran
update [論文] set 
  [内容].write('ABCDEFGHIGKLMNOPQRSTUVWXYZABCDEFGHIGKLMNOPQRSTUVWXYZ', 26, 52)
output 
  deleted.*, len(deleted.[内容]) as [更新前_長さ], 
  inserted.*, len(inserted.[内容]) as [更新後_長さ]
rollback
-- 長さを短くすると...
begin tran
update [論文] set 
  [内容].write('ABCDEFGHIGKLMNOPQRSTUVWXYZABCDEFGHIGKLMNOPQRSTUVWXYZ', 26, 0)
output 
  deleted.*, len(deleted.[内容]) as [更新前_長さ], 
  inserted.*, len(inserted.[内容]) as [更新後_長さ]
rollback
-- STUFF を使用した場合
begin tran
update [論文] set [内容] = case when len([内容]) <= 26 
  then [内容] + 'ABCDEFGHIGKLMNOPQRSTUVWXYZABCDEFGHIGKLMNOPQRSTUVWXYZ' 
  else STUFF([内容], 27, 52, 'ABCDEFGHIGKLMNOPQRSTUVWXYZABCDEFGHIGKLMNOPQRSTUVWXYZ') 
end 
output 
  deleted.*, len(deleted.[内容]) as [更新前_長さ], 
  inserted.*, len(inserted.[内容]) as [更新後_長さ]
rollback
-- STUFF で挿入する場合
begin tran
update [論文] set [内容] = case when len([内容]) <= 26 
  then [内容] + 'ABCDEFGHIGKLMNOPQRSTUVWXYZABCDEFGHIGKLMNOPQRSTUVWXYZ' 
  else STUFF([内容], 27, 0, 'ABCDEFGHIGKLMNOPQRSTUVWXYZABCDEFGHIGKLMNOPQRSTUVWXYZ') 
end 
output 
  deleted.*, len(deleted.[内容]) as [更新前_長さ], 
  inserted.*, len(inserted.[内容]) as [更新後_長さ]
rollback
-- 後片付け
drop table [論文]
go

*1:UPDATETEXT は 2005 でも存在しますが、text, ntext, image が非推奨となったため、使用しない方が良い