July 21, 2011 at 3:41 am
Hi All,
I working on a datamart and am trying to create a table. While the table is getting created, i am getting the following warning msg. Also while trying to insert data into the table, it is failing with same error.
=============Warning while creating the table================
Warning: The table "XXXX" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.
=============Error while inserting the data===================
ERROR MESSAGE : Cannot create a row of size 8468 which is greater than the allowable maximum of 8060.
==============Some info=====================
I am working SQL 2005 and compatibility 90
Have tried using varchar(max) but dint work
have tried using Sp_tableoption for large value but din work.
given below is the table schema i m trying to create. even if i am dividing the tabkle in 2 still cud not get away with the waring. dun want to divide it into 3. Ideally , we must not get this warning due to row over flow concept of the SQL 2005 however i am still gettiung this issue.
kindly tell me whr am i goin wrong. Thnx
=================Query=======================
create table MyTable (
A1 bigint , A2 datetime,A3 datetime,A4 datetime,A5 datetime,A6 datetime,A7 datetime,A8 datetime,
A9 datetime,A10 datetime,A11 datetime,A12 datetime,A13 varchar(50),A14 varchar(50),A15 varchar(50),
A16 varchar(50),A17 varchar(50),A18 varchar(50),A19 varchar(50),A20 varchar(50),A21 varchar(50),A22 varchar(50),
A23 varchar(50),A24 varchar(50),A25 varchar(50),A26 varchar(50),A27 varchar(50),A28 varchar(50),A29 varchar(50),
A30 varchar(50),A31 varchar(9),A32 varchar(20),A33 varchar(7),A34 varchar(20),A35 varchar(255),A36 varchar(20),
A37 varchar(1),A38 char(1),A39 varchar(60),A40 varchar(60),A41 varchar(20),A42 char(1),A43 varchar(9),
A44 varchar(10),A45 varchar(100),A46 varchar(100),A47 varchar(100),A48 varchar(20),A49 varchar(20),
A50 varchar(100),A51 varchar(20),A52 varchar(100),A53 varchar(20),A54 varchar(100),A55 varchar(20),A56 varchar(100),
A57 varchar(20),A58 varchar(100),A59 varchar(10),A60 varchar(60),A61 varchar(10),A62 varchar(60),A63 varchar(10),
A64 varchar(60),A65 varchar(10),A66 varchar(60),A67 varchar(10),A68 varchar(60),A69 varchar(10),A70 varchar(60),
A71 varchar(10),A72 varchar(60),A73 varchar(10),A74 varchar(60),A75 varchar(10),A76 varchar(60),A77 varchar(10),
A78 varchar(60),A79 varchar(10),A80 varchar(10),A81 varchar(10),A82 varchar(10),A83 varchar(200),A84 varchar(200),
A85 varchar(200),A86 varchar(200),A87 varchar(200),A88 varchar(9),A89 varchar(20),A90 varchar(7),A91 varchar(20),
A92 varchar(255),A93 varchar(20),A94 varchar(1),A95 char(1),A96 varchar(60),A97 varchar(60),A98 varchar(20),
A99 char(1),A100 varchar(9),A101 varchar(10),A102 varchar(100),A103 varchar(100),A104 varchar(100),A105 varchar(20),
A106 varchar(20),A107 varchar(100),A108 varchar(20),A109 varchar(100),A110 varchar(20),A111 varchar(100),
A112 varchar(20),A113 varchar(100),A114 varchar(20),A115 varchar(100),A116 varchar(10),A117 varchar(60),
A118 varchar(10),A119 varchar(60),A120 varchar(10),A121 varchar(60),A122 varchar(10),A123 varchar(60),
A124 varchar(10),A125 varchar(60),A126 varchar(10),A127 varchar(60),A128 varchar(10),A129 varchar(60),
A130 varchar(10),A131 varchar(60),A132 varchar(10),A133 varchar(60),A134 varchar(10),A135 varchar(60),
A136 varchar(10),A137 varchar(10),A138 varchar(10),A139 varchar(10),A140 varchar(200),A141 varchar(200),
A142 varchar(200),A143 varchar(200),A144 varchar(200),A145 varchar(9),A146 varchar(20),A147 varchar(7),A148 varchar(20),
A149 varchar(255),A150 varchar(20),A151 varchar(1),A152 char(1),A153 varchar(60),A154 varchar(60),A155 varchar(20),A156 char(1),
A157 varchar(9),A158 varchar(10),A159 varchar(100),A160 varchar(100),A161 varchar(100),A162 varchar(20),A163 varchar(20),
A164 varchar(100),A165 varchar(20),A166 varchar(100),A167 varchar(20),A168 varchar(100),A169 varchar(20),A170 varchar(100),
A171 varchar(20),A172 varchar(100),A173 varchar(10),A174 varchar(60),A175 varchar(10),A176 varchar(60),A177 varchar(10),
A178 varchar(60),A179 varchar(10),A180 varchar(60),A181 varchar(10),A182 varchar(60),A183 varchar(10),A184 varchar(60),
A185 varchar(10),A186 varchar(60),A187 varchar(10),A188 varchar(60),A189 varchar(10),A190 varchar(60),
A191 varchar(10),A192 varchar(60),A193 varchar(10),A194 varchar(10),A195 varchar(10),A196 varchar(10),A197 varchar(200),
A198 varchar(200),A199 varchar(200),A200 varchar(200),A201 varchar(200),A202 varchar(9),A203 varchar(20),
A204 varchar(7),A205 varchar(20),A206 varchar(255),A207 varchar(20),A208 varchar(1),A209 char(1),
A210 varchar(60),A211 varchar(60),A212 varchar(20),A213 char(1),A214 varchar(9),A215 varchar(10),A216 varchar(100),
A217 varchar(100),A218 varchar(100),A219 varchar(20),A220 varchar(20),A221 varchar(100),A222 varchar(20),A223 varchar(100),
A224 varchar(20),A225 varchar(100),A226 varchar(20),A227 varchar(100),A228 varchar(20),A229 varchar(100),A230 varchar(10),
A231 varchar(60),A232 varchar(10),A233 varchar(60),A234 varchar(10),A235 varchar(60),A236 varchar(10),A237 varchar(60),
A238 varchar(10),A239 varchar(60),A240 varchar(10),A241 varchar(60),A242 varchar(10),A243 varchar(60),A244 varchar(10),
A245 varchar(60),A246 varchar(10),A247 varchar(60),A248 varchar(10),A249 varchar(60),A250 varchar(10),A251 varchar(10),
A252 varchar(10),A253 varchar(10),A254 varchar(200),A255 varchar(200),A256 varchar(200),A257 varchar(200),A258 varchar(200),
A259 varchar(9),A260 varchar(20),A261 varchar(7),A262 varchar(20),A263 varchar(255),A264 varchar(20),A265 varchar(1),
A266 char(1),A267 varchar(60),A268 varchar(60),A269 varchar(20),A270 char(1),A271 varchar(9),A272 varchar(10),A273 varchar(100),
A274 varchar(100),A275 varchar(100),A276 varchar(20),A277 varchar(20),A278 varchar(100),A279 varchar(20),A280 varchar(100),
A281 varchar(20),A282 varchar(100),A283 varchar(20),A284 varchar(100),A285 varchar(20),A286 varchar(100),A287 varchar(10),
A288 varchar(60),A289 varchar(10),A290 varchar(60),A291 varchar(10),A292 varchar(60),A293 varchar(10),A294 varchar(60),
A295 varchar(10),A296 varchar(60),A297 varchar(10),A298 varchar(60),A299 varchar(10),A300 varchar(60),A301 varchar(10),
A302 varchar(60),A303 varchar(10),A304 varchar(60),A305 varchar(10),A306 varchar(60),A307 varchar(10),A308 varchar(10),A309 varchar(10),
A310 varchar(10),A311 varchar(200),A312 varchar(200),A313 varchar(200),A314 varchar(200),A315 varchar(200),A316 varchar(9),
A317 varchar(20),A318 varchar(7),A319 varchar(20),A320 varchar(255),A321 varchar(20),A322 varchar(1),A323 char(1),
A324 varchar(60),A325 varchar(60),A326 varchar(20),A327 char(1),A328 varchar(9),A329 varchar(10),A330 varchar(100),A331 varchar(100),
A332 varchar(100),A333 varchar(20),A334 varchar(20),A335 varchar(100),A336 varchar(20),A337 varchar(100),A338 varchar(20),
A339 varchar(100),A340 varchar(20),A341 varchar(100),A342 varchar(20),A343 varchar(100),A344 varchar(10),A345 varchar(60),
A346 varchar(10),A347 varchar(60),A348 varchar(10),A349 varchar(60),A350 varchar(10),A351 varchar(60),A352 varchar(10),
A353 varchar(60),A354 varchar(10),A355 varchar(60),A356 varchar(10),A357 varchar(60),A358 varchar(10),A359 varchar(60),
A360 varchar(10),A361 varchar(60),A362 varchar(10),A363 varchar(60),A364 varchar(10),A365 varchar(10),A366 varchar(10),
A367 varchar(10),A368 varchar(200),A369 varchar(200),A370 varchar(200),A371 varchar(200),A372 varchar(200),A373 varchar(9),
A374 varchar(20),A375 varchar(7),A376 varchar(20),A377 varchar(255),A378 varchar(20),A379 varchar(1),A380 char(1),A381 varchar(60),
A382 varchar(60),A383 varchar(20),A384 char(1),A385 varchar(9),A386 varchar(10),A387 varchar(100),A388 varchar(100),A389 varchar(100),
A390 varchar(20),A391 varchar(20),A392 varchar(100),A393 varchar(20),A394 varchar(100),A395 varchar(20),A396 varchar(100),A397 varchar(20),
A398 varchar(100),A399 varchar(20),A400 varchar(100),A401 varchar(10),A402 varchar(60),A403 varchar(10),A404 varchar(60),
A405 varchar(10),A406 varchar(60),A407 varchar(10),A408 varchar(60),A409 varchar(10),A410 varchar(60),A411 varchar(10),A412 varchar(60),
A413 varchar(10),A414 varchar(60),A415 varchar(10),A416 varchar(60),A417 varchar(10),A418 varchar(60),A419 varchar(10),A420 varchar(60),
A421 varchar(10),A422 varchar(10),A423 varchar(10),A424 varchar(10),A425 varchar(200),A426 varchar(200),A427 varchar(200),A428 varchar(200),
A429 varchar(200),A430 varchar(9),A431 varchar(20),A432 varchar(7),A433 varchar(20),A434 varchar(255),A435 varchar(20),A436 varchar(1),
A437 char(1),A438 varchar(60),A439 varchar(60),A440 varchar(20),A441 char(1),A442 varchar(9),A443 varchar(10),A444 varchar(100),
A445 varchar(100),A446 varchar(100),A447 varchar(20),A448 varchar(20),A449 varchar(100),A450 varchar(20),A451 varchar(100),
A452 varchar(20),A453 varchar(100),A454 varchar(20),A455 varchar(100),A456 varchar(20),A457 varchar(100),A458 varchar(10),
A459 varchar(60),A460 varchar(10),A461 varchar(60),A462 varchar(10),A463 varchar(60),A464 varchar(10),A465 varchar(60),
A466 varchar(10),A467 varchar(60),A468 varchar(10),A469 varchar(60),A470 varchar(10),A471 varchar(60),A472 varchar(10),A473 varchar(60),
A474 varchar(10),A475 varchar(60),A476 varchar(10),A477 varchar(60),A478 varchar(10),A479 varchar(10),A480 varchar(10),
A481 varchar(10),A482 varchar(200),A483 varchar(200),A484 varchar(200),A485 varchar(200),A486 varchar(200),A487 varchar(9),A488 varchar(20),
A489 varchar(7),A490 varchar(20),A491 varchar(255),A492 varchar(20),A493 varchar(1),A494 char(1),A495 varchar(60),A496 varchar(60),A497 varchar(20),
A498 char(1),A499 varchar(9),A500 varchar(10),A501 varchar(100),A502 varchar(100),A503 varchar(100),A504 varchar(20),A505 varchar(20),
A506 varchar(100),A507 varchar(20),A508 varchar(100),A509 varchar(20),A510 varchar(100),A511 varchar(20),A512 varchar(100),A513 varchar(20),
A514 varchar(100),A515 varchar(10),A516 varchar(60),A517 varchar(10),A518 varchar(60),A519 varchar(10),A520 varchar(60),A521 varchar(10),A522 varchar(60),
A523 varchar(10),A524 varchar(60),A525 varchar(10),A526 varchar(60),A527 varchar(10),A528 varchar(60),A529 varchar(10),A530 varchar(60),A531 varchar(10),
A532 varchar(60),A533 varchar(10),A534 varchar(60),A535 varchar(10),A536 varchar(10),A537 varchar(10),A538 varchar(10),A539 varchar(200),
A540 varchar(200),A541 varchar(200),A542 varchar(200),A543 varchar(200),A544 varchar(9),A545 varchar(20),A546 varchar(7),A547 varchar(20),A548 varchar(255),
A549 varchar(20),A550 varchar(1),A551 char(1),A552 varchar(60),A553 varchar(60),A554 varchar(20),A555 char(1),A556 varchar(9),A557 varchar(10),
A558 varchar(100),A559 varchar(100),A560 varchar(100),A561 varchar(20),A562 varchar(20),A563 varchar(100),A564 varchar(20),A565 varchar(100),
A566 varchar(20),A567 varchar(100),A568 varchar(20),A569 varchar(100),A570 varchar(20),A571 varchar(100),A572 varchar(10),A573 varchar(60),
A574 varchar(10),A575 varchar(60),A576 varchar(10),A577 varchar(60),A578 varchar(10),A579 varchar(60),A580 varchar(10),A581 varchar(60),
A582 varchar(10),A583 varchar(60),A584 varchar(10),A585 varchar(60),A586 varchar(10),A587 varchar(60),A588 varchar(10),A589 varchar(60),
A590 varchar(10),A591 varchar(60),A592 varchar(10),A593 varchar(10),A594 varchar(10),A595 varchar(10),A596 varchar(200),A597 varchar(200),
A598 varchar(200),A599 varchar(200),A600 varchar(200),A601 varchar(9),A602 varchar(20),A603 varchar(7),A604 varchar(20),A605 varchar(255),
A606 varchar(20),A607 varchar(1),A608 char(1),A609 varchar(60),A610 varchar(60),A611 varchar(20),A612 char(1),A613 varchar(9),
A614 varchar(10),A615 varchar(100),A616 varchar(100),A617 varchar(100),A618 varchar(20),A619 varchar(20),A620 varchar(100),
A621 varchar(20),A622 varchar(100),A623 varchar(20),A624 varchar(100),A625 varchar(20),A626 varchar(100),A627 varchar(20),A628 varchar(100),
A629 varchar(10),A630 varchar(60),A631 varchar(10),A632 varchar(60),A633 varchar(10),A634 varchar(60),A635 varchar(10),A636 varchar(60),
A637 varchar(10),A638 varchar(60),A639 varchar(10),A640 varchar(60),A641 varchar(10),A642 varchar(60),A643 varchar(10),A644 varchar(60),
A645 varchar(10),A646 varchar(60),A647 varchar(10),A648 varchar(60),A649 varchar(10),A650 varchar(10),A651 varchar(10),A652 varchar(10),
A653 varchar(200),A654 varchar(200),A655 varchar(200),A656 varchar(200),A657 varchar(200),A658 varchar(9),A659 varchar(20),
A660 varchar(7),A661 varchar(20),A662 varchar(255),A663 varchar(20),A664 varchar(1),A665 char(1),A666 varchar(60),A667 varchar(60),
A668 varchar(20),A669 char(1),A670 varchar(9),A671 varchar(10),A672 varchar(100),A673 varchar(100),A674 varchar(100),A675 varchar(20),
A676 varchar(20),A677 varchar(100),A678 varchar(20),A679 varchar(100),A680 varchar(20),A681 varchar(100),A682 varchar(20),
A683 varchar(100),A684 varchar(20),A685 varchar(100),A686 varchar(10),A687 varchar(60),A688 varchar(10),A689 varchar(60),
A690 varchar(10),A691 varchar(60),A692 varchar(10),A693 varchar(60),A694 varchar(10),A695 varchar(60),
A696 varchar(10),A697 varchar(60),A698 varchar(10),A699 varchar(60),A700 varchar(10),A701 varchar(60),A702 varchar(10),
A703 varchar(60),A704 varchar(10),A705 varchar(60),A706 varchar(10),A707 varchar(10),A708 varchar(10),A709 varchar(10),
A710 varchar(200),A711 varchar(200),A712 varchar(200),A713 varchar(200),A714 varchar(200),A715 varchar(50),
A716 varchar(255),A717 bigint,A718 varchar(20),A719 varchar(255),A720 varchar(20),A721 varchar(20),A722 varchar(20),
A723 varchar(20),A724 varchar(20),A725 varchar(20),A726 varchar(20),A727 varchar(20),A728 varchar(20),A729 varchar(20),
A730 varchar(20),A731 varchar(1),A732 varchar(5),A733 varchar(10),A734 varchar(20),A735 varchar(20),A736 varchar(20),
A737 varchar(20),A738 varchar(20),A739 varchar(1),A740 numeric,A741 varchar(50),A742 varchar(50),A743 varchar(20),
A744 varchar(20),A745 varchar(20),A746 varchar(20),A747 varchar(10),A748 varchar (25) ,A749 varchar (500) ,A750 varchar(20),
A751 numeric,A752 numeric,A753 varchar(4),A754 varchar(18),A755 varchar(20),A756 varchar(20),A757 varchar(20),A758 varchar(20),
A759 varchar(30),A760 varchar(20),A761 varchar(50),A762 varchar(255),A763 varchar(50),A764 varchar(255),A765 varchar(50),
A766 varchar(255),A767 varchar(50),A768 varchar(255),A769 varchar(50),A770 varchar(255),A771 varchar(50),A772 varchar(255),
A773 varchar(50),A774 varchar(255),A775 varchar(50),A776 varchar(255),A777 varchar(50),A778 varchar(255),A779 varchar(50),
A780 varchar(255),A781 varchar(50),A782 bigint,A783 varchar(50),A784 varchar(50),A785 varchar(50),A786 varchar(50),
A787 varchar(50),A788 char(5),A789 varchar(50),A790 numeric,A791 numeric,A792 char(1),A793 char(1),A794 char(1),A795 int,
A796 varchar(50),A797 datetime,A798 datetime,A799 datetime,A800 datetime,A801 numeric,A802 datetime,A803 numeric,A804 varchar(100),
A805 varchar(100),A806 smallint,A807 smallint,A808 smallint,A809 smallint,A810 smallint,A811 smallint,A812 smallint,
A813 smallint,A814 smallint,A815 smallint,A816 smallint,A817 smallint,A818 smallint,A819 smallint,A820 smallint,A821 smallint,
A822 decimal,A823 decimal,A824 decimal,A825 decimal,A826 decimal,A827 decimal,A828 decimal,A829 decimal,A830 decimal,
A831 decimal,A832 decimal,A833 decimal,A834 decimal,A835 decimal,A836 decimal,A837 int,A838 int,A839 decimal,A840 datetime,
A841 datetime,A842 varchar(10),A843 varchar(20),A844 varchar(20),A845 decimal,A846 int,A847 char(1),A848 decimal,A849 decimal,
A850 decimal,A851 decimal,A852 varchar(20),A853 varchar(20),A854 varchar(1),A855 varchar(20),A856 varchar(20),A857 varchar(1),A858 varchar(30),
A859 varchar(30),A860 varchar(50),A861 nvarchar(Max),A862 nvarchar(Max),A863 nvarchar(Max),A864 nvarchar(Max),A865 decimal,
A866 decimal,A867 decimal,A868 decimal,A869 varchar(2000),A870 char(1),A871 varchar(50),A872 varchar(60),A873 varchar(60),
A874 varchar(3),A875 varchar(4),A876 decimal,A877 decimal,A878 smallint,A879 smallint,A880 datetime,A881 datetime,
A882 int,A883 int,A884 int,A885 int)
July 21, 2011 at 3:49 am
The maximum width of a table is 8060 bytes on SQL 2005 (for In Row Data) . You need to reduce the amount of columns in your table.
July 21, 2011 at 4:04 am
Hi,
But when i am creating the following table its getting created wihtout any warning???
Isnt the row lengh in case of the following table also greater than 8060.
kindly let me know if i am misunderstanding anything. Thanks
CREATE TABLE [dbo].[temp_6](
[w] [varchar](7000) NULL,[a] [varchar](5000) NULL,
[varchar](5000) NULL,[c] [varchar](5000) NULL,
[aa] [varchar](5000) NULL,[ab] [varchar](5000) NULL,
[ac] [varchar](5000) NULL,[RQ] [nvarchar](4000) NULL,
[Ru] [nvarchar](4000) NULL,[Ry] [nvarchar](4000) NULL,
[Re] [nvarchar](4000) NULL,[e] [nvarchar](4000) NULL,
[nvarchar](4000) NULL, [nvarchar](4000) NULL,
[l] [nvarchar](4000) NULL,[tyu] [varchar](max) NULL,
[tydfsdu] [nvarchar](max) NULL)
July 21, 2011 at 4:05 am
rathod.balu (7/21/2011)
...Ideally , we must not get this warning due to row over flow concept of the SQL 2005 however i am still gettiung this issue.
Row overflow happens only for varchar, nvarchar, etc. type of columns.
Your other data type (char, datetime, decimal, etc.) columns are more than 8090 bytes.
July 21, 2011 at 4:25 am
Hi,
Thanks for you reply.
I tried creating the table after removing all varchar and nvarchar column.
it created it without any warning and the row lenght is also aslomst 592 only in this case. Hope i have understood u correct.
kindly find belo the schema for the same
create table MyTable2 (
A1 bigint , A2 datetime,A3 datetime,A4 datetime,A5 datetime,A6 datetime,A7 datetime,A8 datetime,
A9 datetime,A10 datetime,A11 datetime,A12 datetime,A38 char(1),A42 char(1),A95 char(1),A99 char(1),A152 char(1),A156 char(1),A209 char(1),
A213 char(1),A266 char(1),A270 char(1),A323 char(1),A327 char(1),A380 char(1),A437 char(1),A441 char(1),A494 char(1),
A498 char(1),A551 char(1),A555 char(1),A608 char(1),A612 char(1),A665 char(1),A669 char(1),A717 bigint,A740 numeric,
A751 numeric,A752 numeric,A782 bigint,A788 char(5),A790 numeric,A791 numeric,A792 char(1),A793 char(1),A794 char(1),A795 int,A797 datetime,A798 datetime,A799 datetime,A800 datetime,A801 numeric,A802 datetime,A803 numeric,
A806 smallint,A807 smallint,A808 smallint,A809 smallint,A810 smallint,A811 smallint,A812 smallint,
A813 smallint,A814 smallint,A815 smallint,A816 smallint,A817 smallint,A818 smallint,A819 smallint,A820 smallint,A821 smallint,
A822 decimal,A823 decimal,A824 decimal,A825 decimal,A826 decimal,A827 decimal,A828 decimal,A829 decimal,A830 decimal,
A831 decimal,A832 decimal,A833 decimal,A834 decimal,A835 decimal,A836 decimal,A837 int,A838 int,A839 decimal,A840 datetime,
A841 datetime,A845 decimal,A846 int,A847 char(1),A848 decimal,A849 decimal,
A850 decimal,A851 decimal,A865 decimal,A866 decimal,A867 decimal,A868 decimal,A870 char(1),
A876 decimal,A877 decimal,A878 smallint,A879 smallint,A880 datetime,A881 datetime,
A882 int,A883 int,A884 int,A885 int)
July 21, 2011 at 4:55 am
I'd strongly suggest looking into the concept of data normalization. I'm not sure what business case this table is answering, but I'm positive it could be better met through relational storage mechanism rather than one giant fat table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 21, 2011 at 7:04 am
Although I strongly support Grants reply, I must admit for some subsystems we actually use that kind of column naming conventions to supposed handle dynamic queries for SCADA systems. Other parameter tables will describe the actual content for a column. I'm lucky these devs actually stated "select speed" is not a concern for their systems.
The restriction the system informs you about is the restriction to 8060 bytes for fixed datatyped columns length including the pointers for off row data. Your variable length data will get shifted off row when your row data would overgrow the number. Not all var length columns will be shifted at once, but only the ones needed to do the job.
"So what ?", you may ask.
Well consider every off row column may need an extra IO to be fetched with your queries. Keep in mind IO is still the slowest part in your server ( even with SSD ).
That's where DA comes in. Data analysis to design your data system. Applying the concepts Dr. Codd handed us and helping us the actually understand our data systems and preventing a "well thats how our system does it" gesture.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 21, 2011 at 8:15 am
Thanks for your reply. But i am sorry, i failed to understand the point you are trying to make.
Could you please elaborate as for whr i am going worng and wut option do i have.
July 21, 2011 at 8:41 pm
rathod.balu (7/21/2011)
Thanks for your reply. But i am sorry, i failed to understand the point you are trying to make.Could you please elaborate as for whr i am going worng and wut option do i have.
Rowsize isn't based just on the width of each column. VARCHAR and NVARCHAR each use an additional 2 bytes to keep track of the width of the "field" within a row and those can't go "out of row". Also (IIRC), when things do go "out of row", a locator "index" (or sorts, can't remember for sure, but I think it's VarBinary) is included in the row.
Bottom line? You just have too many variable width columns. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
July 21, 2011 at 9:00 pm
I got real curious and looked it up. Out-of-row pointers are freakin' huge... they're 24 bytes. I'm thinking that's likely the problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 21, 2011 at 9:11 pm
I don't know if it'll help for sure but I seem to remember an old wive's tale that may help. If you put all of the fixed length columns first, followed by the MAX datatypes, followed by the other variable length columns, you might stop getting the error.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 22, 2011 at 12:22 am
rathod.balu (7/21/2011)
Thanks for your reply. But i am sorry, i failed to understand the point you are trying to make.Could you please elaborate as for whr i am going worng and wut option do i have.
The point is:
- having rows with many columns will cause your 8060 bytes to be consumed much faster than you may think. When sqlserver shifts a var column off row, it replaces it with a 17-24 bytes pointer. This pointer is also a fixed length thingy, so sums up with the other fixed lenght columns length.
- concider worst case, every off row column consumes at least one extra I/O ( slowest operation !!! )
- Column naming conventions used arent explaining what the actual content will be, making it a harder job to untangle the cluther and optimize by vertical split.
Splitting this table in multiple tables so evey table actually holds data of a certain logical domain may help your data system be more/better manageble, tunable, ... so serve you better in the long run.
datatypen columnSum(length)
bigint324
char3034
datetime20160
decimal27243
int832
numeric7133
nvarchar44*1GBOff row candidates
smallint1836
varchar76846585+(768*2)Off row candidates
I would also avoid the mix of decimal and numeric because they are synomims.
The way you are using decimal/numeric, without specifying precision nor scale, gives me the feeling these may be replaced by integers or bigints because of the lack of scale !
Jeff Moden (7/21/2011)
I don't know if it'll help for sure but I seem to remember an old wive's tale that may help. If you put all of the fixed length columns first, followed by the MAX datatypes, followed by the other variable length columns, you might stop getting the error.
A younger girls tale, maybe not that much wisdom, but a bit more attractive anyway 😉 , said the actual order shouldn't make any difference nowadays. The engine will do the placement by itself !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 22, 2011 at 1:04 am
Hey... thanks so much.... the piece of info shared by you is really valuable ... thnks once agin....
as far as the col name goes , i have changed thme due security reasons...
Thus, can i conclude that breaking the table is the only option i am left with...???
July 22, 2011 at 2:11 am
I suggest to follow Grants advise.
If you are building an OLTP database, one should analyse the data to get at least to 3NF and only then consider applying less optimal NFs if needed.
Have a look at Description of the database normalization basics to get started with such an exercise.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply