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_ZZ int no 4 10 0
PATIENT numeric no 9 12 0
INVOICE numeric no 9 12 0
ADM_DT datetime no 8
BILLING_AREA int no 4 10 0
CASE_NUM varchar no 15
CASE_PACKAGE int no 4 10 0
CASE_TYPE int no 4 10 0
CLM_NUM numeric no 9 10 0
CLM_RUN_DT datetime no 8
CLM_RUN_NUM int no 4 10 0
COMM_LAB int no 4 10 0
CORR_INV_NUM int no 4 10 0
DISCH_DT datetime no 8
DIV int no 4 10 0
DT_OF_INJ datetime no 8
DX_EIGHT int no 4 10 0
DX_FIVE int no 4 10 0
DX_FOUR int no 4 10 0
DX_NINE int no 4 10 0
DX_ONE int no 4 10 0
DX_SEVEN int no 4 10 0
DX_SIX int no 4 10 0
DX_TEN int no 4 10 0
DX_THREE int no 4 10 0
DX_TWO int no 4 10 0
FIRST_SPLIT_INV int no 4 10 0
FSC int no 4 10 0
HOS int no 4 10 0
INV_BAL money no 8 19 4
INV_CRE_DT datetime no 8
INV_CRE_PD int no 4 10 0
INV_NUM int no 4 10 0
LAST_ACT_PCE_1 int no 4 10 0
LAST_ACT_PCE_2 int no 4 10 0
LAST_WORKED_DT datetime no 8
LOC int no 4 10 0
MCA_CONTRACT_NUM numeric no 9 16 0
MCA_CUSTOM_SUPER_GROUP int no 4 10 0
MCA_REF_NUM varchar no 15
NEXT_TXN int no 4 10 0
ORIG_INV_FROM_SPLIT int no 4 10 0
ORIG_INV_NUM int no 4 10 0
OTH_INV_NUM varchar no 20
PRIOR_AUTH_NUM varchar no 40
PROV int no 4 10 0
REFERRING_PHYSICIAN int no 4 10 0
REFERRING_PHYSICIAN_FTXT varchar no 80
REF_NUM varchar no 30
REJ_1 varchar no 75
REJ_2 varchar no 75
REJ_3 varchar no 75
REJ_4 varchar no 75
REJ_5 varchar no 75
REJ_6 varchar no 75
REJ_7 varchar no 75
REJ_8 varchar no 75
REJ_9 varchar no 75
REJ_MESS varchar no 80
REJ_OTH_DT datetime no 8
SECONDARY_PROV int no 4 10 0
SECOND_SPLIT_INV int no 4 10 0
SER_DT datetime no 8
TOT_CHG money no 8 19 4
TYPE_OF_INJ varchar no 13
TYPE_OF_SER int no 4 10 0
U_DW_ENR_LOC int no 4 10 0
U_DW_PCP int no 4 10 0
U_DW_PLAN varchar no 10
U_DX_FOUR_ZCLAIM int no 4 10 0
INV_SCHED_LOC int no 4 10 0
INV_SCHED_PROV int no 4 10 0
U_LEVEL_OF_ACUITY int no 4 10 0
U_MCA_VNDR int no 4 10 0
MEDICAL_GROUP int no 4 10 0
U_MEDICAL_GROUP_FTXT varchar no 30
U_MED_COMMENT varchar no 30
ORDER_NUM varchar no 25
PLACE_OF_SERV int no 4 10 0
U_PP_FLAG int no 4 10 0
U_PRIOR_AUTH_NUM varchar no 25
PROGRAM int no 4 10 0
U_REJ_DE_1 int no 4 10 0
U_REJ_DE_2 int no 4 10 0
U_REJ_DE_3 int no 4 10 0
U_REJ_DE_4 int no 4 10 0
U_REJ_DE_5 int no 4 10 0
U_REJ_DE_6 int no 4 10 0
U_REJ_DE_7 int no 4 10 0
U_REJ_DE_8 int no 4 10 0
U_REJ_DE_9 int no 4 10 0
RESIDENT_PHY int no 4 10 0
U_RPT_FAXED varchar no 1
ORIG_VIS_NUM int no 4 10 0
VIS_NUM int no 4 10 0
WORK_COMP_CASE_NO varchar no 31
ZGW_FSC_LIST varchar no 125
ZGW_ORIG_FSC int no 4 10 0
DATESTAMP datetime no 8
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