Maximum Rowsize screw up by MSSQL 2000

I've got a table that has too many fields to fit on one page ( So i get that
warning when adding fields ).
Most of those fields were added after the creation of the table.
When I try to edit a record, I get the maxrowsize error.
When I try to add a new record to this table with only one field filled in,
I get the maxrowsize error.
So now I change one field ( from varchar 50 -> varchar 51 ) via Entreprise
Manager.
All changes via Entreprise Manager do a drop and recreate of a table.
Then I can edit the existing records and add new records to it without a
problem.
Is there a difference for MSSQL of you add fields <-> creating the table in
one piece?
Is this a known problem/bug ? How can I avoid this in the future ?
PS : Still awaiting answer from customer on SP-version installed there but
would be at least 3 because it's on Win2K3.
Regards,
Sven Peeters
BelgiumSven
Don't use EM . Perfom such operation in the QA
CREATE TABLE eee
(
col1 VARCHAR(4000),
col2 VARCHAR(4000),
col3 VARCHAR(1000)
)
--Warning: The table 'eee' has been
--created but its maximum row size (9027)
-- exceeds the maximum number of bytes per row (8060).
-- INSERT or UPDATE of a row in this table will fail if
-- the resulting row length exceeds 8060 bytes.
If you deal with large amount of data you might consider using TEXT datatype
instead.
CREATE TABLE eee11
(
col1 TEXT,
col2 TEXT,
col3 VARCHAR(1000)
)
DROP TABLE eee,eee11
"Sven Peeters" <SvenPeeters@.discussions.microsoft.com> wrote in message
news:281B4941-F6EE-4098-A7E1-DCA82C039B36@.microsoft.com...
> I've got a table that has too many fields to fit on one page ( So i get
> that
> warning when adding fields ).
> Most of those fields were added after the creation of the table.
> When I try to edit a record, I get the maxrowsize error.
> When I try to add a new record to this table with only one field filled
> in,
> I get the maxrowsize error.
> So now I change one field ( from varchar 50 -> varchar 51 ) via Entreprise
> Manager.
> All changes via Entreprise Manager do a drop and recreate of a table.
> Then I can edit the existing records and add new records to it without a
> problem.
> Is there a difference for MSSQL of you add fields <-> creating the table
> in
> one piece?
> Is this a known problem/bug ? How can I avoid this in the future ?
> PS : Still awaiting answer from customer on SP-version installed there but
> would be at least 3 because it's on Win2K3.
> Regards,
> Sven Peeters
> Belgium|||That's not the problem, most fields were added by T-SQL command.
Cannot change/add records to this table ( which has data below the maxsize )
.
I change one field ( make it even bigger ) in EM and after that I can
change/add records again.
That's the same table and data, only difference is that the table was
dropped and recreated ( with data ).
"Uri Dimant" wrote:

> Sven
> Don't use EM . Perfom such operation in the QA
>
> CREATE TABLE eee
> (
> col1 VARCHAR(4000),
> col2 VARCHAR(4000),
> col3 VARCHAR(1000)
> )
> --Warning: The table 'eee' has been
> --created but its maximum row size (9027)
> -- exceeds the maximum number of bytes per row (8060).
> -- INSERT or UPDATE of a row in this table will fail if
> -- the resulting row length exceeds 8060 bytes.
>
> If you deal with large amount of data you might consider using TEXT dataty
pe
> instead.
> CREATE TABLE eee11
> (
> col1 TEXT,
> col2 TEXT,
> col3 VARCHAR(1000)
> )
>
> DROP TABLE eee,eee11
>
> "Sven Peeters" <SvenPeeters@.discussions.microsoft.com> wrote in message
> news:281B4941-F6EE-4098-A7E1-DCA82C039B36@.microsoft.com...
>
>|||PS : SQL Version : 8.00.760 (sp3)
"Sven Peeters" wrote:

