can you have too many rows in a table

Question

can you have too many rows in a table

sukai_ndure

SSC Journeyman

Points: 84
More actions
November 14, 2014 at 9:57 am

#298555

How many rows would you consider to be too many for a single table and how would you re-arrange the data if asked?
any answers?

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

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

sukai_ndure (11/14/2014)
How many rows would you consider to be too many for a single table and how would you re-arrange the data if asked?
any answers?

Quick thought, any row with an identical meaning of an existing row is one too many

😎

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

It depends completely on the structure of the table. Like Erikur said, duplication can be normalized to eliminate it.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

djj SSCoach Points: 18831 More actions · Answer 3

sukai_ndure (11/14/2014)
How many rows would you consider to be too many for a single table and how would you re-arrange the data if asked?
any answers?

If there is a large number of rows you MAY need to look at partitioning the table.

Lowell SSC Guru Points: 323518 More actions · Answer 4

and the issue is often not really related to the number of rows, but if it can be accessed effectively and with reasonable speed.

that starts bringing in things like design, indexing partitioning, statistics, and hardware considerations, depending on the actual amount of data.

i've got 100gig databases, so is that too big? well, the application runs smooth, but i'm looking over indexing options all the time, based on queries.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

sukai_ndure (11/14/2014)
How many rows would you consider to be too many for a single table and how would you re-arrange the data if asked?
any answers?

Considering that your other question was...

What methods could you use to extract specific bits of data from an XML column in a table and return the data in alphanumeric format?
any answers?

... I'm convinced that you're either applying for a job or taking a test. How about you science this one out yourself?

Ask yourself is it really the number of rows that you need to be concerned about? Or, is it how long a backup or restore might take if you didn't divvy the table up? Would a piecemeal restore ever be appropriate? What version of SQL Server do you have and do you want to be able to do index rebuilds online? How much log space will your index rebuilds or reorgs take and what effect will that have on backups? Will there ever be a need to delete old data and will those deletions occur temporally or by some other factor such as a lost client? Are there any security concerns with having an all-in-one table for multiple clients?

--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)

Miles Neale SSChampion Points: 13147 More actions · Answer 6

sukai_ndure (11/14/2014)
How many rows would you consider to be too many for a single table and how would you re-arrange the data if asked?
any answers?

First Question How many rows - The answer is Blue

How to re-arrange the data - in descending order of third childs birth month within number of letters in the name of the birthstone spelled backwards descending.

Not all gray hairs are Dinosaurs!

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Lowell (11/14/2014)
and the issue is often not really related to the number of rows, but if it can be accessed effectively and with reasonable speed.
that starts bringing in things like design, indexing partitioning, statistics, and hardware considerations, depending on the actual amount of data.
i've got 100gig databases, so is that too big? well, the application runs smooth, but i'm looking over indexing options all the time, based on queries.

Ah, sorry, Lowell. I feel like I duplicated your good answer. I had the post open for reply for a long time and didn't get back to it.

Shifting gears and to add to what Lowell, myself, and others have said and hopefully drive the point home, I have a table with only 1.2 million rows, has only 4 columns in it, and only has a clustered index on a IDENTITY column for indexes. Would anyone consider that to have "too many rows in it"? Probably not from that description.

Now, what if I told you that the table occupies 384GB and takes 3 to 5 hours to backup (or restore) depending on what else is running at the time and because I've been compelled to do the backups on NAS instead of SAN?

And, yeah... I really have a table like that.

Like Miles said above, "The answer is Blue" because if you're holding your breath for a restore on that table, that's the color you will be before it's done. If it's your boss, the answer will be "Hopping Mad Red"... especially since this is for a critical customer telephony application.

And, yeah... I'm in the process of setting up temporal partitioning with read only files that never need to be backed up again and I'll be able to do a "Get back in business" restore in 6 minutes flat and restore the 3 most critical months in just several minutes more.

The number of rows mean nothing when making such decisions... unless you really like "Blue". 😛

--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)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 8

To summarize the rest of the answers:

It depends.

"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

Michael L John One Orange Chip Points: 27055 More actions · Answer 9

I think you may have asked the wrong question.

My answer to you is:

What specific problem are you trying to solve? Or, what specific argument are you trying to win?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Ed Wagner SSC Guru Points: 287026 More actions · Answer 10

Jeff Moden (11/14/2014)
And, yeah... I'm in the process of setting up temporal partitioning with read only files that never need to be backed up again and I'll be able to do a "Get back in business" restore in 6 minutes flat and restore the 3 most critical months in just several minutes more.

Now that is just plain cool. If anyone ever gets a chance to attend a SQL Saturday where Jeff does his partitioning presentation, I'd recommend you take the opportunity. It spans two sessions, but it's definitely worth it.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

Thanks for the plug, Ed! 🙂

--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)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 12

Jeff Moden (11/15/2014)
Thanks for the plug, Ed! 🙂

No problem. You've earned it - it's a great presentation that delves into how it really works. The dust bunnies must have been creating their own communes on your back with that one. 😛

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions