This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes JDBC and the fly likes Maximum size of a row in sql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Maximum size of a row in sql" Watch "Maximum size of a row in sql" New topic
Author

Maximum size of a row in sql

Rd Dari
Ranch Hand

Joined: Feb 22, 2010
Posts: 206

Hi all Rancher,

I faced a written technical test yesterday during my interview there was a question.

What is the maximum size of a row in sql?

but I didn't know this. So please tell me the answer.
I searched it on net but exactly I don't know because there is so many example where I confused about that.

Thanks!
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

It is database dependent and (unless you were interviewing for a DBA job) quite a specialist thing to know. I'm guessing the interviewer was testing if you knew there was such a thing and that it varies from database to database. Usually if you are anywhere near row limits its time to review your data model!


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Rd Dari
Ranch Hand

Joined: Feb 22, 2010
Posts: 206

No sir I was interviewing of java but there were some questions of sql.

by the way thanks!
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

You are welcome
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1658
    
  14

Rd Dari wrote:Hi all Rancher,
What is the maximum size of a row in sql?


Not only is it database dependent, but the question is kind of vague too. Do they mean the maximum size of data that can be contained in a table row, or the maximum number of columns per table? Do they mean the maximum that can be physically stored in a table row, the maximum that can be handled within SQL (could vary I guess), or the maximum that JDBC can handle? Guess we'll never know!

For example, Oracle has various limits on individual column sizes and columns per table table, so you could in theory have a table containing 1000 x CLOB columns, each of which could contain many GB.

I probably wouldn't recommend it, though!

No more Blub for me, thank you, Vicar.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

I feel like this is one of those issues that "if you have to ask the limit, you're doing something wrong". Granted, interview questions sometimes test the boundaries of what you know, not what you need to know.


My Blog: Down Home Country Coding with Scott Selikoff
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30353
    
150

I agree. Knowing the maximum size of a column is reasonable. I have no idea what the maximum number of columns per row is. And if I was ever near that limit, there's too many columns.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

I recall in Oracle knowing the table data limit is practical, in part because if you have 2-3 large columns (free text entry such as VARCHAR(2000)) you'll hit it fast. Oracle "encourages" clobs at point since they don't get stored in the table directly. I've never thought much about the row limit per query though, since there would probably be bandwidth or timeout issues long before you hit it.
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1658
    
  14

Jeanne Boyarsky wrote:I agree. Knowing the maximum size of a column is reasonable. I have no idea what the maximum number of columns per row is. And if I was ever near that limit, there's too many columns.


Well, I'd agree with you there, except that my current project requires us to deal with precisely that situation. Our users have datasets containing up to 1300 columns of data, and different records may not contain the same 1300 columns either. As you cannot fit 1300 columns into a single table, one option is to split it across two or more tables, which is the approach we've taken for constructing views to output these datasets for example. But to deal with the unpredictable variability (i.e. each record could have a different set of up to 1300 columns), we've taken a star schema approach i.e. splitting each record into its constituent elements and storing these individually, then pivoting them back into a wide format as necessary. Lots of fun.

Anyway, drifting off topic I guess, but just goes to show that you can't predict when you'll need to know (or rather know how to find out) some of this arcane stuff.


Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30353
    
150

Chris,
The star schema sounds like a better design in that case anyway. Since some columns are n/a, they aren't really a logical part of the row.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Maximum size of a row in sql