Maximum Capacity Information

16-FEB-2013
How many column can I put in an index?

What is the maximum size of an index key?

How many indexes can I have?

The tables below answer all these questions!

Database
Database Engine ObjectMaximum on SQL Server 6.5Maximum on SQL Server 7.0Maximum on SQL Server 2000Maximum on SQL Server 2005Maximum on SQL Server 2008Maximum on SQL Server 2012
Database size1 TB1,048,516 TB524,258 TB524,272 TB
Databases per instance of SQL Server32,767
Filegroups per databasen.a.25632,767
Files per database3232,767
File size (data)32 GB32 TB16 TB
File size (log)32 GB4 TB32 TB2 TB
Tables and Columns
Database Engine ObjectMaximum on SQL Server 6.5Maximum on SQL Server 7.0Maximum on SQL Server 2000Maximum on SQL Server 2005Maximum on SQL Server 2008Maximum on SQL Server 2012
*1 SQL Server supports row-overflow storage which enables variable length columns to be pushed off-row. Only a 24-byte root is stored in the main record for variable length columns pushed out of row.
*2 varchar(max), varbinary(max) and xml data types were introduced in SQL Server 2005.
*3 nvarchar(max) data type was introduced in SQL Server 2005.
*6 The total number of all objects in a database cannot exceed 2,147,483,647.
Bytes per row19628,0608,060 *1
Bytes per short string column2558,000
Bytes per varchar(max), varbinary(max), xml, text, or image column2GB - 2 *22GB - 1
Characters per ntext or nvarchar(max) column2GB - 2 *3
Columns per nonwide table2501,024
Columns per wide tablen.a.30,000
Identifier length (in characters)30128
Rows per tableLimited by available storage
Tables per database2 billionLimited by number of objects in a database *6
Partitions per partitioned table or indexn.a.1,00015,000
Triggers per table3Limited by number of objects in a database *6
DRI Constraints
Database Engine ObjectMaximum on SQL Server 6.5Maximum on SQL Server 7.0Maximum on SQL Server 2000Maximum on SQL Server 2005Maximum on SQL Server 2008Maximum on SQL Server 2012
Bytes per foreign key900
Bytes per primary key900
Columns per foreign key16
Columns per primary key16
Foreign key table references per table16253unlimited
Indexes
Database Engine ObjectMaximum on SQL Server 6.5Maximum on SQL Server 7.0Maximum on SQL Server 2000Maximum on SQL Server 2005Maximum on SQL Server 2008Maximum on SQL Server 2012
*4 If the table contains one or more XML indexes, the clustering key of the user table is limited to 15 columns because the XML column is added to the clustering key of the primary XML index.
Clustered indexes per table1
Nonclustered indexes per table249999
XML indexesn.a.249
Bytes per index key900
Columns per index key16 *4
Statistics on non-indexed columnsn.a.2,00030,000
Programming
Database Engine ObjectMaximum on SQL Server 6.5Maximum on SQL Server 7.0Maximum on SQL Server 2000Maximum on SQL Server 2005Maximum on SQL Server 2008Maximum on SQL Server 2012
Batch size128 KB65,536 * Network Packet Size
Bytes per GROUP BY, ORDER BY9008,060
Bytes in source text of a stored procedure65,025Lesser of batch size or 250 MB
Columns in GROUP BY, ORDER BY16Limited only by number of bytes
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement10
Distinct expressions in a GROUP BY when present: CUBE, ROLLUP, GROUPING SETS, WITH CUBE or WITH ROLLUPn.a.32
Grouping sets generated by operators in the GROUP BY clausen.a.4,096
Columns per SELECT statement4,096
Columns per INSERT statement2501,0244,096
Nested stored procedure levels1632
Nested subqueries1632
Nested trigger levels1632
Parameters per stored procedure25510242,100
Parameters per user-defined functionn.a.2,100
Tables per SELECT statement16256Limited only by available resources *6
Columns per UPDATE statement (Wide Tables)n.a.4,096
Other
Database Engine ObjectMaximum on SQL Server 6.5Maximum on SQL Server 7.0Maximum on SQL Server 2000Maximum on SQL Server 2005Maximum on SQL Server 2008Maximum on SQL Server 2012
*5 This value is for static lock allocation on 32-bit installations. Dynamic locks or 64-bit installations are limited only by memory. Dynamic locks limited to 40% of memory for SQL Server 7.0 and 2000
Instances per computern.a.1650 on stand-alone server, 25 on a failover cluster
Locks per instance of SQL ServerUp to 2,147,483,647Up to 2,147,483,647 *5
User connections?32,767


Back to SQL Server main menu. Mail your comments to gertjans@xs4all.nl.