Home | Articles | Functions | Tips & Tricks | SQL Server 2005 | SQL Server 2008 | SQL Server 2012 | SQL Server 2014 | SQL Server 2016 | FAQ | Practice Test |    
Tip of the Day : Query String Parser Function
Home > SQL Server 2005 > Maximum Capacity Specifications
Maximum Capacity Specifications Comparison Table

Maximum Capacity Specifications Comparison Table

The following table compares the maximum sizes and numbers of various objects defined in SQL Server 7.0, SQL Server 2000 and SQL Server 2005 databases or referenced in Transact-SQL statements.  The table does not include Microsoft SQL Server 2000 Windows CE Edition and Microsoft SQL Server 2005 Windows CE Edition.
Maximum Sizes/Numbers
Object SQL Server 7.0 SQL Server 2000 SQL Server 2005 (32-bit)
Batch size 65,536 * Network Packet Size1 65,536 * Network Packet Size1 65,536 * Network Packet Size1
Bytes per short string column 8,000 8,000 8,000
Bytes per text, ntext, or image column 2 GB-2 2 GB-2 2 GB-2
Bytes per GROUP BY, ORDER BY 8,060 8,060 8,060
Bytes per index 900 9002 9002
Bytes per foreign key 900 900 900
Bytes per primary key 900 900 900
Bytes per row 8,060 8,060 8,0608
Bytes in source text of a stored procedure Lesser of batch size or 250 MB Lesser of batch size or 250 MB Lesser of batch size or 250 MB
Bytes per VARCHAR(MAX), VARBINARY(MAX), XML, TEXT, or IMAGEcolumn N/A N/A 2^31-1
Characters per NTEXT or NVARCHAR(MAX) column. N/A N/A 2^30-1
Clustered indexes per table 1 1 1
Columns in GROUP BY, ORDER BY Limited only by number of bytes per GROUP BY, ORDER BY Limited only by number of bytes per GROUP BY, ORDER BY Limited only by number of bytes per GROUP BY, ORDER BY
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement 10 10 10
Columns per index 16 16 167
Columns per foreign key 16 16 16
Columns per primary key 16 16 16
Columns per base table 1,024 1,024 1,024
Columns per SELECT statement 4,096 4,096 4,096
Columns per INSERT statement 1,024 1,024 1,024
Connections per client Maximum value of configured connections Maximum value of configured connections Maximum value of configured connections
Database size 1,048,516 TB 1,048,516 TB 1,048,516 TB
Databases per instance of SQL Server 32,767 32,767 32,767
Filegroups per database 256 256 32,767
Files per database 32,767 32,767 32,767
File size (data) 32 TB 32 TB 16 TB
File size (log) 4 TB 32 TB 2 terabytes
Foreign key table references per table4 253 253 253
Identifier length (in characters) 128 128 128
Instances per computer N/A 16 16
Length of a string containing SQL Statements (batch size) 65,536 * Network packet size 1 65,536 * Network packet size 1 65,536 * Network packet size 1
Locks per connection Maximum locks per server Maximum locks per server Maximum locks per server
Locks per instance of SQL Server 2,147,483,647 (static) 40% of SQL Server memory (dynamic) 2,147,483,647 (static) 40% of SQL Server memory (dynamic) Up to 2,147,483,6475
Nested stored procedure levels6 32 32 32
Nested subqueries 32 32 32
Nested trigger levels 32 32 32
Nonclustered indexes per table 249 249 249
Objects concurrently open in an instance of SQL Server3 2,147,483,647 (or available memory) 2,147,483,647 (or available memory)  
Objects in a database 2,147,483,6473 2,147,483,6473 2,147,483,6473
Parameters per stored procedure 1,024 1,024 2,100
Parameters per user-defined function 1,024 1,024 2,100
Partitions per partitioned table or index N/A N/A 1,000
REFERENCES per table 253 253 253
Rows per table Limited by available storage Limited by available storage Limited by available storage
Statistics on non-indexed columns     2,000
Tables per database Limited by number of objects in a database3 Limited by number of objects in a database3 Limited by number of objects in a database3
Tables per SELECT statement 256 256 256
Triggers per table Limited by number of objects in a database3 Limited by number of objects in a database3 Limited by number of objects in a database3
UNIQUE indexes or constraints per table 249 nonclustered and 1 clustered 249 nonclustered and 1 clustered 249 nonclustered and 1 clustered
XML indexes N/A N/A 249

1 Network Packet Size is the size of the tabular data stream (TDS) packets used to communicate between applications and the relational database engine.  The default packet size is 4 kilobytes (KB), and is controlled by the network packet size configuration option.

2 The maximum number of bytes in any index key cannot exceed 900 in SQL Server 2000 and SQL Server 2005.  You can define a key using variable-length columns whose maximum sizes add up to more than 900, provided no row is ever inserted with more than 900 bytes of data in those columns.  In SQL Server 2005, you can include nonkey columns in a nonclustered index to avoid the maximum index key size of 900 bytes.

3 Database objects include objects such as tables, views, stored procedures, extended stored procedures, user-defined functions, triggers, rules, defaults, and constraints.  The sum of the number of all objects in a database cannot exceed 2,147,483,647.

4 Although a table can contain an unlimited number of FOREIGN KEY constraints, the recommended maximum is 253.  Depending on the hardware configuration hosting SQL Server, specifying additional foreign key constraints may be expensive for the query optimizer to process.

5 This value is for static lock allocation.  Dynamic locks are limited only by memory.

6 If a stored procedure accesses more than 8 databases, or more than 2 databases in interleaving, you will receive an error.

7 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.  In SQL Server 2005, you can include nonkey columns in a nonclustered index to avoid the limitation of a maximum of 16 key columns.

8 SQL Server 2005 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; because of this, the effective row limit is higher than in previous releases of SQL Server.

References: