SQL Server Maximum Capacity Specifications

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.