alter table datatype giving error date to bit

Question

alter table datatype giving error date to bit

mpradeep23

SSCommitted

Points: 1868
More actions
November 20, 2014 at 12:06 am

#292579

IF EXISTS (SELECT * FROM SYS.COLUMNS WHERE NAME =N'Case_Escalated__c' AND OBJECT_ID = OBJECT_ID(N'[Case]'))
begin
alter table [Case] alter column Case_Escalated__c bit null
print 'Case_Escalated__c altered sucessfully for [Case]'
end
Msg 1792, Level 16, State 1, Line 3
Alter table 'Case' failed because the added fixed column might cause existing data to go beyond the maximum allowable table row size of 8060 bytes.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Your table is too wide. The fixed length columns in a table (data types other than varchar/nvarchar) cannot exceed 8060 bytes total. Your table is so large that adding the extra column will take it over that limit.

You need to refactor this table, either reduce the size of existing columns, remove unused columns or split the table into multiple tables.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

GilaMonster (11/20/2014)
Your table is too wide. The fixed length columns in a table (data types other than varchar/nvarchar) cannot exceed 8060 bytes total. Your table is so large that adding the extra column will take it over that limit.
You need to refactor this table, either reduce the size of existing columns, remove unused columns or split the table into multiple tables.

The strange thing is that he is not adding a column, he's changing a current column. To a very small data type nonetheless.

@mpradeep23: what is the original data type?

Maybe you can copy the data into a new table with the correct data type, drop the old one and rename the new one.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Koen Verbeeck (11/20/2014)
GilaMonster (11/20/2014)
Your table is too wide. The fixed length columns in a table (data types other than varchar/nvarchar) cannot exceed 8060 bytes total. Your table is so large that adding the extra column will take it over that limit.
You need to refactor this table, either reduce the size of existing columns, remove unused columns or split the table into multiple tables.
The strange thing is that he is not adding a column, he's changing a current column. To a very small data type nonetheless.

Could be changing it from variable length to fixed length, could be changing it from not null to null (perhaps necessitating an increase in the size of the null bitmap). Could be changing a computed column to a real column. Can't tell without the table structure.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

would have thought the biggest issue here is that conversion is not allowed from type date to type bit!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