|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
length of text in row in tablewe are using a third party issue management system. On the back end it uses SQL 200 sp3. it has different tables for different types of documents (issue, sir, rick, etc...) and columns for properties on the documents (created by, assigned to, Description, etc...) i see an error getting logged when trying to put large amount of data in the document: ODBC SQL Server Driver][SQL Server]Cannot create a row of size 14127 which is greater than the allowable maximum of 8060. then looking at the MS site i see this note: This error message indicates that you have variable length columns in your table (such as nvarchar or varbinary) and that the total maximum length of all the columns adds up to more than 8060 bytes. You can still insert rows into the table provided that the total length of the data in each row does not exceed 8060 bytes. from SQL, i see that it does define the table columns as nvarchar. We cannot change this, since it is a third party tool. but, is this true you have a limit on the total string value in a row in a table? this seems crazy. > but, is this true you have a limit on the total string value in a row in a Yes.> table? > this seems crazy. Indeed. Well, a row needs to exist on one and only one page. A page is roughly 8k (8060 comes from other overhead). So, if you need to store more than 8k in a single row, you will need to store some of your data off-row. One way to do that is a 1:1 or 1:0-1 relationship, by adding a second table and storing large variable length column(s) there. Another is to use NTEXT, which only stores a pointer on the page. Or, for columns that actually store files, using an IMAGE column will have the same effect. A
Show quote
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message Another option is to upgrade to SQL Server 2005 after November 7.news:OIJ7a84vFHA.2312@TK2MSFTNGP14.phx.gbl... >> but, is this true you have a limit on the total string value in a row in >> a table? > > Yes. > >> this seems crazy. > > Indeed. Well, a row needs to exist on one and only one page. A page is > roughly 8k (8060 comes from other overhead). > > So, if you need to store more than 8k in a single row, you will need to > store some of your data off-row. One way to do that is a 1:1 or 1:0-1 > relationship, by adding a second table and storing large variable length > column(s) there. Another is to use NTEXT, which only stores a pointer on > the page. Or, for columns that actually store files, using an IMAGE > column will have the same effect. > This restriction is removed in SQL Server 2005, so if your vendor will support it, this problem will go away when you upgrade. eg create table tt(id int primary key, a varchar(8000), b varchar(8000)) insert into tt(id,a,b) values (1,replicate('a',8000), replicate('b',8000)) select id, datalength(a),datalength(b) from tt works on SQL 2005. David This is true. The total length data in a row is 8060. So one col could
have 4000, the second 4000, and ID (int) is 4, and that leaves 56 bytes left. If a column is a Text datatype then the column is (I believe) a pointer to where the text is at, and the text is not stored in the row itself. Also, Nvarchar takes 2 bytes of storage. This means that a 4000 character string would take 8000 bytes in the nvarchar column. what tool are you using? Each row is 1 page = 8K =8060 bytes and this is not crazy imagine your I/O if
you had 50K rows Try saving it into text fields those are stored separate, there is a pointer from the table to their location http://sqlservercode.blogspot.com/ Show quote "greg" wrote: > hello, > we are using a third party issue management system. On the back end it uses > SQL 200 sp3. > it has different tables for different types of documents (issue, sir, rick, > etc...) and columns for properties on the documents (created by, assigned > to, Description, etc...) > > i see an error getting logged when trying to put large amount of data in the > document: > ODBC SQL Server Driver][SQL Server]Cannot create a row of size 14127 which > is greater than the allowable maximum of 8060. > > then looking at the MS site i see this note: > This error message indicates that you have variable length columns in your > table (such as nvarchar or varbinary) and that the total maximum length of > all the columns adds up to more than 8060 bytes. You can still insert rows > into the table provided that the total length of the data in each row does > not exceed 8060 bytes. > > from SQL, i see that it does define the table columns as nvarchar. We > cannot change this, since it is a third party tool. > > but, is this true you have a limit on the total string value in a row in a > table? > this seems crazy. > > > thanks all. i guess this is right on the table limitation
Show quote "greg" <greg@nospam.com> wrote in message news:ek01z54vFHA.3556@TK2MSFTNGP12.phx.gbl... > hello, > we are using a third party issue management system. On the back end it > uses SQL 200 sp3. > it has different tables for different types of documents (issue, sir, > rick, etc...) and columns for properties on the documents (created by, > assigned to, Description, etc...) > > i see an error getting logged when trying to put large amount of data in > the document: > ODBC SQL Server Driver][SQL Server]Cannot create a row of size 14127 which > is greater than the allowable maximum of 8060. > > then looking at the MS site i see this note: > This error message indicates that you have variable length columns in your > table (such as nvarchar or varbinary) and that the total maximum length of > all the columns adds up to more than 8060 bytes. You can still insert rows > into the table provided that the total length of the data in each row does > not exceed 8060 bytes. > > from SQL, i see that it does define the table columns as nvarchar. We > cannot change this, since it is a third party tool. > > but, is this true you have a limit on the total string value in a row in a > table? > this seems crazy. > > On Thu, 22 Sep 2005 11:00:52 -0500, greg wrote:
>hello, Hi Greg,>we are using a third party issue management system. On the back end it uses >SQL 200 sp3. (snip) >but, is this true you have a limit on the total string value in a row in a >table? >this seems crazy. As others have said: it is true, and the reason for this limitation is related to how SQL Server stored it's data internally. However, the crazy part is that a third party apparently disregards all the warnings that SQL Server gives when you attempt to create a table with a row size that might exceed this maximum, AND doesn't take any precautions in it's front-end to keep you from exceeding this maximum. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address)  |
|||||||||||||||||||||||
Other interesting topics