May 9, 2003 at 3:40 am
Hi,
I have a problem with increasing number of bytes per row.
Please run this SQL script to obtain the problem:
--------------------------------------------
set nocount on
drop table test
go
create table test (
test1 varchar (8000),
test2 varchar (17),
test3 numeric (18,0)
)
go
declare @counter numeric
select @counter = 0
while( @counter < 100 )
begin
alter table test drop column test3
alter table test add test3 numeric (18,5)
select @counter = @counter + 1
select @counter
end
set nocount off
--------------------------------------------
The results of execution on MSSQL 2000 are:
counter
--------------------
1
counter
--------------------
2
...
counter
--------------------
5
Warning: The table 'test' has been created but its maximum row size (8061) 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.
...
counter
--------------------
13
Warning: The table 'test' has been created but its maximum row size (8062) 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.
...
counter
--------------------
21
Warning: The table 'test' has been created but its maximum row size (8063) 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.
May 9, 2003 at 4:46 am
This is interesting, looks like there is a hidden piece to allocations.
May 9, 2003 at 7:54 am
If after reaching that point you BCP out/in, then try to modify, do you get the error then?
Andy
May 10, 2003 at 8:46 pm
I thought it may have a bearing on whether the table has an index or not, how SQL handle tables without PKs.
First made second column an primary index. Problem persist.
Secondly change second(pk) to int and problem disappeared.
set nocount on
drop table test
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test]
GO
CREATE TABLE [dbo].[test] (
[test1] [varchar] (8000) NULL ,
[test2] int NOT NULL ,
[test3] [numeric](18, 0) NULL) ON [PRIMARY]
GO
ALTER TABLE [dbo].[test] WITH NOCHECK ADD
CONSTRAINT [IX_test] UNIQUE CLUSTERED
([test2])
GO
insert test values(1,1,1)
insert test values(2,2,2)
insert test values(3,3,3)
go
declare @counter numeric
select @counter = 0
while( @counter < 100 )
begin
alter table test drop column test3
alter table test add test3 numeric (18,5)
select @counter = @counter + 1
select @counter
end
set nocount off
Conclusions: I think MS will have a simple explanations. One should start looking at the record layout of SQL specially when variable length columns are included.
May 12, 2003 at 3:41 am
I thought I remembered this one, from SQL Server Magazine UPDATE, May 3 2001...
* TIP: MAXIMUM ROW SIZE
(contributed by Richard Waymire, rwaymi@microsoft.com)
Q. How can I find out the maximum row size for a table in which all the
varchars are fully populated?
A. The only way to find a SQL Server table's maximum row size is to
query the system tables (or the INFORMATION_SCHEMA views) and add up the
byte counts. But note that Microsoft doesn't recommend querying system
tables, which can change from release to release. Also note that the
total row size never exceeds about 8060 bytes in SQL Server 2000 or
7.0.
May 12, 2003 at 3:59 am
Ahh, but that's the thing, none of the usual SPs or INFORMATION_SCHEMA items report an incorrect size. And none of the usuall DBCC items report any issues.
May 12, 2003 at 4:32 am
Hi,
In first example the row size is 8026.
In the second example which is sent by 5409045121009 the row size is 8013.
When you change loop condition to @counter < 200, you obtain the warning in iteration 126.
I calculate row size using query:
select sum(length) from syscolumns where id=object_id('test')
The main problem is:
When you have a table with large row size the table structure modification
calls warning message for row size equal or less then 8035.
For example:
create table test (
test1 varchar (8000),
test2 varchar (35),
)
go
--row size is 8035
select sum(length) from syscolumns where id=object_id('test')
go
--few table modifications calls warning message but row size is this same row size.
declare @counter numeric
select @counter = 0
while( @counter < 100 )
begin
alter table test drop column test2
alter table test add test2 varchar (35)
select @counter = @counter + 1
select @counter as counter, sum(length) as length from syscolumns where id=object_id('test')
end
go
drop table test
go
/*
And row size (8062) is incremented for few iterations:
counter = 7
Warning: The table 'test' has been created but its maximum row size (8061) 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.
counter = 16
Warning: The table 'test' has been created but its maximum row size (8062) 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.
*/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply