Alter Table Row Size Error

I am trying to alter a table and getting the following error.
Server: Msg 1701, Level 16, State 2, Line 1
Creation of table 'CustomerMaster' failed because the row size would be
8508, including internal overhead. This exceeds the maximum allowable table
row size, 8060.
I can create a new table with the same fields, but not alter an existing one.
I have calculated the maximum row size as only 6004 after the changes. Here
is what I used to calculate.
69 columns
39 char fields (5816 total characters)
9 tinyint fields (size = 9 * 1 = 9)
4 int fields (size = 4 * 4 = 16)
5 datetime fields (size = 5 * 8 = 40)
12 decimal 19,5 fields (size = 12 * 9 = 108)
Num_Cols = 69
Fixed_Data_Size = 5989
Num_Var_Cols = 0
Max_Var_Size = 0
Null_Bitmap = 2 + ((69 + 7 ) / 8 = 11.5
Row_Size = 5981 + 0 + 11 + 4 = 6004> 39 char fields (5816 total characters)
What are the definitions of these columns? Are they
CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
> 9 tinyint fields (size = 9 * 1 = 9)
> 4 int fields (size = 4 * 4 = 16)
> 5 datetime fields (size = 5 * 8 = 40)
> 12 decimal 19,5 fields (size = 12 * 9 = 108)
Are any of these NULLable?|||"Aaron Bertrand [SQL Server MVP]" wrote:
> > 39 char fields (5816 total characters)
> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
> > 9 tinyint fields (size = 9 * 1 = 9)
> > 4 int fields (size = 4 * 4 = 16)
> > 5 datetime fields (size = 5 * 8 = 40)
> > 12 decimal 19,5 fields (size = 12 * 9 = 108)
> Are any of these NULLable?
>
>
39 CHAR fields
field length - field count
3 - 2
5 - 3
10 - 4
15 - 1
20 - 11
25 - 2
30 - 11
45 - 2
50 - 1
2000 - 1
3000 - 1
total characters = 5816
all fields are NULLABLE but one CHAR field (in my calculations I considered
all fields as nullable)|||"Aaron Bertrand [SQL Server MVP]" wrote:
> > 39 char fields (5816 total characters)
> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
> > 9 tinyint fields (size = 9 * 1 = 9)
> > 4 int fields (size = 4 * 4 = 16)
> > 5 datetime fields (size = 5 * 8 = 40)
> > 12 decimal 19,5 fields (size = 12 * 9 = 108)
> Are any of these NULLable?
>
>
I used http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx to
calculate my row size.|||Are you certain they're all CHAR and none are NCHAR or NVARCHAR? Can you
post the results of:
EXEC sp_help 'tablename';
?
Why would you have a CHAR(2000) or CHAR(3000)? Is every row always going to
be 3000 characters?
"Matt Soukup" <MattSoukup@.discussions.microsoft.com> wrote in message
news:F6ADF63E-E3FD-4D8E-88C7-8D1B1C4F2D22@.microsoft.com...
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> > 39 char fields (5816 total characters)
>> What are the definitions of these columns? Are they
>> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
>> > 9 tinyint fields (size = 9 * 1 = 9)
>> > 4 int fields (size = 4 * 4 = 16)
>> > 5 datetime fields (size = 5 * 8 = 40)
>> > 12 decimal 19,5 fields (size = 12 * 9 = 108)
>> Are any of these NULLable?
>>
> 39 CHAR fields
> field length - field count
> 3 - 2
> 5 - 3
> 10 - 4
> 15 - 1
> 20 - 11
> 25 - 2
> 30 - 11
> 45 - 2
> 50 - 1
> 2000 - 1
> 3000 - 1
> total characters = 5816
> all fields are NULLABLE but one CHAR field (in my calculations I
> considered
> all fields as nullable)|||> I used http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx to
> calculate my row size.
Yes, that's all fine and good. But if you wrote down CHAR(3000) when it's
actually NCHAR(3000), and used the former in your calculations, it doesn't
really matter how accurate the calculation is, the source input invalidates
it.|||"Aaron Bertrand [SQL Server MVP]" wrote:
> > I used http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx to
> > calculate my row size.
> Yes, that's all fine and good. But if you wrote down CHAR(3000) when it's
> actually NCHAR(3000), and used the former in your calculations, it doesn't
> really matter how accurate the calculation is, the source input invalidates
> it.
>
>
all character fields are CHAR(####)
Nothing is NCHAR, VARCHAR, NVARCHAR, etc...
Here is a list of the fields:
Customer_Number char(20)
CUSTNMBR char(45)
VENDORID char(45)
Attn_Name char(30)
Contact_Name char(30)
ContactPhone char(20)
BusinessName char(30)
AddressLine1 char(30)
AddressLine2 char(30)
City char(25)
State char(3)
ZipCode char(10)
Phone char(20)
Fax char(20)
ICC_Number char(20)
FedID_Number char(15)
Contracted tinyint
Start_Date datetime
InsAgentName char(30)
InsAgentAddress1 char(30)
InsAgentAddress2 char(30)
InsAgentCity char(25)
InsAgentState char(3)
InsAgentZipCode char(10)
InsAgentPhone char(20)
InsAgentFax char(20)
InsAgentContact char(30)
CargoInsName char(28)
CargoInsAmount decimal(19, 5)
CargoInsExpirationDate datetime
AutoLibInsName char(30)
AutoLibInsAmount decimal(19, 5)
AutoLibInsExpirationDate datetime
Agent_YN tinyint
Broker_YN tinyint
Carrier_YN tinyint
Shipper_YN tinyint
Consignee_YN tinyint
BillTo_YN tinyint
CreatedDate datetime
CreatedUserID char(20)
Billing_Rate decimal(19, 5)
Billing_Rate_Code char(5)
Rating char(5)
Color char(20)
Flagged tinyint
FlagDate datetime
FlagUserID char(20)
FlagReason char(50)
Active tinyint
Notes char(3000)
PayCode char(5)
PayRateFlat decimal(19, 5)
PayRatePercent decimal(19, 5)
PayRateLoaded decimal(19, 5)
PayRateUnloaded decimal(19, 5)
DefaultSalesperson char(20)
Directions char(2000)
FSType char(10)
FSRateAmount decimal(19, 5)
Weight decimal(19, 5)
AgentPayAccount int
CarrierPayAccount int
DropPayAmount decimal(19, 5)
PickupPayAmount decimal(19, 5)
ISType char(10)
ISRateAmount decimal(19, 5)
Latitude int
Longitude int|||Sorry, but there is still information missing. I don't want to see your
compiled list of fields. Can you please copy and paste the result of:
EXEC sp_help 'CustomerMaster';
? Otherwise, I have no further input on this issue. The list of columns
you provided below can't possibly be 8508 bytes unless (a) the column you're
trying to add is a lot longer than you're letting on, (b) some of those data
types are not correct, or (c) you've left out some columns.
> all character fields are CHAR(####)
> Nothing is NCHAR, VARCHAR, NVARCHAR, etc...
> Here is a list of the fields:
> Customer_Number char(20)|||"Aaron Bertrand [SQL Server MVP]" wrote:
> Sorry, but there is still information missing. I don't want to see your
> compiled list of fields. Can you please copy and paste the result of:
> EXEC sp_help 'CustomerMaster';
> ? Otherwise, I have no further input on this issue. The list of columns
> you provided below can't possibly be 8508 bytes unless (a) the column you're
> trying to add is a lot longer than you're letting on, (b) some of those data
> types are not correct, or (c) you've left out some columns.
>
>
> > all character fields are CHAR(####)
> > Nothing is NCHAR, VARCHAR, NVARCHAR, etc...
> >
> > Here is a list of the fields:
> >
> > Customer_Number char(20)
>
>
Just forget it. SQL is being stupid.
This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20).
I can create this table from scratch with the columns/sizes I listed above.
The table is created with no problem. I have to up the size of one of my CHAR
fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get the
CREATE TABLE to fail with the same error.|||> Just forget it. SQL is being stupid.
I am willing to bet that it is not. But I can't prove it unless you post an
honest result from EXEC sp_help.
> This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
> COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20).
Is it possible this column is involved in a foreign key relationship, and
you are trying to implement the change through the GUI?
A|||> I can create this table from scratch with the columns/sizes I listed
> above.
> The table is created with no problem. I have to up the size of one of my
> CHAR
> fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get
> the
> CREATE TABLE to fail with the same error.
I also strongly recommend you familiarize yourself with the reasons we have
CHAR and VARCHAR. CHAR(2000) is not exactly a great choice here.
http://databases.aspfaq.com/database/what-datatype-should-i-use-for-my-character-based-database-columns.html
A|||Hi Matt
If you ALTER the length of fixed length columns, SQL Server may not reuse
the original space.
See my blog post on the subject:
http://sqlblog.com/blogs/kalen_delaney/archive/2006/10/13/301.aspx
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Matt Soukup" <MattSoukup@.discussions.microsoft.com> wrote in message
news:9EF8C9DF-A8DE-406D-B1BA-1C67338D8FB3@.microsoft.com...
>I am trying to alter a table and getting the following error.
> Server: Msg 1701, Level 16, State 2, Line 1
> Creation of table 'CustomerMaster' failed because the row size would be
> 8508, including internal overhead. This exceeds the maximum allowable
> table
> row size, 8060.
> I can create a new table with the same fields, but not alter an existing
> one.
> I have calculated the maximum row size as only 6004 after the changes.
> Here
> is what I used to calculate.
> 69 columns
> 39 char fields (5816 total characters)
> 9 tinyint fields (size = 9 * 1 = 9)
> 4 int fields (size = 4 * 4 = 16)
> 5 datetime fields (size = 5 * 8 = 40)
> 12 decimal 19,5 fields (size = 12 * 9 = 108)
> Num_Cols = 69
> Fixed_Data_Size = 5989
> Num_Var_Cols = 0
> Max_Var_Size = 0
> Null_Bitmap = 2 + ((69 + 7 ) / 8 = 11.5
> Row_Size = 5981 + 0 + 11 + 4 = 6004
>|||On Feb 7, 2:13 pm, Matt Soukup <MattSou...@.discussions.microsoft.com>
wrote:
> Just forget it. SQL is being stupid.
> This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
> COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20).
> I can create this table from scratch with the columns/sizes I listed above.
> The table is created with no problem. I have to up the size of one of my CHAR
> fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get the
> CREATE TABLE to fail with the same error.
Aaron asked you twice for the results of sp_help, and you've refused
to provide that, instead opting for the "SQL is being stupid"
response. WTF? If we can see the table structure, then we know
exactly what you're working with, and can provide a proper answer
instead of guessing.
Here's another suggestion that you can write off as "stupid" - think
about using VARCHAR instead of CHAR for your column definitions.
You're very likely wasting a ton of space with these fixed-length
2000+ column lengths. Read up on the differences between CHAR and
VARCHAR, hopefully the documentation isn't too "stupid".|||This may very well be it; I'm glad I cntinue to learn things from you Kalen.
But the OP stated:
"This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20)."
Yet is able to increase the length of a CHAR(2000) column to CHAR(5000). I
would think that if previous column modifications had caused the row size
threshold to be within 10 bytes of the current row size, that just about
*any* column length extension would cause the problem. I guess having the
actual table structure from sp_help, so understanding the order of the
columns (and a history of modifications made) would help us answer the
question better. Better still would be the result of your query against
sys.partitions/columns/system_internals_partition_columns.
In any case, the last statement in your blog entry is probably the most
helpful to the OP, though Tracy and I have both suggested it to no avail:
"So be careful when using large datatypes, especially if you want to make
them fixed length instead of variable length."
The shortest path is likely to rebuild the table. But the Directions and
Notes columns should be VARCHAR, not CHAR.
A
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e5yhB40SHHA.1552@.TK2MSFTNGP05.phx.gbl...
> Hi Matt
> If you ALTER the length of fixed length columns, SQL Server may not reuse
> the original space.
> See my blog post on the subject:
> http://sqlblog.com/blogs/kalen_delaney/archive/2006/10/13/301.aspx|||I didn't read the whole thread in detail initially, I was just tossing this
out as a possibility.
However, now that I am going back and reading over the posts, I don't see
anywhere that he said he had already altered the table changing a column
from 2000 to 5000 bytes. He said he would have to replace a 2000 byte column
with a 5000 byte column to get the original CREATE TABLE to fail. Has he
indicated he has done other ALTERs?
Yes, it would be nice to get more details from the OP, including WHY he
thinks he needs fixed length columns. Otherwise we just can't know. I agree
Directions and Notes should be variable length.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OpFCoZ4SHHA.1552@.TK2MSFTNGP05.phx.gbl...
> This may very well be it; I'm glad I cntinue to learn things from you
> Kalen.
> But the OP stated:
> "This error is being posted when I try to ALTER TABLE CustomerMaster ALTER
> COLUMN "ContactPhone" CHAR(20). Changing it from CHAR(10) to CHAR(20)."
> Yet is able to increase the length of a CHAR(2000) column to CHAR(5000).
> I would think that if previous column modifications had caused the row
> size threshold to be within 10 bytes of the current row size, that just
> about *any* column length extension would cause the problem. I guess
> having the actual table structure from sp_help, so understanding the order
> of the columns (and a history of modifications made) would help us answer
> the question better. Better still would be the result of your query
> against sys.partitions/columns/system_internals_partition_columns.
> In any case, the last statement in your blog entry is probably the most
> helpful to the OP, though Tracy and I have both suggested it to no avail:
> "So be careful when using large datatypes, especially if you want to make
> them fixed length instead of variable length."
> The shortest path is likely to rebuild the table. But the Directions and
> Notes columns should be VARCHAR, not CHAR.
> A
>
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e5yhB40SHHA.1552@.TK2MSFTNGP05.phx.gbl...
>> Hi Matt
>> If you ALTER the length of fixed length columns, SQL Server may not reuse
>> the original space.
>> See my blog post on the subject:
>> http://sqlblog.com/blogs/kalen_delaney/archive/2006/10/13/301.aspx
>|||> However, now that I am going back and reading over the posts, I don't see
> anywhere that he said he had already altered the table changing a column
> from 2000 to 5000 bytes. He said he would have to replace a 2000 byte
> column with a 5000 byte column to get the original CREATE TABLE to fail.
> Has he indicated he has done other ALTERs?
Here is what I am referring to:
I have to up the size of one of my CHAR
fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get the
CREATE TABLE to fail with the same error.
My interpretation was that he had done that. Maybe I'm wrong. It's
ambiguous.
A|||Yes, that is what I'm referring to, and he explicitly says it is the CREATE
table and not ALTER.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23UIpXh6SHHA.2228@.TK2MSFTNGP03.phx.gbl...
>> However, now that I am going back and reading over the posts, I don't see
>> anywhere that he said he had already altered the table changing a column
>> from 2000 to 5000 bytes. He said he would have to replace a 2000 byte
>> column with a 5000 byte column to get the original CREATE TABLE to fail.
>> Has he indicated he has done other ALTERs?
> Here is what I am referring to:
> I have to up the size of one of my CHAR
> fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get
> the
> CREATE TABLE to fail with the same error.
> My interpretation was that he had done that. Maybe I'm wrong. It's
> ambiguous.
> A
>|||> Yes, that is what I'm referring to, and he explicitly says it is the
> CREATE table and not ALTER.
Ahh. You should know by now that I lack the ability to read entire
sentences. :-)
And you would think the caps would make words stand out more, but for me it
obscures them... I tend to focus on the meat *between* the keywords...
A