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 Object | Maximum on SQL Server 6.5 | Maximum on SQL Server 7.0 | Maximum on SQL Server 2000 | Maximum on SQL Server 2005 | Maximum on SQL Server 2008 | Maximum on SQL Server 2012 |
Database size | 1 TB | 1,048,516 TB | 524,258 TB | 524,272 TB |
Databases per instance of SQL Server | 32,767 |
Filegroups per database | n.a. | 256 | 32,767 |
Files per database | 32 | 32,767 |
File size (data) | 32 GB | 32 TB | 16 TB |
File size (log) | 32 GB | 4 TB | 32 TB | 2 TB |
Tables and Columns
Database Engine Object | Maximum on SQL Server 6.5 | Maximum on SQL Server 7.0 | Maximum on SQL Server 2000 | Maximum on SQL Server 2005 | Maximum on SQL Server 2008 | Maximum on SQL Server 2012 |
Bytes per row | 1962 | 8,060 | 8,060 *1 |
Bytes per short string column | 255 | 8,000 |
Bytes per varchar(max) , varbinary(max) , xml , text , or image column | 2GB - 2 *2 | 2GB - 1 |
Characters per ntext or nvarchar(max) column | 2GB - 2 *3 |
Columns per nonwide table | 250 | 1,024 |
Columns per wide table | n.a. | 30,000 |
Identifier length (in characters) | 30 | 128 |
Rows per table | Limited by available storage |
Tables per database | 2 billion | Limited by number of objects in a database *6 |
Partitions per partitioned table or index | n.a. | 1,000 | 15,000 |
Triggers per table | 3 | Limited by number of objects in a database *6 |
DRI Constraints
Database Engine Object | Maximum on SQL Server 6.5 | Maximum on SQL Server 7.0 | Maximum on SQL Server 2000 | Maximum on SQL Server 2005 | Maximum on SQL Server 2008 | Maximum on SQL Server 2012 |
Bytes per foreign key | 900 |
Bytes per primary key | 900 |
Columns per foreign key | 16 |
Columns per primary key | 16 |
Foreign key table references per table | 16 | 253 | unlimited |
Indexes
Database Engine Object | Maximum on SQL Server 6.5 | Maximum on SQL Server 7.0 | Maximum on SQL Server 2000 | Maximum on SQL Server 2005 | Maximum on SQL Server 2008 | Maximum on SQL Server 2012 |
Clustered indexes per table | 1 |
Nonclustered indexes per table | 249 | 999 |
XML indexes | n.a. | 249 |
Bytes per index key | 900 |
Columns per index key | 16 *4 |
Statistics on non-indexed columns | n.a. | 2,000 | 30,000 |
Programming
Database Engine Object | Maximum on SQL Server 6.5 | Maximum on SQL Server 7.0 | Maximum on SQL Server 2000 | Maximum on SQL Server 2005 | Maximum on SQL Server 2008 | Maximum on SQL Server 2012 |
Batch size | 128 KB | 65,536 * Network Packet Size |
Bytes per GROUP BY, ORDER BY | 900 | 8,060 |
Bytes in source text of a stored procedure | 65,025 | Lesser of batch size or 250 MB |
Columns in GROUP BY, ORDER BY | 16 | Limited only by number of bytes |
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement | 10 |
Distinct expressions in a GROUP BY when present: CUBE, ROLLUP, GROUPING SETS, WITH CUBE or WITH ROLLUP | n.a. | 32 |
Grouping sets generated by operators in the GROUP BY clause | n.a. | 4,096 |
Columns per SELECT statement | 4,096 |
Columns per INSERT statement | 250 | 1,024 | 4,096 |
Nested stored procedure levels | 16 | 32 |
Nested subqueries | 16 | 32 |
Nested trigger levels | 16 | 32 |
Parameters per stored procedure | 255 | 1024 | 2,100 |
Parameters per user-defined function | n.a. | 2,100 |
Tables per SELECT statement | 16 | 256 | Limited only by available resources *6 |
Columns per UPDATE statement (Wide Tables) | n.a. | 4,096 |
Other
Database Engine Object | Maximum on SQL Server 6.5 | Maximum on SQL Server 7.0 | Maximum on SQL Server 2000 | Maximum on SQL Server 2005 | Maximum on SQL Server 2008 | Maximum on SQL Server 2012 |
Instances per computer | n.a. | 16 | 50 on stand-alone server, 25 on a failover cluster |
Locks per instance of SQL Server | Up to 2,147,483,647 | Up to 2,147,483,647 *5 |
User connections | ? | 32,767 |