Is the maximum row size per table really limited to
8K bytes? Or is this the internal organization of SQL
data structures. I have imported a legacy table that
wound up having over 100 columns that came in as
NVARCHAR(255) or roughly 25K + in size. Any comments?
ThanksIt will allow you to create the object with the warning something as
The table % has been created but its maximum row size (%) exceeds the
maximum number of bytes per row (8060)
Ex:
create table test_size
(i1 nvarchar(255),
i2 nvarchar(255),
i3 nvarchar(255),
i4 nvarchar(255),
i6 nvarchar(255),
i8 nvarchar(255),
i9 nvarchar(255),
i10 nvarchar(255),
i11 nvarchar(255),
i12 nvarchar(255),
i14 nvarchar(255),
i19 nvarchar(255),
i20 nvarchar(255),
i21 nvarchar(255),
i22 nvarchar(255),
i23 nvarchar(255),
i24 nvarchar(255),
i30 nvarchar(255),
i31 nvarchar(255),
i32 nvarchar(255),
i33 nvarchar(255))
This is because you are using nvarchar datatype which is variable length
data type. Hence it will give you
error at runtime if actual data that you are going to insert goes beyond
8060 bytes. Also point to be noted
that nvarchar table 2 bytes to store a single character.
So SQL server is happy to create a table though a table crosses limit of
8060 bytes per row so far you use
variable data type like varchar/nvarchar but it will throw you an error
while inserting/updating data if actual
row size that a row will occupy goes beyond this limit.
--
-Vishal
"wringland" <bill@.tiati.com> wrote in message
news:001001c34fa3$3447a540$a401280a@.phx.gbl...
> Is the maximum row size per table really limited to
> 8K bytes? Or is this the internal organization of SQL
> data structures. I have imported a legacy table that
> wound up having over 100 columns that came in as
> NVARCHAR(255) or roughly 25K + in size. Any comments?
> Thanks|||And Dinesh is referring to the "hard" limit of the row size. When you have
varchar columns, you need to figure in the "soft" factor: the actual limit
is applied to the actual size of content. If you have 100 nvarchar(255)
columns and each column has only 1 character in it, the row size would be
(100 (columns) x 1 (character each column) x 2 (for Nvarchar) + 9 (row
overhead) ) = 209 bytes.
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:OsEUMR6TDHA.3700@.tk2msftngp13.phx.gbl...
> Bill,
> The maximum row size is 8060 bytes except for blob data types as TEXT ,
> IMAGE etc.That means your 25K table wont fit in.Moreover, you are using
the
> unicode datatype, NVARCHAR, which itself take twice as much storage space
as
> non-unicode data types.
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "wringland" <bill@.tiati.com> wrote in message
> news:001001c34fa3$3447a540$a401280a@.phx.gbl...
> > Is the maximum row size per table really limited to
> > 8K bytes? Or is this the internal organization of SQL
> > data structures. I have imported a legacy table that
> > wound up having over 100 columns that came in as
> > NVARCHAR(255) or roughly 25K + in size. Any comments?
> >
> > Thanks
>|||Thanks for the reply. Can you perhaps explain why the
table was imported into SQL and the table design show the
proper format and size for the columns. All does seem to
be well with the data.
Thanks
>--Original Message--
>Bill,
>The maximum row size is 8060 bytes except for blob data
types as TEXT ,
>IMAGE etc.That means your 25K table wont fit in.Moreover,
you are using the
>unicode datatype, NVARCHAR, which itself take twice as
much storage space as
>non-unicode data types.
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"wringland" <bill@.tiati.com> wrote in message
>news:001001c34fa3$3447a540$a401280a@.phx.gbl...
>> Is the maximum row size per table really limited to
>> 8K bytes? Or is this the internal organization of SQL
>> data structures. I have imported a legacy table that
>> wound up having over 100 columns that came in as
>> NVARCHAR(255) or roughly 25K + in size. Any comments?
>> Thanks
>
>.
>|||> Thanks for the reply. Can you perhaps explain why the
> table was imported into SQL and the table design show the
> proper format and size for the columns. All does seem to
> be well with the data.
Because the data is not "full" - you will only get an error if you attempt
to put more than 8,060 bytes of *actual data* into the table. I am guessing
that many of your nvarchar(255) columns are NULL or blank.|||Bill,
As mentioned in other posts, SQL server will let you create the table with a
warning about data truncation.While inserting data, the moment a particular
rowsize exceeds 8060, the rest would be truncated and you wouldnt even
know.Means while querying the table, you may be looking at incomplete and
thus false data.
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"wringland" <bill@.tiati.com> wrote in message
news:009201c34fa7$6db8dbb0$a401280a@.phx.gbl...
> Thanks for the reply. Can you perhaps explain why the
> table was imported into SQL and the table design show the
> proper format and size for the columns. All does seem to
> be well with the data.
> Thanks
> >--Original Message--
> >Bill,
> >
> >The maximum row size is 8060 bytes except for blob data
> types as TEXT ,
> >IMAGE etc.That means your 25K table wont fit in.Moreover,
> you are using the
> >unicode datatype, NVARCHAR, which itself take twice as
> much storage space as
> >non-unicode data types.
> >
> >--
> >Dinesh.
> >SQL Server FAQ at
> >http://www.tkdinesh.com
> >
> >"wringland" <bill@.tiati.com> wrote in message
> >news:001001c34fa3$3447a540$a401280a@.phx.gbl...
> >> Is the maximum row size per table really limited to
> >> 8K bytes? Or is this the internal organization of SQL
> >> data structures. I have imported a legacy table that
> >> wound up having over 100 columns that came in as
> >> NVARCHAR(255) or roughly 25K + in size. Any comments?
> >>
> >> Thanks
> >
> >
> >.
> >|||> As mentioned in other posts, SQL server will let you create the table with
a
> warning about data truncation.While inserting data, the moment a
particular
> rowsize exceeds 8060, the rest would be truncated and you wouldnt even
> know.
Actually in this case, wouldn't you get the "string or binary data would be
truncated" error message?|||I have five fields each varchar(5000) and about
8 other columns between 50 and 100 bytes each.
so in answer to your question, none is > 8060.
>--Original Message--
>What is your current table structure? Are there one or
two columns forcing
>it beyond 8,060, or do you have 100 columns that are 255
characters?
>
>
>"Alice" <mygth@.hotmail.com> wrote in message
>news:056401c34fce$de321fb0$a101280a@.phx.gbl...
>> I am running into similar situation,
>> I am creating new table with rowsize > 8060,
>> my rowsize is 25000, is there any way by which
>> I can increase the rowsize to this limit?
>> I read on microsoft site that this limiation existed
>> on 6.5 and u.s. pack 2 fixed it. Any ideas?
>> BTW, I am on Sql 7.
>
>.
>|||Alice,
>> Then why do they say US patch 4 fixes it for sql 6.5?
What article are you talking about?
Thanks!
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Alice" <mygth@.hotmail.com> wrote in message
news:04a101c34fda$1dcc50e0$a401280a@.phx.gbl...
> well, I was thinking on those lines, but I am surprised
> that we cannot have > 8060 characters in a row.
> Then why do they say US patch 4 fixes it for sql 6.5?
> unless I am mistaken.
> >--Original Message--
> >> I have five fields each varchar(5000) and about
> >> 8 other columns between 50 and 100 bytes each.
> >>
> >> so in answer to your question, none is > 8060.
> >
> >If you expect to ever fill more than 8,060 characters
> across the row, then
> >you're going to have to make a decision. You could use
> text columns, or you
> >could put each varchar(5000) in its own table with a
> foreign key to this
> >tables pk. Are you expecting to use all 5000 characters
> in any row/column
> >combination? If not, you might consider decreasing the
> size to reduce the
> >number of columns you'll have to store off-row.
> >
> >
> >.
> >
