Warning while creating a table in SQL 2005

Question

Warning while creating a table in SQL 2005

Viewing 13 posts - 1 through 14 (of 14 total)

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

Panders Right there with Babe Points: 772 More actions · Answer 1

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.

rathod.balu SSC Enthusiast Points: 111 More actions · Answer 2

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)

Suresh B. SSC-Insane Points: 22986 More actions · Answer 3

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.

rathod.balu SSC Enthusiast Points: 111 More actions · Answer 4

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)

Grant Fritchey SSC Guru Points: 399330 More actions · Answer 5

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

Johan Bijnens SSC Guru Points: 135590 More actions · Answer 6

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 and code to get the best help

- 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

rathod.balu SSC Enthusiast Points: 111 More actions · Answer 7

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.

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 8

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 9

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 10

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135590 More actions · Answer 11

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 and code to get the best help

- 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

rathod.balu SSC Enthusiast Points: 111 More actions · Answer 12

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...???

Johan Bijnens SSC Guru Points: 135590 More actions · Answer 13

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 and code to get the best help

- 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