Bug? pessimistic row size calculations

23 Sep 2005 9:01 AM

Hugo Kornelis

Hi all,

Just came across some strange behaviour - see the simplified repro
below. Is this a known bug?

-- Make a table with long rows. According to my calculation,
-- maximum row size = 4 + 8000 + 40 (maximum data size)
-- + 1 (NULL bitmap) + 2 (1 or more variable length columns)
-- + 2 (for one variable length column) + 6 (fixed overhead)
-- for a total of 8,055 bytes. Just under the maximum of 8,060.
-- However, SQL Server warns me that the maximum size is 8,067 bytes,
-- and I will encounter errors if I fill all columns to the max.
CREATE TABLE test (col1 char(4) NOT NULL PRIMARY KEY,
col2 varchar(8000) NULL,
col3 char(40) NULL)
go

-- Let's see who's calculations are better. If SQL Server is right,
-- adding a row with maximum size for all columns should fail.
-- But there is no error message!
INSERT test (col1, col2, col3)
SELECT 'aaaa', REPLICATE('b', 8000), REPLICATE('c', 40)
go

-- And the row is actually there!
SELECT * FROM test
go

-- Let's get rid of this silly table before it drives us mad!
DROP TABLE test
go

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

23 Sep 2005 10:31 AM

Erland Sommarskog

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:

> Just came across some strange behaviour - see the simplified repro
> below. Is this a known bug?
>
> -- Make a table with long rows. According to my calculation,
> -- maximum row size = 4 + 8000 + 40 (maximum data size)
> -- + 1 (NULL bitmap) + 2 (1 or more variable length columns)
> -- + 2 (for one variable length column) + 6 (fixed overhead)
> -- for a total of 8,055 bytes. Just under the maximum of 8,060.
> -- However, SQL Server warns me that the maximum size is 8,067 bytes,
> -- and I will encounter errors if I fill all columns to the max.

Certainly looks like a bug to me. I have no idea if this is a known issue
or not. I've reported it to Microsoft, but I would not expect this to
be fixed. This is because in SQL 2005, you will never get this warning,
but you get an overflow page if the row does not fit into one page.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

23 Sep 2005 12:00 PM

Syed Maqsood Hafeez

test
Show quote

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:img7j1pcjk0sgrb00airqk3lvn7ldvc4u3@4ax.com...
> Hi all,
>
> Just came across some strange behaviour - see the simplified repro
> below. Is this a known bug?
>
> -- Make a table with long rows. According to my calculation,
> -- maximum row size = 4 + 8000 + 40 (maximum data size)
> -- + 1 (NULL bitmap) + 2 (1 or more variable length columns)
> -- + 2 (for one variable length column) + 6 (fixed overhead)
> -- for a total of 8,055 bytes. Just under the maximum of 8,060.
> -- However, SQL Server warns me that the maximum size is 8,067 bytes,
> -- and I will encounter errors if I fill all columns to the max.
> CREATE TABLE test (col1 char(4) NOT NULL PRIMARY KEY,
> col2 varchar(8000) NULL,
> col3 char(40) NULL)
> go
>
> -- Let's see who's calculations are better. If SQL Server is right,
> -- adding a row with maximum size for all columns should fail.
> -- But there is no error message!
> INSERT test (col1, col2, col3)
> SELECT 'aaaa', REPLICATE('b', 8000), REPLICATE('c', 40)
> go
>
> -- And the row is actually there!
> SELECT * FROM test
> go
>
> -- Let's get rid of this silly table before it drives us mad!
> DROP TABLE test
> go
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>