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

Comments

Popular Posts