August 21, 2002 at 12:40 pm
I am trying to insert an extremely long text string into a SQL Server table. The column is the text data type (From what I have read the text data type can handle 2G of data). The insert SQL command below runs without error in Query Analyzer. However, the data gets truncated at 255 characters. At least I only get 255 characters when I select the column in Query Analyzer. So is the extremely long text string being truncated when it is inserted OR is the 255 limitation only when viewing the data? More importantly, how do I work around this problem ? Thank you.
Here is the SQL command:
Insert into TableName (TableColumn) VALUES ('11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111122222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222221111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222223')
August 21, 2002 at 5:11 pm
Change your settings in QA to display more than 255. And could you not post example strings that are so long? I agree it should wrap, just doesnt. Thanks.
Andy
August 22, 2002 at 2:07 am
I have had this problem, but I never found out why. My text, which was an email body, was also being truncated at 255 characters when assigned to a field which was of type Varchar which should have had a length of 4000 characters!
The solution to my problem was to use a field of type text.
If anyone else knows why this happened, I would love to know
August 22, 2002 at 2:08 am
I have had this problem, but I never found out why. My text, which was an email body, was also being truncated at 255 characters when assigned to a field which was of type Varchar which should have had a length of 4000 characters!
The solution to my problem was to use a field of type text.
If anyone else knows why this happened, I would love to know
August 22, 2002 at 2:08 am
I have had this problem, but I never found out why. My text, which was an email body, was also being truncated at 255 characters when assigned to a field which was of type Varchar which should have had a length of 4000 characters!
The solution to my problem was to use a field of type text.
If anyone else knows why this happened, I would love to know
August 22, 2002 at 8:04 am
Ah, so it is just a visual problem. Thanks Andy !!!
August 22, 2002 at 8:28 am
If you are using a text datatype, you need to use the readtext, writetext, and updatetext
commands to use the text field.
By default, SQL only stores the first 256 characters of a text datatype in the same page as the rest of the table data. The rest of the text data is stroed in seperate pages and must be accessed using the special commands above. (the default can be changed based on the "Text in row" table option.
What is happening is when you run a select statement, you are retrieveing the data in the table only (the first 256 characters) and TSQL does not know to retrieve data from the other pages
Chad Hattabaugh MCDBA-SE
Chad Hattabaugh MCDBA-SE
Chattabaugh@earthlink.net
August 22, 2002 at 2:04 pm
I do not believe this is correct. (above post).
In SQL 2000 you can specify whether text data is stored in the row, up to some size.
From Inside SQL Server:
Prior to v2000, SQL always stored a pointer in the data row. With 2000, you can use Text_in_row to alter this.
Steve Jones
August 23, 2002 at 5:00 am
Steve is correct. 7 stored the data always as a pointer on the table to a location elsewhere in the file. 2000 can store in the table from 24 to 7000 bytes (256 by default when just turned on) and once it is larger the data is moved and a poitner stored instead.
From SQL 2000 BOL
quote:
Text in Row DataNew Information - September 2001.
Microsoft® SQL Server™ 2000 supports the ability to store small to medium text, ntext, and image values in a data row. The feature is best used for tables in which the data in text, ntext, and image columns is usually read or written in one unit and most statements referencing the table use the text, ntext, and image data.
Unless the text in row option is specified, text, ntext, or image strings are large character or binary strings (up to 2 gigabytes) stored outside a data row. The data row contains only a 16-byte text pointer that points to the root node of a tree built of internal pointers that map the pages in which the string fragments are stored. For more information about the storage of text, ntext, or image strings, see text, ntext, and image Data.
You can set a text in row option for tables containing text, ntext, or image columns. You can also specify a text in row option limit, from 24 through 7,000 bytes. With this option set, text, ntext, or image strings are stored directly in the data row if:
The length of the string is shorter than the specified limit.
There is enough space available in the data row to hold the string.
When the text, ntext, or image string is stored in the data row, SQL Server does not have to access a separate page or set of pages to read or write the string. This makes reading and writing the text, ntext, or image in-row strings about as fast as reading or writing varchar, nvarchar, or varbinary strings.
If a text, ntext, or image string is longer than the text in row option limit or the available space in the row, the set of pointers that are otherwise stored in the root node of the pointer tree are stored in the row. The pointers are stored in the row if:
The amount of space needed to store the pointers is shorter than the specified text in row option limit.
There is enough space available in the data row to hold the pointers.
When pointers are moved from the root node to the row itself, SQL Server does not have to use a root node. This can eliminate a page access when reading or writing the string, which speeds processing.
When root nodes are used, they are stored as one of the string fragments in a text, ntext, or image page and can hold up to five internal pointers. SQL Server needs 72 bytes of space in the row to store five pointers for an in-row string. If there is not enough space in the row to hold the pointers when the text in row option is on, SQL Server may have to allocate an 8-K page to hold them. You should not set the text in row limit to less than 72 unless you are certain that all strings stored in the column are either short or over 3 MB.
When text, ntext, or image strings are stored in the row, they are stored similarly to variable-length strings. For example, if the text in row option limit is 500 bytes and you store a 200-byte string in a row, SQL Server uses only the number of bytes needed to store the string. If a string is inserted that is longer than 500 bytes, so that pointers are stored in the row, SQL Server uses only enough space to hold the pointers and not the entire 500 bytes.
If a table has multiple text, ntext, or image columns, and you attempt to insert multiple text, ntext, or image strings, SQL Server assigns space to the strings one at a time in sequence based on column ID. For example, assume you have a table containing four text columns and you have set the text in row option limit to 1000. You then insert a row with a 900-byte string for each text column, and enough data for all of the other columns in the table, leaving only 3,000 bytes of free space in the row to hold the text strings. The strings for the first three text columns are stored in the row, using 2,700 bytes of the 3,000 bytes available. The string for the fourth text column is not stored in the row, but the pointers from the root node are stored in the row.
Enabling and Disabling the text in row Option
You can enable the text in row option for a table by using sp_tableoption:
sp_tableoption N'MyTable', 'text in row', 'ON'
Optionally, you can specify a maximum limit, from 24 through 7,000 bytes, for the length of a text, ntext, and image string that can be stored in a data row:
sp_tableoption N'MyTable', 'text in row', '1000'
If you specify on instead of a specific limit, the limit defaults to 256 bytes. This default value allows you most of the performance benefits that can be gained from the text in row option. Although you generally should not set the value below 72, you also should not set the value too high, especially for tables in which most statements do not reference the text, ntext, and image columns, or in which there are multiple text, ntext, and image columns. If you set a large text in row limit, and many strings are stored in the row itself, you can significantly reduce the number of data rows that fit on each page. If most statements referencing the table do not access the text, ntext, or image columns, decreasing the rows in a page can increase the pages that must be read to process queries. Reducing the rows per page can increase the size of indexes and the pages that might need to be scanned if the optimizer finds no usable index. The text in row limit default value of 256 is large enough to ensure that small strings and the root text pointers can be stored in the rows, but not so large that it decreases the rows per page enough to affect performance.
You can also use sp_tableoption to turn the option off by specifying an option value of either off or 0:
sp_tableoption N'MyTable', 'text in row', 'OFF'
Effects of the text in row Option
The text in row option has these effects:
After you have turned on the text in row option, you can use the TEXTPRT, READTEXT, UPDATETEXT or WRITETEXT statements, to read or modify parts of any text, ntext, or image value stored in the table. In SELECT statements you can read an entire text, ntext, or image string, or use the SUBSTRING function to read parts of the string. All INSERT or UPDATE statements referencing the table must specify complete strings and cannot modify only a part of a text, ntext, or image string.
When the text in row option is first enabled, existing text, ntext, or image strings are not immediately converted to in-row strings. The strings are converted to in-row strings only if they are subsequently updated. Any text, ntext, or image string inserted after the text in row option is turned on is inserted as an in-row string.
Turning off the text in row option can be a long-running, logged operation. The table is locked and all in-row text, ntext, and image strings are converted to regular text, ntext, and image strings. The length of time the command must run and the amount of data modified depends on how many text, ntext, and image strings must be converted from in-row strings to regular strings.
The text in row option does not affect the operation of the OLE DB Provider for SQL Server or the SQL Server ODBC driver, other than to speed access to the text, ntext, and image data.
The DB-Library text and image functions, such as dbreadtext and dbwritetext, cannot be used on a table after the text in row option has been turned on.
The text in row option is set to 256 automatically for:
Variables with a table data type.
Tables returned by user-defined functions that return a table.
This setting cannot be changed.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply