November 19, 2009 at 12:53 pm
One of my table has a row size more than 8090 bytes. Do you think it's a issue because I have read somewhere that row size should not be more than 8090 bytes. I am varchar(max), image type in my table.
Following query to get the size of the row.
SELECT a.name,sum(a.max_length)as Bytes
FROM (
SELECT ST.NAME,SC.MAX_LENGTH
FROM SYS.COLUMNS sc inner join sys.tables st on sc.object_id = st.object_id
UNION ALL
SELECT ST.NAME,SC.MAX_LENGTH
FROM SYS.INDEXES SI INNER JOIN SYS.TABLES ST ON SI.OBJECT_ID = ST.OBJECT_ID
inner join sys.columns sc on sc.object_id = st.object_id
inner join sys.index_columns sic on sc.object_id = sic.object_id
and sc.column_id = sic.column_id ) a
group by a.name
having sum(a.max_length) > 8000
order by a.name
November 19, 2009 at 1:06 pm
Post the table definition
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
November 19, 2009 at 1:10 pm
Here you go.
ColumnName Type Computed Length Prec scale
GROUP_ZZintno410 0
PATIENTnumericno912 0
INVOICEnumericno912 0
ADM_DTdatetimeno8
BILLING_AREAintno410 0
CASE_NUMvarcharno15
CASE_PACKAGEintno410 0
CASE_TYPEintno410 0
CLM_NUMnumericno910 0
CLM_RUN_DTdatetimeno8
CLM_RUN_NUMintno410 0
COMM_LABintno410 0
CORR_INV_NUMintno410 0
DISCH_DTdatetimeno8
DIVintno410 0
DT_OF_INJdatetimeno8
DX_EIGHTintno410 0
DX_FIVEintno410 0
DX_FOURintno410 0
DX_NINEintno410 0
DX_ONEintno410 0
DX_SEVENintno410 0
DX_SIXintno410 0
DX_TENintno410 0
DX_THREEintno410 0
DX_TWOintno410 0
FIRST_SPLIT_INVintno410 0
FSCintno410 0
HOSintno410 0
INV_BALmoneyno819 4
INV_CRE_DTdatetimeno8
INV_CRE_PDintno410 0
INV_NUMintno410 0
LAST_ACT_PCE_1intno410 0
LAST_ACT_PCE_2intno410 0
LAST_WORKED_DTdatetimeno8
LOCintno410 0
MCA_CONTRACT_NUMnumericno916 0
MCA_CUSTOM_SUPER_GROUPintno410 0
MCA_REF_NUMvarcharno15
NEXT_TXNintno410 0
ORIG_INV_FROM_SPLITintno410 0
ORIG_INV_NUMintno410 0
OTH_INV_NUMvarcharno20
PRIOR_AUTH_NUMvarcharno40
PROVintno410 0
REFERRING_PHYSICIANintno410 0
REFERRING_PHYSICIAN_FTXTvarcharno80
REF_NUMvarcharno30
REJ_1varcharno75
REJ_2varcharno75
REJ_3varcharno75
REJ_4varcharno75
REJ_5varcharno75
REJ_6varcharno75
REJ_7varcharno75
REJ_8varcharno75
REJ_9varcharno75
REJ_MESSvarcharno80
REJ_OTH_DTdatetimeno8
SECONDARY_PROVintno410 0
SECOND_SPLIT_INVintno410 0
SER_DTdatetimeno8
TOT_CHGmoneyno819 4
TYPE_OF_INJvarcharno13
TYPE_OF_SERintno410 0
U_DW_ENR_LOCintno410 0
U_DW_PCPintno410 0
U_DW_PLANvarcharno10
U_DX_FOUR_ZCLAIMintno410 0
INV_SCHED_LOCintno410 0
INV_SCHED_PROVintno410 0
U_LEVEL_OF_ACUITYintno410 0
U_MCA_VNDRintno410 0
MEDICAL_GROUPintno410 0
U_MEDICAL_GROUP_FTXTvarcharno30
U_MED_COMMENTvarcharno30
ORDER_NUMvarcharno25
PLACE_OF_SERVintno410 0
U_PP_FLAGintno410 0
U_PRIOR_AUTH_NUMvarcharno25
PROGRAMintno410 0
U_REJ_DE_1intno410 0
U_REJ_DE_2intno410 0
U_REJ_DE_3intno410 0
U_REJ_DE_4intno410 0
U_REJ_DE_5intno410 0
U_REJ_DE_6intno410 0
U_REJ_DE_7intno410 0
U_REJ_DE_8intno410 0
U_REJ_DE_9intno410 0
RESIDENT_PHYintno410 0
U_RPT_FAXEDvarcharno1
ORIG_VIS_NUMintno410 0
VIS_NUMintno410 0
WORK_COMP_CASE_NOvarcharno31
ZGW_FSC_LISTvarcharno125
ZGW_ORIG_FSCintno410 0
DATESTAMPdatetimeno8
November 19, 2009 at 2:09 pm
One of the "new" features in SQL 2005 (was new at the time) is that it can handle rows that are larger than 8090. It just moves them into an extended storage space. Same sort of thing as SQL 2000 did with text and image data types, and SQL 2005 still does with varchar(max) and varbinary(max).
Slows down queries, sometimes not by a noticeable amount, sometimes by a large amount. Otherwise, it's not a big deal.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 19, 2009 at 2:18 pm
Correction in my question.
There is no varchar(max) and image type in the table. I know when you calculate the size of the row then we have to exclude the size of varchar(max), image and text type.
One of my other table size is 12940 bytes.
I am really surprised , why I am not getting any error.
Just let you know that these tabels are partition tables. does this make a difference. ?
November 19, 2009 at 2:20 pm
I meant to say my other table row size is 12940
November 19, 2009 at 2:55 pm
This article may give you a clue.
November 19, 2009 at 3:17 pm
I did a little test. I ran the following query on SQL 2000 and the same query on SQL 2005.
I get error when I run the following query on sql 2000 server but don't get error when I ran on 2005 server. I think in 2005 the row storage architecture is different than SQL 2000. SQL 2005 still maintains a limit of 8 KB per page. When a combination of varchar, nvarchar, varbinary, or sql_variant columns exceeds this limit, SQL Server moves the record column with the largest width to another page, while maintaining a 24-byte pointer on the original page.
so in sql 2005 it will accept any row size but it will affect the performance of the server.
CREATE TABLE mytab (
col1 varchar(8000), col3 varchar(8000),
col4 varchar(8000), col5 varchar(8000),
col6 varchar(8000), col7 varchar(8000),
)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply