Hi there.
I am researching a solution using a TEXT datatype for data that will exceed 8,000 characters. I know that there is a limit to what can be returned in a simple SELECT, UPDATE and DELETE statement but BOL seems to contradict itself on the exact limit. Maybe I'm misinterpreting... hopefully someone can clarify.
From BOL:
"If an ntext, text, and image data value is no longer than a Unicode, character, or binary string (4,000 characters, 8,000 characters, 8,000 bytes respectively), the value can be referenced in SELECT, UPDATE, and INSERT statements much the same way as the smaller data types."
Also from BOL:
"You can retrieve ntext, text or image values by:
Simply referencing the column in a SELECT statement.
When this method is used in Transact-SQL scripts, stored procedures, and triggers, it works only for relatively short values. If the length of the data is longer than the length specified in SET TEXTSIZE, you must use increase TEXTSIZE or use another method. The current TEXTSIZE setting is reported by the @@TEXTSIZE function...The full amount of data is returned if the length is less than TEXTSIZE."
My current textsize setting is 64512, the text field I am working with contains 20,000 characters. Is it possible to return this much data without using READTEXT, WRITETEXT, etc?
Also - if I must use READTEXT, is it possible to return multiple columns and multiple records including the text column? For example, if my table has 3 fields, MyID int, MyName varchar(25) and MyDescription text, can I do something like this, only using READTEXT?
select MyID, MyName, MyDescription
from MyTable
where MyName like 'A%'
Thanks in advance.
I am researching a solution using a TEXT datatype for data that will exceed 8,000 characters. I know that there is a limit to what can be returned in a simple SELECT, UPDATE and DELETE statement but BOL seems to contradict itself on the exact limit. Maybe I'm misinterpreting... hopefully someone can clarify.
From BOL:
"If an ntext, text, and image data value is no longer than a Unicode, character, or binary string (4,000 characters, 8,000 characters, 8,000 bytes respectively), the value can be referenced in SELECT, UPDATE, and INSERT statements much the same way as the smaller data types."
Also from BOL:
"You can retrieve ntext, text or image values by:
Simply referencing the column in a SELECT statement.
When this method is used in Transact-SQL scripts, stored procedures, and triggers, it works only for relatively short values. If the length of the data is longer than the length specified in SET TEXTSIZE, you must use increase TEXTSIZE or use another method. The current TEXTSIZE setting is reported by the @@TEXTSIZE function...The full amount of data is returned if the length is less than TEXTSIZE."
My current textsize setting is 64512, the text field I am working with contains 20,000 characters. Is it possible to return this much data without using READTEXT, WRITETEXT, etc?
Also - if I must use READTEXT, is it possible to return multiple columns and multiple records including the text column? For example, if my table has 3 fields, MyID int, MyName varchar(25) and MyDescription text, can I do something like this, only using READTEXT?
select MyID, MyName, MyDescription
from MyTable
where MyName like 'A%'
Thanks in advance.
