Sql Server Problem

[lothos12345]

I have written I stored procedure and it works but it is giving me warnings. The warnings it is giving me are as follows:

Warning: The table '#HEADERINFO' has been created but its maximum row size (8435) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

(1 row(s) affected)


(3 row(s) affected)

Warning: The table '#HEADERINFO' has been created but its maximum row size (8435) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

(17 row(s) affected)

Warning: The table '#HEADERINFO' has been created but its maximum row size (8435) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

Warning: The table '#MasterTable' has been created but its maximum row size (10444) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

(17 row(s) affected)

Warning: The table '#HEADERINFO' has been created but its maximum row size (8435) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

Warning: The table '#MasterTable' has been created but its maximum row size (10444) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

Warning: The table '#HEADERINFO' has been created but its maximum row size (8435) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

Warning: The table '#MasterTable' has been created but its maximum row size (10444) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

(17 row(s) affected)

Any help with this warnings would be greatly appreciated.

 

[mrdenny]

Any table must be less than 8060 characters wide (including temp tables). The tables listed are wider than 8060 characters. Reduce the column widths so that the tables are less than 8060 characters wide.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[lothos12345]

None of my columns are greater than that here is the sql I used to create one of the tables that is giving me the error.

--Retrieve the Header and footer information.
CREATE TABLE #HEADERINFO
(
PatientName varchar(100),
PatientSSN varchar(20),
Diags varchar(1000),
PatientId int,
PatientSex varchar(10),
ChiefComplaint varchar(1000),
PriorHx varchar(1000),
Medication varchar(1000),
OtherRx varchar(1000),
DOB datetime,
DOI datetime,
NextVisit datetime,
Doctor varchar(100),
TreatmentCount int,
TreatmentDates varchar(100),
PrintSig int,
PhysicianComment int,
ReportFooter varchar(1000),
ReportHeader varchar(1000),
SigBlock varchar(1000),
DoctorToUse int,
DateField datetime,
ptid int
)

None of my columns are over 1000?

 

[earthandfire]

BOL clearly explains the situation under Create Table and the table structure you posted exceeds the 8060 maximum.

 

[harebrain]

It isn't the individual column size, it's the sum of their sizes. You've been rather generous in your allocations, e.g., SSN needs 20 chars and PatientSex needs 10? Do a more realistic evaluation of what the columns will contain and you'll easily be under the limit. A table definition with so many 1000-character-width columns screams LAZY.

 

[SQLBill]

You have 7 columns of VARCHAR(1000), right there that's 7000 and all the rest of the columns have to add up to 1060 or less.

-SQLBill

Posting advice: FAQ481-4875

 

[lothos12345]

I modeled the temp table after an actual table in the sql database, why can an actual table be created in sql server with the same column settings but I get warnings on the creation of the temp table?

 

[gmmastros]

You can create the table, you just can't fill every field with data. If the combined data in the row is less than 8060 bytes, you will not get errors when inserted or updating data. However, if the size of the data combined exceeds 8060 bytes (either through insert or update), you will get an error.

To see for yourself, run each line seperately in query analyzer.

Create table #Temp(id int, Data1 VarChar(8000), Data2 VarChar(8000), Data3 VarChar(8000))

Insert Into #Temp Values(1, Replicate('a', 8000), Replicate('a', 8000), Replicate('a', 8000))

Insert Into #Temp(id, Data1) Values(2, Replicate('a', 8000))
Update #Temp Set Data2 = Replicate('a', 8000) Where Id = 2


Drop Table #Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

To clarify what everyone is saying above:

When SQL looks at a table in an effort to determine how big it is, it always takes the maximum byte size of the variable length characters adding them to the byte sizes of all the other columns. Not only that, but variable length fields ALWAYS have additional overhead in order to make them variable length. So, not only do you have a total byte size of 8386 for your current structure (4 bytes for each INT datatype and 8 bytes for each DATETIME datatype), but you have at least 1 or 2 bytes extra per varchar field so that SQL can make the field dynamic in size.

At least that's the way I learned how datatypes work. In any case, think of this like a glass. You have an 8 oz glass which can supposedly hold 8 ozs of liquid. You try to pour 12 ozs into the glass instead. What's going to happen?

SQL doesn't like spillage, though, or overflow. So, just like a robot monitoring the glass, if it senses you're going to pour in more than 8 ozs, the robot will hide the glass (or at least cover it up) to prevent you from spilling your liquid all over the counter.

Does that help?


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[lothos12345]

Yes it does thanks for the help.

 

[SQLDenis]

The reason taht you can not store more than 8060 bytes in a row is because SQL server stores its data in 8k pages (8 of those are 1 extent)

however you will have no problem doing this in SQL server 2005

Create table #Temp(id int, Data1 VarChar(8000), Data2 VarChar(8000), Data3 VarChar(8000))

Insert Into #Temp Values(1, Replicate('a', 8000), Replicate('a', 8000), Replicate('a', 8000))


(1 row(s) affected)

But you should normalize your design and put this into 4 tables or so

the goal is to fit as many rows into a page as possible since IO is the most expensive operation

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/