Limits on number of Rows in a SQL Server Table

Question

Are there any hard limits on the number of rows in a table in a sql server table? I am under the impression the only limit is based around physical storage.

At what point does performance significantly degrade, if at all, on tables with and without an index. Are there any common practicies for very large tables?

To give a little domain knowledge, we are considering usage of an Audit table which will log changes to fields for all tables in a database and are wondering what types of walls we might run up against.

Answer 1

You are correct that the number of rows is limited by your available storage.

It is hard to give any numbers as it very much depends on your server hardware, configuration, and how efficient your queries are.

For example, a simple select statement will run faster and show less degradation than a Full Text or Proximity search as the number of rows grows.

Answer 2

BrianV is correct. It's hard to give a rule because it varies drastically based on how you will use the table, how it's indexed, the actual columns in the table, etc.

As to common practices... for very large tables you might consider partitioning. This could be especially useful if you find that for your log you typically only care about changes in the last 1 month (or 1 day, 1 week, 1 year, whatever). You could then archive off older portions of the data so that it's available if absolutely needed, but won't be in the way since you will almost never actually need it.

Another thing to consider is to have a separate change log table for each of your actual tables if you aren't already planning to do that. Using a single log table makes it VERY difficult to work with. You usually have to log the information in a free-form text field which is difficult to query and process against. Also, it's difficult to look at data if you have a row for each column that has been changed because you have to do a lot of joins to look at changes that occur at the same time side by side.

Answer 3

With audit tables another approach is to archive the data once a month (or week depending on how much data you put in it) or so. That way if you need to recreate some recent changes with the fresh data, it can be done against smaller tables and thus more quickly (recovering from audit tables is almost always an urgent task I've found!). But you still have the data avialable in case you ever need to go back farther in time.

Answer 4

In addition to all the above, which are great reccomendations I thought I would give a bit more context on the index/performance point.

As mentioned above, it is not possible to give a performance number as depending on the quality and number of your indexes the performance will differ. It is also dependent on what operations you want to optimize. Do you need to optimize inserts? or are you more concerned about query response?

If you are truly concerned about insert speed, partitioning, as well a VERY careful index consideration is going to be key.

The separate table reccomendation of Tom H is also a good idea.