Maximum Row Size in SQL Server 2000

Using this code:
CREATE TABLE [dbo].[test] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[COMMENT1] [varchar] (8000) NULL ,
[COMMENT2] [varchar] (8000) NULL
) ON [PRIMARY]
GO
I get the error: Warning: The table 'test' has been created but its maximum
row size (16029) exceeds the maximum number of bytes per row (8060).
However, I can create a table with smaller field lengths:
CREATE TABLE [dbo].[test] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[COMMENT1] [varchar] (10) NULL ,
[COMMENT2] [varchar] (10) NULL
) ON [PRIMARY]
GO
I can then alter the field lengths in Enterprise Manager back to 8000 and
not get the error. I also notice I can import data from a text file and a
table will be created with numerous varchar fields that are each 8000 in
size. Why cannot I create the table with multiple 8000 length fields but SQL
Server allows me to modify an existing table or import into a table that has
multiple 8000 length fields?
Thank you.Hi,
like you said, you get a "Warning" no error. SQL Server just wants to
keep you informed that the data *might* be truncated, if you insert
more than 8000 characters, but the table *will* be created.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||The warning you get is only a warning. The table is still created. Read the warning text carefully.
The table is created, but if you , for a row, try to have > 8060 bytes (when you do INSERT or
UPDATE), then that operation will fail.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brian P" <BrianP@.discussions.microsoft.com> wrote in message
news:7F71DB7B-6F5E-4ABC-90AA-A988691D095A@.microsoft.com...
> Using this code:
> CREATE TABLE [dbo].[test] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [COMMENT1] [varchar] (8000) NULL ,
> [COMMENT2] [varchar] (8000) NULL
> ) ON [PRIMARY]
> GO
> I get the error: Warning: The table 'test' has been created but its maximum
> row size (16029) exceeds the maximum number of bytes per row (8060).
> However, I can create a table with smaller field lengths:
> CREATE TABLE [dbo].[test] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [COMMENT1] [varchar] (10) NULL ,
> [COMMENT2] [varchar] (10) NULL
> ) ON [PRIMARY]
> GO
> I can then alter the field lengths in Enterprise Manager back to 8000 and
> not get the error. I also notice I can import data from a text file and a
> table will be created with numerous varchar fields that are each 8000 in
> size. Why cannot I create the table with multiple 8000 length fields but SQL
> Server allows me to modify an existing table or import into a table that has
> multiple 8000 length fields?
> Thank you.
>
>
>|||Thank you for the information. So I can have a table with numerous varchar
8000 fields and I'm ok as long as a single inserted row does not contain more
than 8060 characters. Is this correct?
"Tibor Karaszi" wrote:
> The warning you get is only a warning. The table is still created. Read the warning text carefully.
> The table is created, but if you , for a row, try to have > 8060 bytes (when you do INSERT or
> UPDATE), then that operation will fail.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brian P" <BrianP@.discussions.microsoft.com> wrote in message
> news:7F71DB7B-6F5E-4ABC-90AA-A988691D095A@.microsoft.com...
> > Using this code:
> >
> > CREATE TABLE [dbo].[test] (
> > [ID] [int] IDENTITY (1, 1) NOT NULL ,
> > [COMMENT1] [varchar] (8000) NULL ,
> > [COMMENT2] [varchar] (8000) NULL
> > ) ON [PRIMARY]
> > GO
> >
> > I get the error: Warning: The table 'test' has been created but its maximum
> > row size (16029) exceeds the maximum number of bytes per row (8060).
> >
> > However, I can create a table with smaller field lengths:
> >
> > CREATE TABLE [dbo].[test] (
> > [ID] [int] IDENTITY (1, 1) NOT NULL ,
> > [COMMENT1] [varchar] (10) NULL ,
> > [COMMENT2] [varchar] (10) NULL
> > ) ON [PRIMARY]
> > GO
> >
> > I can then alter the field lengths in Enterprise Manager back to 8000 and
> > not get the error. I also notice I can import data from a text file and a
> > table will be created with numerous varchar fields that are each 8000 in
> > size. Why cannot I create the table with multiple 8000 length fields but SQL
> > Server allows me to modify an existing table or import into a table that has
> > multiple 8000 length fields?
> >
> > Thank you.
> >
> >
> >
> >
> >
> >
>
>|||Try it :
INSERT INTO TEST VALUES (REPLICATE('*', 80000), REPLICATE('*', 44))
A +
Brian P a écrit :
> Thank you for the information. So I can have a table with numerous varchar
> 8000 fields and I'm ok as long as a single inserted row does not contain more
> than 8060 characters. Is this correct?
> "Tibor Karaszi" wrote:
>> The warning you get is only a warning. The table is still created. Read the warning text carefully.
>> The table is created, but if you , for a row, try to have > 8060 bytes (when you do INSERT or
>> UPDATE), then that operation will fail.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Brian P" <BrianP@.discussions.microsoft.com> wrote in message
>> news:7F71DB7B-6F5E-4ABC-90AA-A988691D095A@.microsoft.com...
>> Using this code:
>> CREATE TABLE [dbo].[test] (
>> [ID] [int] IDENTITY (1, 1) NOT NULL ,
>> [COMMENT1] [varchar] (8000) NULL ,
>> [COMMENT2] [varchar] (8000) NULL
>> ) ON [PRIMARY]
>> GO
>> I get the error: Warning: The table 'test' has been created but its maximum
>> row size (16029) exceeds the maximum number of bytes per row (8060).
>> However, I can create a table with smaller field lengths:
>> CREATE TABLE [dbo].[test] (
>> [ID] [int] IDENTITY (1, 1) NOT NULL ,
>> [COMMENT1] [varchar] (10) NULL ,
>> [COMMENT2] [varchar] (10) NULL
>> ) ON [PRIMARY]
>> GO
>> I can then alter the field lengths in Enterprise Manager back to 8000 and
>> not get the error. I also notice I can import data from a text file and a
>> table will be created with numerous varchar fields that are each 8000 in
>> size. Why cannot I create the table with multiple 8000 length fields but SQL
>> Server allows me to modify an existing table or import into a table that has
>> multiple 8000 length fields?
>> Thank you.
>>
>>
>>
>>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Correct. And, btw, this restriction has been removed in SQL Server 2005 ("page overflow").
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brian P" <BrianP@.discussions.microsoft.com> wrote in message
news:4AA95B81-5246-470E-A270-11810106F346@.microsoft.com...
> Thank you for the information. So I can have a table with numerous varchar
> 8000 fields and I'm ok as long as a single inserted row does not contain more
> than 8060 characters. Is this correct?
>|||Brian
But be aware that SQL Server 2005 row_overflow data only applies to variable
length fields.
You cannot have multiple char(8000) columns, for example.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ecx6jdYXGHA.4132@.TK2MSFTNGP04.phx.gbl...
> Correct. And, btw, this restriction has been removed in SQL Server 2005
> ("page overflow").
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Brian P" <BrianP@.discussions.microsoft.com> wrote in message
> news:4AA95B81-5246-470E-A270-11810106F346@.microsoft.com...
>> Thank you for the information. So I can have a table with numerous
>> varchar
>> 8000 fields and I'm ok as long as a single inserted row does not contain
>> more
>> than 8060 characters. Is this correct?|||Thanks everyone for the good information. I appreciate the feedback.
Brian
"Kalen Delaney" wrote:
> Brian
> But be aware that SQL Server 2005 row_overflow data only applies to variable
> length fields.
> You cannot have multiple char(8000) columns, for example.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:ecx6jdYXGHA.4132@.TK2MSFTNGP04.phx.gbl...
> > Correct. And, btw, this restriction has been removed in SQL Server 2005
> > ("page overflow").
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "Brian P" <BrianP@.discussions.microsoft.com> wrote in message
> > news:4AA95B81-5246-470E-A270-11810106F346@.microsoft.com...
> >> Thank you for the information. So I can have a table with numerous
> >> varchar
> >> 8000 fields and I'm ok as long as a single inserted row does not contain
> >> more
> >> than 8060 characters. Is this correct?
> >>
>
>