Maximum Capacity of SQL Server – Some facts

We often take SQL Server for granted as long as we are running a higher edition (like Enterprise etc) assuming size is not a problem for some objects. Again we also hear many criticism from others (who most likely have no exposure on SQL Server) like SQL Server can not host a big enough database etc. Have you even wondered with one of these below questions?

What is the maximum size for a SQL Server database?

How many database SQL Server can host?

How many file groups or files a SQL Server database can have?

How many SQL Server instances can be installed on a machine?

How many parameters a stored procedure can have?

How many parameters a user defined function can have?

How many users can be connected to SQL server at a time?

How many columns a primary or foreign key can have?

How many columns a select or inserts statement can have on SQL Server?

How many rows a SQL Server table can have?

How many non clustered index a table can have?

I know this list can be rather very long. Hence you can visit at http://msdn.microsoft.com/en-us/library/ms143432.aspx to refer to Microsoft’s official Maximum Capacity Specifications for SQL Server for 2008 R2.

Some vital information:

SQL Server Database Engine object	Maximum sizes/numbers SQL Server (32-bit)	Maximum sizes/numbers  SQL Server (64-bit)
Columns per foreign key	16	16
Columns per primary key	16	16
Columns per SELECT statement	4,096	4,096
Columns per INSERT statement	4096	4096
Database size	524,272 terabytes	524,272 terabytes
Databases per instance of SQL Server	32,767	32,767
Filegroups per database	32,767	32,767
Files per database	32,767	32,767
File size (data)	16 terabytes	16 terabytes
File size (log)	2 terabytes	2 terabytes
Instances per computer	50 instances on a stand-alone server for all SQL Server editions. SQL Server supports 25 instances on a failover cluster.	50 instances  on a stand-alone  server. 25 instances  on a failover  cluster.
Nonclustered indexes per table	999	999
Rows per table	Limited by available storage	Limited by available  storage
User connections	32,767	32,767

Note: These information are collected from http://msdn.microsoft.com/en-us/library/ms143432.aspx