Hi,
I'm trying to add an extra element into a schema collection and keep getting
an error message:
"Msg 9455, Level 16, State 1, Line 1
XML parsing: line 3, character 1, illegal qualified name character
"
The SQL I'm using is:
alter xml schema collection dbo.EmployeeSchemaCollection ADD N'
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
<xsd:element name="Employee">
<xsd:complexType>
<xsd:element name="HireDate" type="xsd:datetime" />
</xsd:complexType>
</xsd:element>
</xsd:schema>'
Can anyone help?
ThanksI don't know this stuff much however should not be a ">" in the end of the
second line? (<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema")
--
Ekrem Ã?nsoy
"John S" <js162@.newsgroup.nospam> wrote in message
news:F7FE9209-3176-4742-97AD-9689A20085A1@.microsoft.com...
> Hi,
> I'm trying to add an extra element into a schema collection and keep
> getting
> an error message:
> "Msg 9455, Level 16, State 1, Line 1
> XML parsing: line 3, character 1, illegal qualified name character
> "
> The SQL I'm using is:
> alter xml schema collection dbo.EmployeeSchemaCollection ADD N'
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> <xsd:element name="Employee">
> <xsd:complexType>
> <xsd:element name="HireDate" type="xsd:datetime" />
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>'
> Can anyone help?
> Thanks|||It's so easy to miss the obvious!
Thanks
JS
"Ekrem Ã?nsoy" wrote:
> I don't know this stuff much however should not be a ">" in the end of the
> second line? (<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema")
> --
> Ekrem Ã?nsoy
>
> "John S" <js162@.newsgroup.nospam> wrote in message
> news:F7FE9209-3176-4742-97AD-9689A20085A1@.microsoft.com...
> > Hi,
> >
> > I'm trying to add an extra element into a schema collection and keep
> > getting
> > an error message:
> > "Msg 9455, Level 16, State 1, Line 1
> > XML parsing: line 3, character 1, illegal qualified name character
> > "
> >
> > The SQL I'm using is:
> >
> > alter xml schema collection dbo.EmployeeSchemaCollection ADD N'
> > <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> > <xsd:element name="Employee">
> > <xsd:complexType>
> > <xsd:element name="HireDate" type="xsd:datetime" />
> > </xsd:complexType>
> > </xsd:element>
> > </xsd:schema>'
> >
> > Can anyone help?
> >
> > Thanks
>sql
Showing posts with label msg. Show all posts
Showing posts with label msg. Show all posts
Tuesday, March 27, 2012
Thursday, March 22, 2012
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
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
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:
> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
>
> 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:
> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
>
> 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
> 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:
> 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 invalidate
s
> 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 column
s
> 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 da
ta
> types are not correct, or (c) you've left out some columns.
>
>
>
>
>
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 CHA
R
fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get the
CREATE TABLE to fail with the same error.|||> 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/databas...se-columns.html
Asql
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:
> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
>
> 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:
> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
>
> 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
> 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:
> 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 invalidate
s
> 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 column
s
> 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 da
ta
> types are not correct, or (c) you've left out some columns.
>
>
>
>
>
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 CHA
R
fields by 3000, change Directions from CHAR(2000) to CHAR(5000), to get the
CREATE TABLE to fail with the same error.|||> 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/databas...se-columns.html
Asql
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:
> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
>
> 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:
> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
>
> 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
> 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:
> 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_Numberchar(20)
CUSTNMBRchar(45)
VENDORIDchar(45)
Attn_Namechar(30)
Contact_Namechar(30)
ContactPhonechar(20)
BusinessNamechar(30)
AddressLine1char(30)
AddressLine2char(30)
Citychar(25)
Statechar(3)
ZipCodechar(10)
Phonechar(20)
Faxchar(20)
ICC_Numberchar(20)
FedID_Numberchar(15)
Contractedtinyint
Start_Datedatetime
InsAgentNamechar(30)
InsAgentAddress1char(30)
InsAgentAddress2char(30)
InsAgentCitychar(25)
InsAgentStatechar(3)
InsAgentZipCodechar(10)
InsAgentPhonechar(20)
InsAgentFaxchar(20)
InsAgentContactchar(30)
CargoInsNamechar(28)
CargoInsAmountdecimal(19, 5)
CargoInsExpirationDatedatetime
AutoLibInsNamechar(30)
AutoLibInsAmountdecimal(19, 5)
AutoLibInsExpirationDatedatetime
Agent_YNtinyint
Broker_YNtinyint
Carrier_YNtinyint
Shipper_YNtinyint
Consignee_YNtinyint
BillTo_YNtinyint
CreatedDatedatetime
CreatedUserIDchar(20)
Billing_Ratedecimal(19, 5)
Billing_Rate_Codechar(5)
Ratingchar(5)
Colorchar(20)
Flaggedtinyint
FlagDatedatetime
FlagUserIDchar(20)
FlagReasonchar(50)
Activetinyint
Noteschar(3000)
PayCodechar(5)
PayRateFlatdecimal(19, 5)
PayRatePercentdecimal(19, 5)
PayRateLoadeddecimal(19, 5)
PayRateUnloadeddecimal(19, 5)
DefaultSalespersonchar(20)
Directionschar(2000)
FSTypechar(10)
FSRateAmountdecimal(19, 5)
Weightdecimal(19, 5)
AgentPayAccountint
CarrierPayAccountint
DropPayAmountdecimal(19, 5)
PickupPayAmountdecimal(19, 5)
ISTypechar(10)
ISRateAmountdecimal(19, 5)
Latitudeint
Longitudeint
|||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.
>
>
>
>
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
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:
> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
>
> 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:
> What are the definitions of these columns? Are they
> CHAR/NCHAR/VARCHAR/NVARCHAR? What size? Are they NULLable?
>
> 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
> 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:
> 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_Numberchar(20)
CUSTNMBRchar(45)
VENDORIDchar(45)
Attn_Namechar(30)
Contact_Namechar(30)
ContactPhonechar(20)
BusinessNamechar(30)
AddressLine1char(30)
AddressLine2char(30)
Citychar(25)
Statechar(3)
ZipCodechar(10)
Phonechar(20)
Faxchar(20)
ICC_Numberchar(20)
FedID_Numberchar(15)
Contractedtinyint
Start_Datedatetime
InsAgentNamechar(30)
InsAgentAddress1char(30)
InsAgentAddress2char(30)
InsAgentCitychar(25)
InsAgentStatechar(3)
InsAgentZipCodechar(10)
InsAgentPhonechar(20)
InsAgentFaxchar(20)
InsAgentContactchar(30)
CargoInsNamechar(28)
CargoInsAmountdecimal(19, 5)
CargoInsExpirationDatedatetime
AutoLibInsNamechar(30)
AutoLibInsAmountdecimal(19, 5)
AutoLibInsExpirationDatedatetime
Agent_YNtinyint
Broker_YNtinyint
Carrier_YNtinyint
Shipper_YNtinyint
Consignee_YNtinyint
BillTo_YNtinyint
CreatedDatedatetime
CreatedUserIDchar(20)
Billing_Ratedecimal(19, 5)
Billing_Rate_Codechar(5)
Ratingchar(5)
Colorchar(20)
Flaggedtinyint
FlagDatedatetime
FlagUserIDchar(20)
FlagReasonchar(50)
Activetinyint
Noteschar(3000)
PayCodechar(5)
PayRateFlatdecimal(19, 5)
PayRatePercentdecimal(19, 5)
PayRateLoadeddecimal(19, 5)
PayRateUnloadeddecimal(19, 5)
DefaultSalespersonchar(20)
Directionschar(2000)
FSTypechar(10)
FSRateAmountdecimal(19, 5)
Weightdecimal(19, 5)
AgentPayAccountint
CarrierPayAccountint
DropPayAmountdecimal(19, 5)
PickupPayAmountdecimal(19, 5)
ISTypechar(10)
ISRateAmountdecimal(19, 5)
Latitudeint
Longitudeint
|||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.
>
>
>
>
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
Tuesday, March 20, 2012
alter table nocheck constraint still some dependencies
Hi.
I'm getting errors like this when I try to run an upgrade script I'm trying to
write/test:
altering labels to length 60
Server: Msg 5074, Level 16, State 4, Line 5
The object 'ALTPART_ANNOT_ANNOTID_FK' is dependent on column 'label'.
I used this to bracket my script:
sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
go
sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
go
/* updates here */
sp_msforeachtable @.command1="print '?'",
@.command2="ALTER TABLE ? CHECK CONSTRAINT all"
go
sp_msforeachtable @.command1="print '?'",
@.command2="ALTER TABLE ? ENABLE TRIGGER all"
go
I guess the alter table nocheck constraint isn't disabling the fk's
completely?
Is there a way around this, or do I manually have to do the constraint
dropping/recreating?
Thanks
Jeff KishOn Wed, 17 May 2006 10:25:25 -0400, Jeff Kish wrote:
>Hi.
>I'm getting errors like this when I try to run an upgrade script I'm trying to
>write/test:
>altering labels to length 60
>Server: Msg 5074, Level 16, State 4, Line 5
>The object 'ALTPART_ANNOT_ANNOTID_FK' is dependent on column 'label'.
>I used this to bracket my script:
>sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
>go
>sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
>go
>/* updates here */
>
>sp_msforeachtable @.command1="print '?'",
>@.command2="ALTER TABLE ? CHECK CONSTRAINT all"
>go
>sp_msforeachtable @.command1="print '?'",
>@.command2="ALTER TABLE ? ENABLE TRIGGER all"
>go
>I guess the alter table nocheck constraint isn't disabling the fk's
>completely?
Hi Jeff,
ALTER TABLE xxx NOCHECK CONSTRAINT yyy is intended to (temporarily)
disable the checking of the constraint. The constraint is not removed
from the metadata. That means that you still can't perform any
modifications that would invalidate the constraint. (Coonsider what
would happpen if you change the datatype of a column on one end of a
FOREIGN KEY constraint but not on the other end and then try to
re-anable the constraint...)
>Is there a way around this, or do I manually have to do the constraint
>dropping/recreating?
If you google for it, you might be able to find scripts to generate the
code to drop and recreate constraints. I've never used any such code, so
I can't comment on the reliability.
--
Hugo Kornelis, SQL Server MVP|||Jeff Kish (jeff.kish@.mro.com) writes:
> I'm getting errors like this when I try to run an upgrade script I'm
> trying to write/test:
> altering labels to length 60
> Server: Msg 5074, Level 16, State 4, Line 5
> The object 'ALTPART_ANNOT_ANNOTID_FK' is dependent on column 'label'.
> I used this to bracket my script:
> sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
> go
> sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
> go
Since you did not include the actual code that implements the change,
I will have to guess. My guess is that you change the length of a
PK column that is referenced by an FK.
If that is the case, you indeed have to drop the FK, as an FK must
always be of the same data type as the key it refers to. SQL Server
cannot know that you are altering both columns, so it only sees that
you are breaking the rule.
> sp_msforeachtable @.command1="print '?'",
> @.command2="ALTER TABLE ? CHECK CONSTRAINT all"
When you reenable constraints, you should use this quirky syntax:
@.command2="ALTER TABLE ? WITH CHEC CHECK CONSTRAINT all"
This forces SQL Server to re-check the constraints. While this take
much longer time, it also means that the optimizer can trust these
constraints and take them in regard when computing a query plan. In
some situations this can have drastic effects on the performance
of the application.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||<snip>
>Since you did not include the actual code that implements the change,
>I will have to guess. My guess is that you change the length of a
>PK column that is referenced by an FK.
>If that is the case, you indeed have to drop the FK, as an FK must
>always be of the same data type as the key it refers to. SQL Server
>cannot know that you are altering both columns, so it only sees that
>you are breaking the rule.
>> sp_msforeachtable @.command1="print '?'",
>> @.command2="ALTER TABLE ? CHECK CONSTRAINT all"
>When you reenable constraints, you should use this quirky syntax:
> @.command2="ALTER TABLE ? WITH CHEC CHECK CONSTRAINT all"
>This forces SQL Server to re-check the constraints. While this take
>much longer time, it also means that the optimizer can trust these
>constraints and take them in regard when computing a query plan. In
>some situations this can have drastic effects on the performance
>of the application.
Thanks to both of you, not only for the quick accurate explanation, but also
the reenable recommendation.
I guess I got kind of spoiled by Oracle (I hope that isn't a dirty word here),
but I was able to get things to work better by dropping then re-creating the
constraints.
Yes, I was changing the length of one of the columns in the primary key .
I took some of Erland's other advice I saw elsewhere, and decided not to rely
on any automated tools, and just sat down and grunted through manually
figuring out and implementing the scripts.
regards,
Jeff Kishsql
Sunday, March 11, 2012
Alter Table
Simple SQL question.
hi ymk,
remove the brackets after add. just
alter table #billingdata add t2 float,t3 varchar(20) null
I am trying to add multiple columns to a temp table and the alter statement throws the following error.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.
The alter statement looks like this.
ALTER TABLE #BillingData ADD (T2 FLOAT, T3 varchar(20) NULL)
I thought you can add multiple columns putting the names in ( ).
Any ideas where I am doing wrong.
Thanks.
Quote:
Originally Posted by ymk
Simple SQL question.
I am trying to add multiple columns to a temp table and the alter statement throws the following error.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.
The alter statement looks like this.
ALTER TABLE #BillingData ADD (T2 FLOAT, T3 varchar(20) NULL)
I thought you can add multiple columns putting the names in ( ).
Any ideas where I am doing wrong.
Thanks.
hi ymk,
remove the brackets after add. just
alter table #billingdata add t2 float,t3 varchar(20) null
Saturday, February 25, 2012
ALTER COLUMN on an XML column type can give error Msg 511 after a few attempts
Basically I am trying to apply an XML Schema to an XML column after data has been added to the table. I need to do this to generate a computed column for use in an index to improve the access times. While I was playing with the schema getting the format/syntax correct I needed to apply and remove the schema several times and got errors. The following is how the errors can easily be generated rather than how I encountered them initially.
Software=Windows 2003 Server, SQL Server 2005
The database table used, without the schema, was...
CREATE TABLE [dbo].[EventXML](
[EventID] [INT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[XMLData] [XML] NULL,
[msrepl_tran_version] [UNIQUEIDENTIFIER] NOT NULL DEFAULT (newid())
)
The data comprises…
85308 rows, EventID length=4, XMLData length=908…5576, Msrepl_tran_version length=16
Database restored from scratch.
Create schema collection for XmlData column…
CREATE XML SCHEMA COLLECTION dbo.EventXML_XMLData_SchemaCollection AS
N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Event" >
<xsd:complexType>
<xsd:all>
<xsd:element name="categoryId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="formatStringId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventTypeId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventSubTypeId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventSubTypeStrId" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventDateTimeL" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventDateTimeU" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="behaviour" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="severity" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="changeId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="idHolderId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="operatorId" type="xsd:string" minOccurs="0" maxOccurs="1" />
<xsd:element name="doorId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="objectId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="oldStateValue" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="newStateValue" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="workstationId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="logonName" type="xsd:string" minOccurs="0" maxOccurs="1" />
<xsd:element name="identifierId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="readerId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="stationId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="Alarm" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
<xsd:all>
<xsd:element name="AlarmHistory" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
<xsd:choice>
<xsd:element name="AlarmHistoryItem" minOccurs="0" maxOccurs="unbounded" >
<xsd:complexType>
<xsd:attribute name="Action" type="xsd:string"/>
<xsd:attribute name="Time" type="xsd:string"/>
<xsd:attribute name="Operator" type="xsd:integer"/>
<xsd:attribute name="Workstation" type="xsd:integer"/>
<xsd:attribute name="PriorityThen" type="xsd:integer"/>
<xsd:attribute name="PriorityNow" type="xsd:integer"/>
<xsd:attribute name="StateThen" type="xsd:string"/>
<xsd:attribute name="StateNow" type="xsd:string"/>
<xsd:attribute name="ResolutionCode" type="xsd:integer"/>
<xsd:attribute name="Comment" type="xsd:string"/>
</xsd:complexType>
</xsd:element>
</xsd:choice>
</xsd:complexType>
</xsd:element>
<xsd:element name="ProcedureHistory" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
</xsd:complexType>
</xsd:element>
</xsd:all>
<xsd:attribute name="ID" type="xsd:integer"/>
<xsd:attribute name="Generated" type="xsd:string"/>
<xsd:attribute name="Reset" type="xsd:string"/>
<xsd:attribute name="State" type="xsd:integer"/>
<xsd:attribute name="Priority" type="xsd:integer"/>
<xsd:attribute name="AlarmTemplateID" type="xsd:integer"/>
<xsd:attribute name="AutoClear" type="xsd:string"/>
<xsd:attribute name="FormatStringID" type="xsd:integer"/>
<xsd:attribute name="ProcdeureTemplateID" type="xsd:integer"/>
<xsd:attribute name="Supervisor" type="xsd:string"/>
<xsd:attribute name="AlarmHandled" type="xsd:string"/>
<xsd:attribute name="ObjectId" type="xsd:integer"/>
</xsd:complexType>
</xsd:element>
</xsd:all>
<xsd:attribute name="ID" type="xsd:integer" use="required"/>
<xsd:attribute name="TypeName" type="xsd:string" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
GO
and then...
Modify XML column (XMLData) attributes...
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 28 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 62 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 71 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 81 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 106 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 78 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=Error in 293 seconds,
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 7th attempt and subsequent attempts.
Database restored from scratch and schema collection created, operations=
Modify XML column (XMLData) attributes
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 17 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 26 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 45 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 70 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 97 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result= Error in 199 seconds
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 6th attempt and subsequent attempts.
Restore Database as before and create schema collection as before
Modify XML column (XMLData) attributes
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 28 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 67 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 82 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 150 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result= Error in 236 seconds
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 5th attempt and subsequent attempts.
If you delete all the rows then the ALTER COLUMN completes without error.
A single row that exceeded 15000 bytes was added to the XML Data column of EventXML table without error.
All the errors that I could find that looked similar related either to SQL Server 2000 or SQL Server Mobile.
Hi Mark,
It seems like a bug in SQL2005. Do you mind to share your data with me so I can reproduce this problem at my side and fix it? You can contact me using this email jinghaol@.microsoft.com.
Thanks
Jinghao Liu
SQL Server Relational Engine XML team
|||Hi
FYI - I have encountered the same problem on one occasion with alter table. As it was just test data, I deleted the data and the alter then worked fine. I don't have a current repro
Just in case it helps, I also had the problem regularly when updating a column that was already typed against a collection
I created a separate filegroup and used "textimage on" to locate the XML data in the new filegroup
I have not had a problem with update since I used the new filegroup
|||Thanks for the reply. I am still interesting to know the real cause of the problem so we can fix it. You can contact me directly if you are able to repro the problem.
Thanks
Jinghao Liu - SQL Server Engine
|||Database information was sent to Jianghao Liu directly as requested. The following email was then received...
A bug has been filed for this problem. Thank you so much, Mark, for spend time to help us making SQL better!
Have a nice weekend!
Jinghao
|||
This reply has just been received from MSFT:
This bug has been resolved as “By Design”. SQL Server only allow altering a column certain amount of times. Alter column adds a column and removes a column. Once you've done this the space for the old column on the pages is not reclaimed.
If you are developing application base on continually ALTER COLUMN, then you need to redesign it.
Well thanks MSFT for that valuable input. Wouldn't it be nice to develop a large application where you know for definite what your schema is before you write a line of code like MSFT must do? This must be the "anti-Agile" methodology that MSFT use. For the rest of the world that don't get it exactly right first time, perhaps someone can explain that, given there are no verbs except ADD to alter a schema collection once it is applied to a column, how you are supposed to make changes without the use of ALTER COLUMN?
So this is "by design". I would love to have been at the design meeting where the requirement for "only allowing a column to be altered a certain number of times" was discussed. Do you think it was a "must have for release 1" or a "nice to have"?
And we wonder why people go anti-MSFT and turn to other platforms.
Anyway can't stop and chat, apparently I've got to go and redesign my app! .....I think I'll use Oracle!!
~swg
|||I agree with swg that this response is unacceptable.
I recognise that efforts should be made to minimise the occasions where an alter is needed - using up versions of the schemas if possible - which can be added without an alter. However, in the course of maintenance it seems almost unavoidable that alters will be needed
It appears that the only way currently available is to export the table contents into untyped XML so that you can drop the table and then recreate it with the column typed against the modified collection.
It looks like you have to assume that alter won't work if you are doing this sort of change in a production environment. I suggest that:
The minimum number of schemas needed are put into each collection - giving smaller collections|||
Hi!
Found a workaround. Good for solving other problems, too.
The mail problem with larga data fields is the fragmentation. The xml data column and the new nvarchar(max) should make the storage and indexes more optimal with the possibility of storing the value in-row or out-of-row.
Either way, after many updates your data pages can become fragmented, and the page usage can easily drop below 50% by using e.g xml columns (resultnig large data files).
The index pages can be optimized by rebuilding indexes.The data pages depend on the clustered indexes, so you should handle these types of issues by rebuilding the clustered index like any other indexes.
The same problem arises when modifying the xml schema on a column: the alter column makes the old column inactive but does not delete it from the occupied pages.
When rebuilding clustered index, the db engine reorders the data pages and the contained data, sorting out pages not needed any more: e.g the pages remained at last schema update.
Hope, I could help.
Bye:
Barrez
|||Thank you for your help
Regards
Mark Dooley
|||Hey thanks for this,
Brilliant piece of detective work.... stuff that MS should have offered actually. Presumably, if there isn't a clustered index on the table already, just adding one (and possibly dropping it again?) will achieve the same result?
I'll get Mark to mark your post as the Answer!
Cheers,
~swg
ALTER COLUMN on an XML column type can give error Msg 511 after a few attempts
Basically I am trying to apply an XML Schema to an XML column after data has been added to the table. I need to do this to generate a computed column for use in an index to improve the access times. While I was playing with the schema getting the format/syntax correct I needed to apply and remove the schema several times and got errors. The following is how the errors can easily be generated rather than how I encountered them initially.
Software=Windows 2003 Server, SQL Server 2005
The database table used, without the schema, was...
CREATE TABLE [dbo].[EventXML](
[EventID] [INT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[XMLData] [XML] NULL,
[msrepl_tran_version] [UNIQUEIDENTIFIER] NOT NULL DEFAULT (newid())
)
The data comprises…
85308 rows, EventID length=4, XMLData length=908…5576, Msrepl_tran_version length=16
Database restored from scratch.
Create schema collection for XmlData column…
CREATE XML SCHEMA COLLECTION dbo.EventXML_XMLData_SchemaCollection AS
N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Event" >
<xsd:complexType>
<xsd:all>
<xsd:element name="categoryId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="formatStringId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventTypeId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventSubTypeId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventSubTypeStrId" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventDateTimeL" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventDateTimeU" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="behaviour" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="severity" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="changeId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="idHolderId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="operatorId" type="xsd:string" minOccurs="0" maxOccurs="1" />
<xsd:element name="doorId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="objectId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="oldStateValue" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="newStateValue" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="workstationId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="logonName" type="xsd:string" minOccurs="0" maxOccurs="1" />
<xsd:element name="identifierId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="readerId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="stationId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="Alarm" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
<xsd:all>
<xsd:element name="AlarmHistory" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
<xsd:choice>
<xsd:element name="AlarmHistoryItem" minOccurs="0" maxOccurs="unbounded" >
<xsd:complexType>
<xsd:attribute name="Action" type="xsd:string"/>
<xsd:attribute name="Time" type="xsd:string"/>
<xsd:attribute name="Operator" type="xsd:integer"/>
<xsd:attribute name="Workstation" type="xsd:integer"/>
<xsd:attribute name="PriorityThen" type="xsd:integer"/>
<xsd:attribute name="PriorityNow" type="xsd:integer"/>
<xsd:attribute name="StateThen" type="xsd:string"/>
<xsd:attribute name="StateNow" type="xsd:string"/>
<xsd:attribute name="ResolutionCode" type="xsd:integer"/>
<xsd:attribute name="Comment" type="xsd:string"/>
</xsd:complexType>
</xsd:element>
</xsd:choice>
</xsd:complexType>
</xsd:element>
<xsd:element name="ProcedureHistory" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
</xsd:complexType>
</xsd:element>
</xsd:all>
<xsd:attribute name="ID" type="xsd:integer"/>
<xsd:attribute name="Generated" type="xsd:string"/>
<xsd:attribute name="Reset" type="xsd:string"/>
<xsd:attribute name="State" type="xsd:integer"/>
<xsd:attribute name="Priority" type="xsd:integer"/>
<xsd:attribute name="AlarmTemplateID" type="xsd:integer"/>
<xsd:attribute name="AutoClear" type="xsd:string"/>
<xsd:attribute name="FormatStringID" type="xsd:integer"/>
<xsd:attribute name="ProcdeureTemplateID" type="xsd:integer"/>
<xsd:attribute name="Supervisor" type="xsd:string"/>
<xsd:attribute name="AlarmHandled" type="xsd:string"/>
<xsd:attribute name="ObjectId" type="xsd:integer"/>
</xsd:complexType>
</xsd:element>
</xsd:all>
<xsd:attribute name="ID" type="xsd:integer" use="required"/>
<xsd:attribute name="TypeName" type="xsd:string" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
GO
and then...
Modify XML column (XMLData) attributes...
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 28 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 62 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 71 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 81 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 106 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 78 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=Error in 293 seconds,
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 7th attempt and subsequent attempts.
Database restored from scratch and schema collection created, operations=
Modify XML column (XMLData) attributes
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 17 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 26 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 45 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 70 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 97 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result= Error in 199 seconds
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 6th attempt and subsequent attempts.
Restore Database as before and create schema collection as before
Modify XML column (XMLData) attributes
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 28 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 67 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 82 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 150 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result= Error in 236 seconds
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 5th attempt and subsequent attempts.
If you delete all the rows then the ALTER COLUMN completes without error.
A single row that exceeded 15000 bytes was added to the XML Data column of EventXML table without error.
All the errors that I could find that looked similar related either to SQL Server 2000 or SQL Server Mobile.
Hi Mark,
It seems like a bug in SQL2005. Do you mind to share your data with me so I can reproduce this problem at my side and fix it? You can contact me using this email jinghaol@.microsoft.com.
Thanks
Jinghao Liu
SQL Server Relational Engine XML team
|||Hi
FYI - I have encountered the same problem on one occasion with alter table. As it was just test data, I deleted the data and the alter then worked fine. I don't have a current repro
Just in case it helps, I also had the problem regularly when updating a column that was already typed against a collection
I created a separate filegroup and used "textimage on" to locate the XML data in the new filegroup
I have not had a problem with update since I used the new filegroup
|||Thanks for the reply. I am still interesting to know the real cause of the problem so we can fix it. You can contact me directly if you are able to repro the problem.
Thanks
Jinghao Liu - SQL Server Engine
|||Database information was sent to Jianghao Liu directly as requested. The following email was then received...
A bug has been filed for this problem. Thank you so much, Mark, for spend time to help us making SQL better!
Have a nice weekend!
Jinghao
|||
This reply has just been received from MSFT:
This bug has been resolved as “By Design”. SQL Server only allow altering a column certain amount of times. Alter column adds a column and removes a column. Once you've done this the space for the old column on the pages is not reclaimed.
If you are developing application base on continually ALTER COLUMN, then you need to redesign it.
Well thanks MSFT for that valuable input. Wouldn't it be nice to develop a large application where you know for definite what your schema is before you write a line of code like MSFT must do? This must be the "anti-Agile" methodology that MSFT use. For the rest of the world that don't get it exactly right first time, perhaps someone can explain that, given there are no verbs except ADD to alter a schema collection once it is applied to a column, how you are supposed to make changes without the use of ALTER COLUMN?
So this is "by design". I would love to have been at the design meeting where the requirement for "only allowing a column to be altered a certain number of times" was discussed. Do you think it was a "must have for release 1" or a "nice to have"?
And we wonder why people go anti-MSFT and turn to other platforms.
Anyway can't stop and chat, apparently I've got to go and redesign my app! .....I think I'll use Oracle!!
~swg
|||I agree with swg that this response is unacceptable.
I recognise that efforts should be made to minimise the occasions where an alter is needed - using up versions of the schemas if possible - which can be added without an alter. However, in the course of maintenance it seems almost unavoidable that alters will be needed
It appears that the only way currently available is to export the table contents into untyped XML so that you can drop the table and then recreate it with the column typed against the modified collection.
It looks like you have to assume that alter won't work if you are doing this sort of change in a production environment. I suggest that:
The minimum number of schemas needed are put into each collection - giving smaller collections|||
Hi!
Found a workaround. Good for solving other problems, too.
The mail problem with larga data fields is the fragmentation. The xml data column and the new nvarchar(max) should make the storage and indexes more optimal with the possibility of storing the value in-row or out-of-row.
Either way, after many updates your data pages can become fragmented, and the page usage can easily drop below 50% by using e.g xml columns (resultnig large data files).
The index pages can be optimized by rebuilding indexes.The data pages depend on the clustered indexes, so you should handle these types of issues by rebuilding the clustered index like any other indexes.
The same problem arises when modifying the xml schema on a column: the alter column makes the old column inactive but does not delete it from the occupied pages.
When rebuilding clustered index, the db engine reorders the data pages and the contained data, sorting out pages not needed any more: e.g the pages remained at last schema update.
Hope, I could help.
Bye:
Barrez
|||Thank you for your help
Regards
Mark Dooley
|||Hey thanks for this,
Brilliant piece of detective work.... stuff that MS should have offered actually. Presumably, if there isn't a clustered index on the table already, just adding one (and possibly dropping it again?) will achieve the same result?
I'll get Mark to mark your post as the Answer!
Cheers,
~swg
ALTER COLUMN on an XML column type can give error Msg 511 after a few attempts
Basically I am trying to apply an XML Schema to an XML column after data has been added to the table. I need to do this to generate a computed column for use in an index to improve the access times. While I was playing with the schema getting the format/syntax correct I needed to apply and remove the schema several times and got errors. The following is how the errors can easily be generated rather than how I encountered them initially.
Software=Windows 2003 Server, SQL Server 2005
The database table used, without the schema, was...
CREATE TABLE [dbo].[EventXML](
[EventID] [INT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[XMLData] [XML] NULL,
[msrepl_tran_version] [UNIQUEIDENTIFIER] NOT NULL DEFAULT (newid())
)
The data comprises…
85308 rows, EventID length=4, XMLData length=908…5576, Msrepl_tran_version length=16
Database restored from scratch.
Create schema collection for XmlData column…
CREATE XML SCHEMA COLLECTION dbo.EventXML_XMLData_SchemaCollection AS
N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Event" >
<xsd:complexType>
<xsd:all>
<xsd:element name="categoryId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="formatStringId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventTypeId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventSubTypeId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventSubTypeStrId" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventDateTimeL" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventDateTimeU" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="behaviour" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="severity" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="changeId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="idHolderId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="operatorId" type="xsd:string" minOccurs="0" maxOccurs="1" />
<xsd:element name="doorId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="objectId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="oldStateValue" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="newStateValue" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="workstationId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="logonName" type="xsd:string" minOccurs="0" maxOccurs="1" />
<xsd:element name="identifierId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="readerId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="stationId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="Alarm" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
<xsd:all>
<xsd:element name="AlarmHistory" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
<xsd:choice>
<xsd:element name="AlarmHistoryItem" minOccurs="0" maxOccurs="unbounded" >
<xsd:complexType>
<xsd:attribute name="Action" type="xsd:string"/>
<xsd:attribute name="Time" type="xsd:string"/>
<xsd:attribute name="Operator" type="xsd:integer"/>
<xsd:attribute name="Workstation" type="xsd:integer"/>
<xsd:attribute name="PriorityThen" type="xsd:integer"/>
<xsd:attribute name="PriorityNow" type="xsd:integer"/>
<xsd:attribute name="StateThen" type="xsd:string"/>
<xsd:attribute name="StateNow" type="xsd:string"/>
<xsd:attribute name="ResolutionCode" type="xsd:integer"/>
<xsd:attribute name="Comment" type="xsd:string"/>
</xsd:complexType>
</xsd:element>
</xsd:choice>
</xsd:complexType>
</xsd:element>
<xsd:element name="ProcedureHistory" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
</xsd:complexType>
</xsd:element>
</xsd:all>
<xsd:attribute name="ID" type="xsd:integer"/>
<xsd:attribute name="Generated" type="xsd:string"/>
<xsd:attribute name="Reset" type="xsd:string"/>
<xsd:attribute name="State" type="xsd:integer"/>
<xsd:attribute name="Priority" type="xsd:integer"/>
<xsd:attribute name="AlarmTemplateID" type="xsd:integer"/>
<xsd:attribute name="AutoClear" type="xsd:string"/>
<xsd:attribute name="FormatStringID" type="xsd:integer"/>
<xsd:attribute name="ProcdeureTemplateID" type="xsd:integer"/>
<xsd:attribute name="Supervisor" type="xsd:string"/>
<xsd:attribute name="AlarmHandled" type="xsd:string"/>
<xsd:attribute name="ObjectId" type="xsd:integer"/>
</xsd:complexType>
</xsd:element>
</xsd:all>
<xsd:attribute name="ID" type="xsd:integer" use="required"/>
<xsd:attribute name="TypeName" type="xsd:string" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
GO
and then...
Modify XML column (XMLData) attributes...
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 28 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 62 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 71 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 81 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 106 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 78 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=Error in 293 seconds,
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 7th attempt and subsequent attempts.
Database restored from scratch and schema collection created, operations=
Modify XML column (XMLData) attributes
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 17 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 26 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 45 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 70 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 97 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result= Error in 199 seconds
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 6th attempt and subsequent attempts.
Restore Database as before and create schema collection as before
Modify XML column (XMLData) attributes
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 28 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 67 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 82 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 150 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result= Error in 236 seconds
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 5th attempt and subsequent attempts.
If you delete all the rows then the ALTER COLUMN completes without error.
A single row that exceeded 15000 bytes was added to the XML Data column of EventXML table without error.
All the errors that I could find that looked similar related either to SQL Server 2000 or SQL Server Mobile.
Hi Mark,
It seems like a bug in SQL2005. Do you mind to share your data with me so I can reproduce this problem at my side and fix it? You can contact me using this email jinghaol@.microsoft.com.
Thanks
Jinghao Liu
SQL Server Relational Engine XML team
|||Hi
FYI - I have encountered the same problem on one occasion with alter table. As it was just test data, I deleted the data and the alter then worked fine. I don't have a current repro
Just in case it helps, I also had the problem regularly when updating a column that was already typed against a collection
I created a separate filegroup and used "textimage on" to locate the XML data in the new filegroup
I have not had a problem with update since I used the new filegroup
|||Thanks for the reply. I am still interesting to know the real cause of the problem so we can fix it. You can contact me directly if you are able to repro the problem.
Thanks
Jinghao Liu - SQL Server Engine
|||Database information was sent to Jianghao Liu directly as requested. The following email was then received...
A bug has been filed for this problem. Thank you so much, Mark, for spend time to help us making SQL better!
Have a nice weekend!
Jinghao
|||
This reply has just been received from MSFT:
This bug has been resolved as “By Design”. SQL Server only allow altering a column certain amount of times. Alter column adds a column and removes a column. Once you've done this the space for the old column on the pages is not reclaimed.
If you are developing application base on continually ALTER COLUMN, then you need to redesign it.
Well thanks MSFT for that valuable input. Wouldn't it be nice to develop a large application where you know for definite what your schema is before you write a line of code like MSFT must do? This must be the "anti-Agile" methodology that MSFT use. For the rest of the world that don't get it exactly right first time, perhaps someone can explain that, given there are no verbs except ADD to alter a schema collection once it is applied to a column, how you are supposed to make changes without the use of ALTER COLUMN?
So this is "by design". I would love to have been at the design meeting where the requirement for "only allowing a column to be altered a certain number of times" was discussed. Do you think it was a "must have for release 1" or a "nice to have"?
And we wonder why people go anti-MSFT and turn to other platforms.
Anyway can't stop and chat, apparently I've got to go and redesign my app! .....I think I'll use Oracle!!
~swg
|||I agree with swg that this response is unacceptable.
I recognise that efforts should be made to minimise the occasions where an alter is needed - using up versions of the schemas if possible - which can be added without an alter. However, in the course of maintenance it seems almost unavoidable that alters will be needed
It appears that the only way currently available is to export the table contents into untyped XML so that you can drop the table and then recreate it with the column typed against the modified collection.
It looks like you have to assume that alter won't work if you are doing this sort of change in a production environment. I suggest that:
The minimum number of schemas needed are put into each collection - giving smaller collections|||Hi!
Found a workaround. Good for solving other problems, too.
The mail problem with larga data fields is the fragmentation. The xml data column and the new nvarchar(max) should make the storage and indexes more optimal with the possibility of storing the value in-row or out-of-row.
Either way, after many updates your data pages can become fragmented, and the page usage can easily drop below 50% by using e.g xml columns (resultnig large data files).
The index pages can be optimized by rebuilding indexes.The data pages depend on the clustered indexes, so you should handle these types of issues by rebuilding the clustered index like any other indexes.
The same problem arises when modifying the xml schema on a column: the alter column makes the old column inactive but does not delete it from the occupied pages.
When rebuilding clustered index, the db engine reorders the data pages and the contained data, sorting out pages not needed any more: e.g the pages remained at last schema update.
Hope, I could help.
Bye:
Barrez
|||Thank you for your help
Regards
Mark Dooley
|||Hey thanks for this,
Brilliant piece of detective work.... stuff that MS should have offered actually. Presumably, if there isn't a clustered index on the table already, just adding one (and possibly dropping it again?) will achieve the same result?
I'll get Mark to mark your post as the Answer!
Cheers,
~swg
ALTER COLUMN on an XML column type can give error Msg 511 after a few attempts
Basically I am trying to apply an XML Schema to an XML column after data has been added to the table. I need to do this to generate a computed column for use in an index to improve the access times. While I was playing with the schema getting the format/syntax correct I needed to apply and remove the schema several times and got errors. The following is how the errors can easily be generated rather than how I encountered them initially.
Software=Windows 2003 Server, SQL Server 2005
The database table used, without the schema, was...
CREATE TABLE [dbo].[EventXML](
[EventID] [INT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[XMLData] [XML] NULL,
[msrepl_tran_version] [UNIQUEIDENTIFIER] NOT NULL DEFAULT (newid())
)
The data comprises…
85308 rows, EventID length=4, XMLData length=908…5576, Msrepl_tran_version length=16
Database restored from scratch.
Create schema collection for XmlData column…
CREATE XML SCHEMA COLLECTION dbo.EventXML_XMLData_SchemaCollection AS
N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Event" >
<xsd:complexType>
<xsd:all>
<xsd:element name="categoryId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="formatStringId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventTypeId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventSubTypeId" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventSubTypeStrId" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventDateTimeL" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="eventDateTimeU" type="xsd:string" minOccurs="1" maxOccurs="1" />
<xsd:element name="behaviour" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="severity" type="xsd:integer" minOccurs="1" maxOccurs="1" />
<xsd:element name="changeId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="idHolderId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="operatorId" type="xsd:string" minOccurs="0" maxOccurs="1" />
<xsd:element name="doorId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="objectId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="oldStateValue" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="newStateValue" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="workstationId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="logonName" type="xsd:string" minOccurs="0" maxOccurs="1" />
<xsd:element name="identifierId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="readerId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="stationId" type="xsd:integer" minOccurs="0" maxOccurs="1" />
<xsd:element name="Alarm" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
<xsd:all>
<xsd:element name="AlarmHistory" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
<xsd:choice>
<xsd:element name="AlarmHistoryItem" minOccurs="0" maxOccurs="unbounded" >
<xsd:complexType>
<xsd:attribute name="Action" type="xsd:string"/>
<xsd:attribute name="Time" type="xsd:string"/>
<xsd:attribute name="Operator" type="xsd:integer"/>
<xsd:attribute name="Workstation" type="xsd:integer"/>
<xsd:attribute name="PriorityThen" type="xsd:integer"/>
<xsd:attribute name="PriorityNow" type="xsd:integer"/>
<xsd:attribute name="StateThen" type="xsd:string"/>
<xsd:attribute name="StateNow" type="xsd:string"/>
<xsd:attribute name="ResolutionCode" type="xsd:integer"/>
<xsd:attribute name="Comment" type="xsd:string"/>
</xsd:complexType>
</xsd:element>
</xsd:choice>
</xsd:complexType>
</xsd:element>
<xsd:element name="ProcedureHistory" minOccurs="0" maxOccurs="1" >
<xsd:complexType>
</xsd:complexType>
</xsd:element>
</xsd:all>
<xsd:attribute name="ID" type="xsd:integer"/>
<xsd:attribute name="Generated" type="xsd:string"/>
<xsd:attribute name="Reset" type="xsd:string"/>
<xsd:attribute name="State" type="xsd:integer"/>
<xsd:attribute name="Priority" type="xsd:integer"/>
<xsd:attribute name="AlarmTemplateID" type="xsd:integer"/>
<xsd:attribute name="AutoClear" type="xsd:string"/>
<xsd:attribute name="FormatStringID" type="xsd:integer"/>
<xsd:attribute name="ProcdeureTemplateID" type="xsd:integer"/>
<xsd:attribute name="Supervisor" type="xsd:string"/>
<xsd:attribute name="AlarmHandled" type="xsd:string"/>
<xsd:attribute name="ObjectId" type="xsd:integer"/>
</xsd:complexType>
</xsd:element>
</xsd:all>
<xsd:attribute name="ID" type="xsd:integer" use="required"/>
<xsd:attribute name="TypeName" type="xsd:string" use="required"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
GO
and then...
Modify XML column (XMLData) attributes...
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 28 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 62 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 71 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 81 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 106 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML(CONTENT dbo.EventXML_XMLData_SchemaCollection) NULL
GO
Result=OK in 78 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=Error in 293 seconds,
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 7th attempt and subsequent attempts.
Database restored from scratch and schema collection created, operations=
Modify XML column (XMLData) attributes
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 17 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 26 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 45 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 70 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 97 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result= Error in 199 seconds
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 6th attempt and subsequent attempts.
Restore Database as before and create schema collection as before
Modify XML column (XMLData) attributes
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 28 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 67 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 82 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result=OK in 150 seconds
ALTER TABLE dbo.EventXML ALTER COLUMN XmlData XML NULL
GO
Result= Error in 236 seconds
Message=
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.
The statement has been terminated.
Error on the 5th attempt and subsequent attempts.
If you delete all the rows then the ALTER COLUMN completes without error.
A single row that exceeded 15000 bytes was added to the XML Data column of EventXML table without error.
All the errors that I could find that looked similar related either to SQL Server 2000 or SQL Server Mobile.
Hi Mark,
It seems like a bug in SQL2005. Do you mind to share your data with me so I can reproduce this problem at my side and fix it? You can contact me using this email jinghaol@.microsoft.com.
Thanks
Jinghao Liu
SQL Server Relational Engine XML team
|||Hi
FYI - I have encountered the same problem on one occasion with alter table. As it was just test data, I deleted the data and the alter then worked fine. I don't have a current repro
Just in case it helps, I also had the problem regularly when updating a column that was already typed against a collection
I created a separate filegroup and used "textimage on" to locate the XML data in the new filegroup
I have not had a problem with update since I used the new filegroup
|||Thanks for the reply. I am still interesting to know the real cause of the problem so we can fix it. You can contact me directly if you are able to repro the problem.
Thanks
Jinghao Liu - SQL Server Engine
|||Database information was sent to Jianghao Liu directly as requested. The following email was then received...
A bug has been filed for this problem. Thank you so much, Mark, for spend time to help us making SQL better!
Have a nice weekend!
Jinghao
|||
This reply has just been received from MSFT:
This bug has been resolved as “By Design”. SQL Server only allow altering a column certain amount of times. Alter column adds a column and removes a column. Once you've done this the space for the old column on the pages is not reclaimed.
If you are developing application base on continually ALTER COLUMN, then you need to redesign it.
Well thanks MSFT for that valuable input. Wouldn't it be nice to develop a large application where you know for definite what your schema is before you write a line of code like MSFT must do? This must be the "anti-Agile" methodology that MSFT use. For the rest of the world that don't get it exactly right first time, perhaps someone can explain that, given there are no verbs except ADD to alter a schema collection once it is applied to a column, how you are supposed to make changes without the use of ALTER COLUMN?
So this is "by design". I would love to have been at the design meeting where the requirement for "only allowing a column to be altered a certain number of times" was discussed. Do you think it was a "must have for release 1" or a "nice to have"?
And we wonder why people go anti-MSFT and turn to other platforms.
Anyway can't stop and chat, apparently I've got to go and redesign my app! .....I think I'll use Oracle!!
~swg
|||I agree with swg that this response is unacceptable.
I recognise that efforts should be made to minimise the occasions where an alter is needed - using up versions of the schemas if possible - which can be added without an alter. However, in the course of maintenance it seems almost unavoidable that alters will be needed
It appears that the only way currently available is to export the table contents into untyped XML so that you can drop the table and then recreate it with the column typed against the modified collection.
It looks like you have to assume that alter won't work if you are doing this sort of change in a production environment. I suggest that:
The minimum number of schemas needed are put into each collection - giving smaller collections|||
Hi!
Found a workaround. Good for solving other problems, too.
The mail problem with larga data fields is the fragmentation. The xml data column and the new nvarchar(max) should make the storage and indexes more optimal with the possibility of storing the value in-row or out-of-row.
Either way, after many updates your data pages can become fragmented, and the page usage can easily drop below 50% by using e.g xml columns (resultnig large data files).
The index pages can be optimized by rebuilding indexes.The data pages depend on the clustered indexes, so you should handle these types of issues by rebuilding the clustered index like any other indexes.
The same problem arises when modifying the xml schema on a column: the alter column makes the old column inactive but does not delete it from the occupied pages.
When rebuilding clustered index, the db engine reorders the data pages and the contained data, sorting out pages not needed any more: e.g the pages remained at last schema update.
Hope, I could help.
Bye:
Barrez
|||Thank you for your help
Regards
Mark Dooley
|||Hey thanks for this,
Brilliant piece of detective work.... stuff that MS should have offered actually. Presumably, if there isn't a clustered index on the table already, just adding one (and possibly dropping it again?) will achieve the same result?
I'll get Mark to mark your post as the Answer!
Cheers,
~swg
Subscribe to:
Posts (Atom)