> I've got a table that has too many fields to fit on one page ( So i get th
at
> warning when adding fields ).
> Most of those fields were added after the creation of the table.
> When I try to edit a record, I get the maxrowsize error.
> When I try to add a new record to this table with only one field filled in
,
> I get the maxrowsize error.
> So now I change one field ( from varchar 50 -> varchar 51 ) via Entreprise
> Manager.
> All changes via Entreprise Manager do a drop and recreate of a table.
> Then I can edit the existing records and add new records to it without a
> problem.
> Is there a difference for MSSQL of you add fields <-> creating the table i
n
> one piece?
> Is this a known problem/bug ? How can I avoid this in the future ?
> PS : Still awaiting answer from customer on SP-version installed there but
> would be at least 3 because it's on Win2K3.
> Regards,
> Sven Peeters
> Belgium

maximum number of fields that can be created in a table

hi...
I would like to know about maximum number number of fields that can be created in a table. my requirement is to meet data entry in morethan 300 fields. I am using SQL 2000. if impossible is there any other method to incorporate additional fields?
Please reply as soon as possible.
linHi linsha,

from memory its 1024 for SQL2k.

I would suggest you take a look again at the data structure and see if you can do some normalisation - take a look at this article for some background reading if required.

Regards Purple|||

Quote:

Originally Posted by linsha

hi...
I would like to know about maximum number number of fields that can be created in a table. my requirement is to meet data entry in morethan 300 fields. I am using SQL 2000. if impossible is there any other method to incorporate additional fields?
Please reply as soon as possible.
lin

if you have that many fields you might think about many to many relationships. Read up onnormalizations

nomad|||thankyou for your reply . Let me try it .
lin

Maximum number of columns in an index

I am upgrading from Access, where you can only have 10 fields in a primary key or unique index. Is this also the limit in SQL Server? If not, what is the limit?

Thanks for any help on this.

From Books Online, Topic: 'Create Index'

"Up to 16 columns can be combined into a single composite index key."

max(keyfield)

I forget, does SQLServer have an internal optimization, such that if
you have a (clustered) index on fields A and B, and you do a select
where max(A) = 'X', SQLServer does NOT have to do a scan to figure out
the max?
Thanks.
J.
A clustered index is an index but with the data at the leaf level. So if A
is the only or first column in the index (clustered or not) it can determine
what the max is with a seek and not a scan.
Andrew J. Kelly SQL MVP
"jxstern" <jxstern@.nowhere.com> wrote in message
news:pouhp01v0onje4a3tgserrtb1ijr8bljd7@.4ax.com...
>I forget, does SQLServer have an internal optimization, such that if
> you have a (clustered) index on fields A and B, and you do a select
> where max(A) = 'X', SQLServer does NOT have to do a scan to figure out
> the max?
> Thanks.
> J.
>
|||In addition to Andrew's points, I have a question. What kind of query is
[vbcol=seagreen]
? Do you expect this to return a row? How? MAX() is an aggregate
function that works on all rows, while WHERE is a clause that works on
individual rows. Maybe you can show us a more practical query ...
http://www.aspfaq.com/
(Reverse address to reply.)
|||On Mon, 15 Nov 2004 14:33:57 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>In addition to Andrew's points, I have a question. What kind of query is
>
>? Do you expect this to return a row? How? MAX() is an aggregate
>function that works on all rows, while WHERE is a clause that works on
>individual rows. Maybe you can show us a more practical query ...
OK, that was a little terse, try something like:
select name
from mytable
where trxdate = (select max(trxdate) from mytable)
So a seek is better than a scan, anyway, and it can do this even for a
clustered index because the top index page has a last row. I just
wondered if it might do even better and cache the high value, but the
important thing is that you confirm it at least an avoid the full
scan. Though, now that I look at my real code again, I suspect it's
going to scan, anyway, oh well, at least I can improve my education
from this example.
Thanks.
J.
|||> So a seek is better than a scan, anyway, and it can do this even for a
> clustered index because the top index page has a last row. I just
> wondered if it might do even better and cache the high value,
No, I don't think individual column values can be cached the way you are
describing.
However, for your education, wouldn't it be relatively simple to set up a
simple test?
CREATE TABLE Kerplunk
(
foo INT,
bar INT,
raboof INT
)
GO
CREATE CLUSTERED INDEX f ON Kerplunk(foo)
CREATE INDEX f ON Kerplunk(foo)
GO
SET NOCOUNT ON
-- <<< populate data here! >>>
-- hit Ctrl+K to see execution plan
-- it will show where scans/seeks are used...
SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM Kerplunk)
GO
DROP TABLE Kerplunk
GO
|||On Mon, 15 Nov 2004 15:18:06 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>No, I don't think individual column values can be cached the way you are
>describing.
Just the high and low.

>However, for your education, wouldn't it be relatively simple to set up a
>simple test?
Well, yeah, I guess, but sometimes it feels more productive to ask
than to do the reverse engineering, and some happy lurker might
benefit thereby.

>CREATE TABLE Kerplunk
>(
> foo INT,
> bar INT,
> raboof INT
>)
>GO
>CREATE CLUSTERED INDEX f ON Kerplunk(foo)
>CREATE INDEX f ON Kerplunk(foo)
on (bar), I suppose you meant.

>GO
>SET NOCOUNT ON
>-- <<< populate data here! >>>
>-- hit Ctrl+K to see execution plan
>-- it will show where scans/seeks are used...
>SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
>SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
>SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM Kerplunk)
>GO
>DROP TABLE Kerplunk
>GO
|||> Just the high and low.
My statement remains as is.

> Well, yeah, I guess, but sometimes it feels more productive to ask
> than to do the reverse engineering, and some happy lurker might
> benefit thereby.
Perhaps, but unless we know every single detail about your environment,
you're only going to get educated guesses.

> on (bar), I suppose you meant.
Yes, I did.
|||On Mon, 15 Nov 2004 17:41:19 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>My statement remains as is.
>
>Perhaps, but unless we know every single detail about your environment,
>you're only going to get educated guesses.
>
>Yes, I did.
CREATE TABLE Kerplunk
(
foo INT,
bar INT,
raboof INT
)
GO
CREATE CLUSTERED INDEX f ON Kerplunk(foo)
CREATE INDEX b ON Kerplunk(bar)
GO
-- <<< populate data here! >>>
SET NOCOUNT ON
declare @.ix int
set @.ix = 0
while @.ix < 100000
begin
insert into Kerplunk values (@.ix, @.ix, @.ix)
set @.ix = @.ix + 1
end
-- hit Ctrl+K to see execution plan
-- it will show where scans/seeks are used...
SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
-- .00640
SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
-- .00650
SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM
Kerplunk)
-- 1.13
GO
DROP TABLE Kerplunk
GO

max(keyfield)

