Permitted no of bytes per row

Question

Permitted no of bytes per row

Anoo S Pillai

Mr or Mrs. 500

Points: 547
More actions
March 17, 2014 at 8:17 pm

#284743

Comments posted to this topic are about the item Permitted no of bytes per row

Viewing 15 posts - 1 through 15 (of 16 total)

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

free_mascot One Orange Chip Points: 27164 More actions · Answer 1

Easy one. Thanks Anoo.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Hany Helmy SSChampion Points: 13921 More actions · Answer 2

Good question, almost got it wrong, but @ the last second noticed that it`s Varchar not Char in the 2nd table 🙂

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

Nice and easy question - thanks.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Umer Akram SSC-Insane Points: 22209 More actions · Answer 4

Umer Akram

SSC-Insane

Points: 22209

March 18, 2014 at 7:10 am

#1697985

nice question . thanks for share

timwell SSCertifiable Points: 5315 More actions · Answer 5

When I ran the script I got the following:

Msg 1701, Level 16, State 1, Line 1

Creating or altering table '#cTab' failed because the minimum row size would be 16011, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

Did I miss something?

@@Version: Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) Jun 11 2012 16:41:53 Copyright (c) Microsoft Corporation Web Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Thomas Abraham SSChampion Points: 10761 More actions · Answer 6

timwell (3/18/2014)
When I ran the script I got the following:
Msg 1701, Level 16, State 1, Line 1
Creating or altering table '#cTab' failed because the minimum row size would be 16011, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.
Did I miss something?
@@Version: Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) Jun 11 2012 16:41:53 Copyright (c) Microsoft Corporation Web Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

You didn't miss anything. The second create succeeds. You don't get a message that tells you if things work. 😉

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

PChiragS SSCarpal Tunnel Points: 4965 More actions · Answer 7

PChiragS

SSCarpal Tunnel

Points: 4965

March 18, 2014 at 7:59 am

#1698019

nice and easy..

timwell SSCertifiable Points: 5315 More actions · Answer 8

Thomas Abraham (3/18/2014)
timwell (3/18/2014)
When I ran the script I got the following:
Msg 1701, Level 16, State 1, Line 1
Creating or altering table '#cTab' failed because the minimum row size would be 16011, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.
Did I miss something?
@@Version: Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) Jun 11 2012 16:41:53 Copyright (c) Microsoft Corporation Web Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
You didn't miss anything. The second create succeeds. You don't get a message that tells you if things work. 😉

Then that's what I missed. Should have read the explanation more thoroughly... 🙂

robert_verell Say Hey Kid Points: 668 More actions · Answer 9

as usual, i read the question too fast and made assumptions

Ken SSCoach Points: 17191 More actions · Answer 10

Stewart "Arturius" Campbell (3/18/2014)
crussell-931424 (3/18/2014)
But what happens when you fill those varchar fields with data?
the insert / update will fail...

Actually, I am not seeing a failure when the varchar columns are filled

insert into #vcTab

select 1,

REPLICATE('A',8000),

REPLICATE('B',8000)

;

select len(n1),

len(vc1),

len(vc2)

from #vcTab

;

select * from #vcTab

TomThomson SSC Guru Points: 104768 More actions · Answer 11

KWymore (3/18/2014)
Stewart "Arturius" Campbell (3/18/2014)
crussell-931424 (3/18/2014)
But what happens when you fill those varchar fields with data?
the insert / update will fail...
Actually, I am not seeing a failure when the varchar columns are filled

There's no reason why you should. The explanation makes it quite clear that any offending field will be moved into the overflow area; or maybe it doesn't - it appears to say that only the longest such field will be moved, which is certainly not true.

A nice question, somewhat spoiled by a careless explanation.

Tom

Anoo S Pillai Mr or Mrs. 500 Points: 547 More actions · Answer 12

.........

A nice question, somewhat spoiled by a careless explanation.

You are setting a tough target Tom, Copied the explanation from BOL for un-ambiguity. Sad to see that it didn't work. Could you please ponder some ideas on a good explanation from SSC point of view.

Umer Akram SSC-Insane Points: 22209 More actions · Answer 13

Anoo S Pillai (3/18/2014)
.........
A nice question, somewhat spoiled by a careless explanation.
You are setting a tough target Tom, Copied the explanation from BOL for un-ambiguity. Sad to see that it didn't work. Could you please ponder some ideas on a good explanation from SSC point of view.

+1

TomThomson SSC Guru Points: 104768 More actions · Answer 14

Anoo S Pillai (3/18/2014)
.........
A nice question, somewhat spoiled by a careless explanation.
You are setting a tough target Tom, Copied the explanation from BOL for un-ambiguity. Sad to see that it didn't work. Could you please ponder some ideas on a good explanation from SSC point of view.

Unfortunately BOL is sometimes not as clear or as accurate as it ought to be.

I think that "moves the record column with the largest width to another page in the ROW_OVERFLOW_DATA allocation unit, while maintaining a 24-byte pointer on the original page" could usefully be changed to read something along the lines of "moves columns to pages in the ROW_OVERFLOW_DATA allocation unit in order of decreasing column length, leaving 24-byte pointers in the original page, starting with the column with greatest width and stopping when the the resulting row with pointers is less than 8061 bytes". But maybe there's a shorter way to say it, or maye just leaving the original and adding "and repeats this with the widest column remaining in the row until the row is short enough" would be better.

Don't take "somewhat spoiled" as being anything like as strong as "spoiled" on its own, by the way. When I saw your reply I realised that I'm not sure whether "somewhat" changes meaning from place to place or not, would have expressed it differently if I'd realised that when I was writing it.

Tom