Improving Text/Image Type Performance

If you know that you are going to be using a text/insert column immediately, insert the row setting the column to a non-null value.

There's a noticeable performance gain.

Unfortunately, text and image datatypes cannot be passed as parameters to stored procedures. The address of the text or image location must be created and returned where it is then manipulated by the calling code. This means that transactions involving both text and image fields and stored procedures are not atomic. However, the datatypes can still be declared as not null in the table definition.

Given this example -

	create table key_n_text
	(
	    key 	int	not null,
	    notes	text	not null
	)

This stored procedure can be used -

	create procedure sp_insert_key_n_text
	    @key	int,
	    @textptr	varbinary(16)	output
	as

	/*
	** Generate a valid text pointer for WRITETEXT by inserting an
	** empty string in the text field.
	*/
	insert key_n_text
	(
	    key,
	    notes
	)
	values
	(
	    @key,
	    ""
	)

	select  @textptr = textptr(notes)
	from    key_n_text
	where   key	 = @key

	return 0
	go

The return parameter is then used by the calling code to update the text field, via the dbwritetext() function if using DB-Library for example.