SQL Server Table Row Limits

Question

Are there strict limits on the number of rows in a table in a sql server table? I get the impression that the only limit is based on physical storage.

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

To give a little knowledge of the domain, we are considering using an audit table, which will record field changes for all tables in the database and wonder what types of walls we might encounter.

Answer 1

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

It is difficult to give any numbers, since it very much depends on your server hardware, configuration and efficiency of your requests.

For example, a simple select statement will work faster and show less degradation than a full-text search or an approximate search, as the number of lines grows.

Answer 2

BrianV is correct. It is difficult to give a rule because it is very different from how you will use the table, how it is indexed, the actual columns in the table, etc.

Regarding common practices ... for very large tables, you might consider splitting up. This can be especially useful if you find that for your journal you usually care about changes in the last 1 month (or 1 day, 1 week, 1 year, no matter what). Then you can archive the old parts of the data so that they are available if they are absolutely necessary, but they will not interfere, since you will almost never need it.

Another thing to consider is to have a separate change log table for each of your actual tables, if you are not planning on doing so already. Using a single log table makes it VERY difficult to work with. Usually you should register information in a free-form text field that is difficult to request and process. In addition, it is difficult to view the data if you have a row for each column that has been changed because you need to make many connections in order to look at the changes that happen simultaneously.

Answer 3

In addition to all of the above, which are great recommendations, I thought I would give a little more context for the index / performance point.

As mentioned above, it’s not possible to assign a performance number because performance will vary depending on the quality and quantity of your indexes. It also depends on what operations you want to optimize. Need to optimize inserts? Or are you more concerned about the request?

If you are really worried about insertion speed, separation, and VERY thorough index review will be key.

The recommended separate table Tom H is also a good idea.

Answer 4

With audit tables, another approach is to archive data once a month (or a week, depending on how much data you put into it) or so. Thus, if you need to recreate some recent changes with fresh data, this can be done against smaller tables and therefore faster (restoring from audit tables is almost always the urgent task I found!). But you still have avialable data if you ever need to return on time.