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
March 18, 2014 at 2:28 am

This was removed by the editor as SPAM
March 18, 2014 at 6:59 am

This was removed by the editor as SPAM

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

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

free_mascot One Orange Chip Points: 27169 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: 104773 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.