SQL Server: the maximum number of rows in table

It's hard to give a generic answer to this. It really depends on number of factors:

• what size your row is
• what kind of data you store (strings, blobs, numbers)
• what do you do with your data (just keep it as archive, query it regularly)
• do you have indexes on your table - how many
• what's your server specs

etc.

As answered elsewhere here, 100,000 a day and thus per table is overkill - I'd suggest monthly or weekly perhaps even quarterly. The more tables you have the bigger maintenance/query nightmare it will become.

I don't know MSSQL specifically, but 36 million rows is not large to an enterprise database - working with mainframe databases, 100,000 rows sounds like a configuration table to me :-).

While I'm not a big fan of some of Microsoft's software, this isn't Access we're talking about here: I assume they can handle pretty substantial database sizes with their enterprise DBMS.

I suspect days may have been too fine a resolution to divide it up, if indeed it needs dividing at all.

I do not know of a row limit, but I know tables with more than 170 million rows. You may speed it up using partitioned tables (2005+) or views that connect multiple tables.

It depends, but I would say it is better to keep everything in one table for that sake of simplicity.

100,000 rows a day is not really that much of an enormous amount. (Depending on your server hardware). I have personally seen MSSQL handle up to 100M rows in a single table without any problems. As long as your keep your indexes in order it should be all good. The key is to have heaps of memory so that indexes don't have to be swapped out to disk.

On the other hand, it depends on how you are using the data, if you need to make lots of query's, and its unlikely data will be needed that spans multiple days (so you won't need to join the tables) it will be faster to separate out it out into multiple tables. This is often used in applications such as industrial process control where you might be reading the value on say 50,000 instruments every 10 seconds. In this case speed is extremely important, but simplicity is not.

You can populate the table until you have enough disk space.

For better performance you can try migration to SQL Server 2005 and then partition the table and put parts on different disks (if you have RAID configuration that could really help you). Partitioning is possible only in enterprise version of SQL Server 2005. You can look at the partitioning example at this link.

Also you can try to create views for most used data portion, that is also one of the solutions.

SELECT Top 1 sysobjects.[name], max(sysindexes.[rows]) AS TableRows, 
  CAST( 
    CASE max(sysindexes.[rows]) 
      WHEN 0 THEN -0 
      ELSE LOG10(max(sysindexes.[rows])) 
    END 
    AS NUMERIC(5,2)) 
  AS L10_TableRows 
FROM sysindexes INNER JOIN sysobjects ON sysindexes.[id] = sysobjects.[id] 
WHERE sysobjects.xtype = 'U' 
GROUP BY sysobjects.[name] 
ORDER BY max(rows) DESC