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 が非推奨となったため、使用しない方が良い