UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Row Size Limit?, Any Versions    
 
   
expatriate
post May 7 2019, 10:03 AM
Post#1



Posts: 410
Joined: 23-April 10
From: Thailand


SS 2017 express. I created these fields in code, and it generated these errors during the process. I know there is overhead and such but surely I can't be over the 8K limit(?) Is this a spurious warning msg that can be ignored?
Warning: The table "ztLogImported2" 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

CODE
  rDateTime datetime NULL,
  [d1s(F)] varchar(5) NULL,
  [d1r(F)] varchar(5) NULL,
  [d2s(F)] varchar(5) NULL,
  [d2r(F)] varchar(5) NULL,
  [d3s(F)] varchar(5) NULL,
  [d3r(F)] varchar(5) NULL,
  [d4s(F)] varchar(5) NULL,
  [d4r(F)] varchar(5) NULL,
  [d5s(F)] varchar(5) NULL,
  [d5r(F)] varchar(5) NULL,
  [d6s(F)] varchar(5) NULL,
  [d6r(F)] varchar(5) NULL,
  [d7s(F)] varchar(5) NULL,
  [d7r(F)] varchar(5) NULL,
  [d8s(F)] varchar(5) NULL,
  [d8r(F)] varchar(5) NULL,
  [d9s(F)] varchar(5) NULL,
  [d9r(F)] varchar(5) NULL


The table was not created in code, just all but the first field added that way.
CODE
EXEC ('ALTER TABLE ' + @Schema + ' ADD ' + @fname + ' varchar(5)')


Thank you
Go to the top of the page
 
Minty
post May 7 2019, 10:31 AM
Post#2



Posts: 312
Joined: 5-July 16
From: UK - Wiltshire


Have a read here https://stackoverflow.com/questions/1407093...8kb-record-size

Think it explains everything pretty well.
Go to the top of the page
 
MadPiet
post May 7 2019, 11:51 AM
Post#3



Posts: 3,171
Joined: 27-February 09



I would stay away from field names like this:
d1s(F)

Any developer working on a database with naming conventions like that is going to have some words for you. And not nice ones, because it means all references to fields like that will have to be bracketed. Yuck.
Go to the top of the page
 
GroverParkGeorge
post May 7 2019, 11:59 AM
Post#4


UA Admin
Posts: 35,306
Joined: 20-June 02
From: Newcastle, WA


Not only do those names appear to be non-standard enough to warrant extreme caution, having that many fields, all named the same except for the incrementing sequence number raise a BIG red flag about lack of valid normalization.

You might benefit from studying some of our design materials here.

In addition, Roger Carlson's blog contains several excellent posts on the problem of Repeating Columns--like those shown in your original script.

Getting the table design right from the first step will go a long way towards making your life less stressful down the road.
This post has been edited by GroverParkGeorge: May 7 2019, 12:19 PM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
MadPiet
post May 7 2019, 02:27 PM
Post#5



Posts: 3,171
Joined: 27-February 09



Time for the CROSS APPLY normalization trick. =)
https://sqlstudies.com/2013/04/01/unpivot-a...ng-cross-apply/

Well, that assumes that your data is already in SQL Server. Where is it? What's the table about? Because this design is just screams NO.
This post has been edited by MadPiet: May 7 2019, 02:29 PM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th July 2019 - 10:45 PM