I forget, does SQLServer have an internal optimization, such that if
you have a (clustered) index on fields A and B, and you do a select
where max(A) = 'X', SQLServer does NOT have to do a scan to figure out
the max?
Thanks.
J.A clustered index is an index but with the data at the leaf level. So if A
is the only or first column in the index (clustered or not) it can determine
what the max is with a seek and not a scan.
--
Andrew J. Kelly SQL MVP
"jxstern" <jxstern@.nowhere.com> wrote in message
news:pouhp01v0onje4a3tgserrtb1ijr8bljd7@.4ax.com...
>I forget, does SQLServer have an internal optimization, such that if
> you have a (clustered) index on fields A and B, and you do a select
> where max(A) = 'X', SQLServer does NOT have to do a scan to figure out
> the max?
> Thanks.
> J.
>|||In addition to Andrew's points, I have a question. What kind of query is
>> where max(A) = 'X'
' Do you expect this to return a row? How? MAX() is an aggregate
function that works on all rows, while WHERE is a clause that works on
individual rows. Maybe you can show us a more practical query ...
--
http://www.aspfaq.com/
(Reverse address to reply.)|||On Mon, 15 Nov 2004 14:33:57 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>In addition to Andrew's points, I have a question. What kind of query is
>> where max(A) = 'X'
>' Do you expect this to return a row? How? MAX() is an aggregate
>function that works on all rows, while WHERE is a clause that works on
>individual rows. Maybe you can show us a more practical query ...
OK, that was a little terse, try something like:
select name
from mytable
where trxdate = (select max(trxdate) from mytable)
So a seek is better than a scan, anyway, and it can do this even for a
clustered index because the top index page has a last row. I just
wondered if it might do even better and cache the high value, but the
important thing is that you confirm it at least an avoid the full
scan. Though, now that I look at my real code again, I suspect it's
going to scan, anyway, oh well, at least I can improve my education
from this example.
Thanks.
J.|||> So a seek is better than a scan, anyway, and it can do this even for a
> clustered index because the top index page has a last row. I just
> wondered if it might do even better and cache the high value,
No, I don't think individual column values can be cached the way you are
describing.
However, for your education, wouldn't it be relatively simple to set up a
simple test?
CREATE TABLE Kerplunk
(
foo INT,
bar INT,
raboof INT
)
GO
CREATE CLUSTERED INDEX f ON Kerplunk(foo)
CREATE INDEX f ON Kerplunk(foo)
GO
SET NOCOUNT ON
-- <<< populate data here! >>
-- hit Ctrl+K to see execution plan
-- it will show where scans/seeks are used...
SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM Kerplunk)
GO
DROP TABLE Kerplunk
GO|||On Mon, 15 Nov 2004 15:18:06 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>> So a seek is better than a scan, anyway, and it can do this even for a
>> clustered index because the top index page has a last row. I just
>> wondered if it might do even better and cache the high value,
>No, I don't think individual column values can be cached the way you are
>describing.
Just the high and low.
>However, for your education, wouldn't it be relatively simple to set up a
>simple test?
Well, yeah, I guess, but sometimes it feels more productive to ask
than to do the reverse engineering, and some happy lurker might
benefit thereby.
>CREATE TABLE Kerplunk
>(
> foo INT,
> bar INT,
> raboof INT
>)
>GO
>CREATE CLUSTERED INDEX f ON Kerplunk(foo)
>CREATE INDEX f ON Kerplunk(foo)
on (bar), I suppose you meant.
>GO
>SET NOCOUNT ON
>-- <<< populate data here! >>
>-- hit Ctrl+K to see execution plan
>-- it will show where scans/seeks are used...
>SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
>SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
>SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM Kerplunk)
>GO
>DROP TABLE Kerplunk
>GO|||> Just the high and low.
My statement remains as is.
> Well, yeah, I guess, but sometimes it feels more productive to ask
> than to do the reverse engineering, and some happy lurker might
> benefit thereby.
Perhaps, but unless we know every single detail about your environment,
you're only going to get educated guesses.
> on (bar), I suppose you meant.
Yes, I did.|||On Mon, 15 Nov 2004 17:41:19 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>> Just the high and low.
>My statement remains as is.
>> Well, yeah, I guess, but sometimes it feels more productive to ask
>> than to do the reverse engineering, and some happy lurker might
>> benefit thereby.
>Perhaps, but unless we know every single detail about your environment,
>you're only going to get educated guesses.
>> on (bar), I suppose you meant.
>Yes, I did.
CREATE TABLE Kerplunk
(
foo INT,
bar INT,
raboof INT
)
GO
CREATE CLUSTERED INDEX f ON Kerplunk(foo)
CREATE INDEX b ON Kerplunk(bar)
GO
-- <<< populate data here! >>
SET NOCOUNT ON
declare @.ix int
set @.ix = 0
while @.ix < 100000
begin
insert into Kerplunk values (@.ix, @.ix, @.ix)
set @.ix = @.ix + 1
end
-- hit Ctrl+K to see execution plan
-- it will show where scans/seeks are used...
SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
-- .00640
SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
-- .00650
SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM
Kerplunk)
-- 1.13
GO
DROP TABLE Kerplunk
GO

max(keyfield)

I forget, does SQLServer have an internal optimization, such that if
you have a (clustered) index on fields A and B, and you do a select
where max(A) = 'X', SQLServer does NOT have to do a scan to figure out
the max?
Thanks.
J.A clustered index is an index but with the data at the leaf level. So if A
is the only or first column in the index (clustered or not) it can determine
what the max is with a seek and not a scan.
Andrew J. Kelly SQL MVP
"jxstern" <jxstern@.nowhere.com> wrote in message
news:pouhp01v0onje4a3tgserrtb1ijr8bljd7@.
4ax.com...
>I forget, does SQLServer have an internal optimization, such that if
> you have a (clustered) index on fields A and B, and you do a select
> where max(A) = 'X', SQLServer does NOT have to do a scan to figure out
> the max?
> Thanks.
> J.
>|||In addition to Andrew's points, I have a question. What kind of query is
[vbcol=seagreen]
' Do you expect this to return a row? How? MAX() is an aggregate
function that works on all rows, while WHERE is a clause that works on
individual rows. Maybe you can show us a more practical query ...
http://www.aspfaq.com/
(Reverse address to reply.)|||On Mon, 15 Nov 2004 14:33:57 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>In addition to Andrew's points, I have a question. What kind of query is
>
>' Do you expect this to return a row? How? MAX() is an aggregate
>function that works on all rows, while WHERE is a clause that works on
>individual rows. Maybe you can show us a more practical query ...
OK, that was a little terse, try something like:
select name
from mytable
where trxdate = (select max(trxdate) from mytable)
So a seek is better than a scan, anyway, and it can do this even for a
clustered index because the top index page has a last row. I just
wondered if it might do even better and cache the high value, but the
important thing is that you confirm it at least an avoid the full
scan. Though, now that I look at my real code again, I suspect it's
going to scan, anyway, oh well, at least I can improve my education
from this example.
Thanks.
J.|||> So a seek is better than a scan, anyway, and it can do this even for a
> clustered index because the top index page has a last row. I just
> wondered if it might do even better and cache the high value,
No, I don't think individual column values can be cached the way you are
describing.
However, for your education, wouldn't it be relatively simple to set up a
simple test?
CREATE TABLE Kerplunk
(
foo INT,
bar INT,
raboof INT
)
GO
CREATE CLUSTERED INDEX f ON Kerplunk(foo)
CREATE INDEX f ON Kerplunk(foo)
GO
SET NOCOUNT ON
-- <<< populate data here! >>>
-- hit Ctrl+K to see execution plan
-- it will show where scans/seeks are used...
SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM Kerplunk)
GO
DROP TABLE Kerplunk
GO|||On Mon, 15 Nov 2004 15:18:06 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>No, I don't think individual column values can be cached the way you are
>describing.
Just the high and low.

>However, for your education, wouldn't it be relatively simple to set up a
>simple test?
Well, yeah, I guess, but sometimes it feels more productive to ask
than to do the reverse engineering, and some happy lurker might
benefit thereby.

>CREATE TABLE Kerplunk
>(
> foo INT,
> bar INT,
> raboof INT
> )
>GO
>CREATE CLUSTERED INDEX f ON Kerplunk(foo)
>CREATE INDEX f ON Kerplunk(foo)
on (bar), I suppose you meant.

>GO
>SET NOCOUNT ON
>-- <<< populate data here! >>>
>-- hit Ctrl+K to see execution plan
>-- it will show where scans/seeks are used...
>SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
>SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
>SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM Kerplunk)
>GO
>DROP TABLE Kerplunk
>GO|||> Just the high and low.
My statement remains as is.

> Well, yeah, I guess, but sometimes it feels more productive to ask
> than to do the reverse engineering, and some happy lurker might
> benefit thereby.
Perhaps, but unless we know every single detail about your environment,
you're only going to get educated guesses.

> on (bar), I suppose you meant.
Yes, I did.|||On Mon, 15 Nov 2004 17:41:19 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>My statement remains as is.
>
>Perhaps, but unless we know every single detail about your environment,
>you're only going to get educated guesses.
>
>Yes, I did.
CREATE TABLE Kerplunk
(
foo INT,
bar INT,
raboof INT
)
GO
CREATE CLUSTERED INDEX f ON Kerplunk(foo)
CREATE INDEX b ON Kerplunk(bar)
GO
-- <<< populate data here! >>>
SET NOCOUNT ON
declare @.ix int
set @.ix = 0
while @.ix < 100000
begin
insert into Kerplunk values (@.ix, @.ix, @.ix)
set @.ix = @.ix + 1
end
-- hit Ctrl+K to see execution plan
-- it will show where scans/seeks are used...
SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
-- .00640
SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
-- .00650
SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM
Kerplunk)
-- 1.13
GO
DROP TABLE Kerplunk
GO