How many records can SQL Server handle?
-
Hi all,
I've just received a mail from a MySQL is as follows
quote:
>> I tried to look everywhere @ mysql.com but i couldn't my question is
> how many records can a table handle so it stays fairly fast?
> What is the maximum records it can handle??
Up to 4 billion rows.
is there a similar (theoretical?) number for SQL Server?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
AFAIK, it's limited by disk space. No limit to number of rows.
Steve Jones
-
I currently have one table that has 734,371,580 rows. I don't see any problems/slowness because of the size.
-SQLBill
-
The big thing with MySQL at it's limit is threading is not quite there yet. You do not have multiple threads processing data at the same time.
SQL Server has a TerraBytes of size limit which you can find in the product documentation on the SQL website. But your speed will be affected by other factors such as poor indexing, number of concurrent users, IO bottlenecks (memory, hard drive, and even network), and other things. So your question actually has to take into account other factors besides the service.
-
Hi James,
quote:
SQL Server has a TerraBytes of size limit which you can find in the product documentation on the SQL website. But your speed will be affected by other factors such as poor indexing, number of concurrent users, IO bottlenecks (memory, hard drive, and even network), and other things. So your question actually has to take into account other factors besides the service.basically I was wondering about the lapidary answer 'Up to 4 billion rows'!
One should be careful with such answer, because of the side effects you've mentioned.
Another thing is, what is in the table? 2 fields, 10, 100?
What kind of fields and so on.
I know I have seen technical specifications on SQL Server in BOL. But can't find them right now.
Can someone post a keyword to look for?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
If you have access to SQL Server Books OnLine, in the index search for: limitations-SQL Server objects
You will see that the maximum rows for a table is limited by the available storage space.
The maximum (theoretical) size of a database is 1,048,516 TB, and not a single terrabyte as earlier suggested.
Regards
Kent
-
Hi Kent,
quote:
If you have access to SQL Server Books OnLine, in the index search for: limitations-SQL Server objectsthat was the page I've meant.
Thanks for waking me up!
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Quote: "The maximum (theoretical) size of a database is 1,048,516 TB, and not a single terrabyte as earlier suggested."
Don't forget you can have up to 32768 databases per server. It is also possible to spread your application over more than one server...:) The real limit to how much data you can store is the number of disk drives that physically exist in the world today.
The same answer (with a different combination of figures) applies to the other enterprise DBMSs.
All information provided is a personal opinion that may not match reality.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Interesting note:
* When creating a non-unique clustered index on a table, MSSQL adds a UNIQUE value to the non-unique data to make it unique.
Somewhere in BOL I read that this unique value is 4 bytes big.
Impacts? A non-unique clustered index on a TINYINT (Obviously I am not recommending this 😉 would give a RID of 5 bytes. A RID of 40 bits would mean a maximum of 2^40 = 1.099.511.627.776 UNIQUE RIDs (which of course is enough, considering prior discussions of Max DB size). Worst case scenario is if the clustered index contain the same value for all rows (which would nullify the additional RID space contributed from the index). In this case we would only be able to produce 4 bytes of UNIQUE RIDs. 2^32 is just enough to get the MySQL limit, earlier mentioned by Frank, roughly 4 billion. Would that not mean that in this scenario MS SQL cannot handle more then 4 billion rows (as in MySQL)?
Pointers on this one would be hughly appreciated!
Regards, Hans!
-
Hi Hans,
quote:
Interesting note:* When creating a non-unique clustered index on a table, MSSQL adds a UNIQUE value to the non-unique data to make it unique.
Somewhere in BOL I read that this unique value is 4 bytes big.
Impacts? A non-unique clustered index on a TINYINT (Obviously I am not recommending this 😉 would give a RID of 5 bytes. A RID of 40 bits would mean a maximum of 2^40 = 1.099.511.627.776 UNIQUE RIDs (which of course is enough, considering prior discussions of Max DB size). Worst case scenario is if the clustered index contain the same value for all rows (which would nullify the additional RID space contributed from the index). In this case we would only be able to produce 4 bytes of UNIQUE RIDs. 2^32 is just enough to get the MySQL limit, earlier mentioned by Frank, roughly 4 billion. Would that not mean that in this scenario MS SQL cannot handle more then 4 billion rows (as in MySQL)?
Pointers on this one would be hughly appreciated!
I've never heard of what you've mentioned above. But ot me it makes sense.
Why?
Just because, why should MySQL be so popular with such an 'obvious' limitation other matured DBMS don't have? But I'm curious to see, what other think of it!
BTW, I believe 4 billion rows are more than enough. And if not, I like EdVassies' comment to make another db
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi
The size is not an issues. I would think very carefully about:
a) number and sizes of indexes and where they will reside
b) plan to stripe this table over multiple raid sets perhaps? (say, single fg with 2 files on two different raid-5's?)
c) buffer cache "flooding" issues? read/write dynamics to this table
d) trans log issues and db recovery
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
shoot, sorry, my comment was for SQLBill - didnt read the posts carefully enough. Anyhow, SQLBill, with that honking great table, what did you do with it?? this sort of stuff would be a great article.
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply