Maximum capacity specification for SQL Server

 

SQL Server 2008 books online (this is a special post for users to search and view)

 

 

The following table specifies the maximum size and maximum number of objects defined in the SQL Server database or referenced in a Transact-SQL statement.

SQL Server database engine object	Maximum size/quantity-SQL Server (32-bit)	Maximum size/quantity-SQL Server (64-bit)
Batch Size 1	65,536 * network packet size	65,536 * network packet size
Number of bytes per short string Column	8,000	8,000
Number of bytes per group by and order	8,060	8,060
2 bytes per index key	900	900
Number of bytes for each foreign key	900	900
Number of bytes per primary key	900	900
8 bytes per line	8,060	8,060
Number of bytes in the Stored Procedure Source Text	Smaller in batch size or 250 MB	Smaller in batch size or 250 MB
EachVarchar (max),Varbinary (max),XML,TextOrImageNumber of bytes in a column	2 ^ 31-1	2 ^ 31-1
EachNtextOrNvarchar (max)Number of characters in a column	2 ^ 30-1	2 ^ 30-1
Number of clustered indexes for each data table	1	1
Number of columns in group by and order	Limited by the number of bytes	Limited by the number of bytes
Number of columns or expressions in a group by with cube or with rollup statement	10	10
Number of columns per index key 7	16	16
Number of columns for each foreign key	16	16
Number of columns of each primary key	16	16
Number of columns in a non-Wide Table	1,024	1,024
Number of columns in each Wide Table	30,000	30,000
Number of columns in each select statement	4,096	4,096
Number of columns in each insert statement	4096	4096
Number of connections per client	Maximum Value of the configured connection	Maximum Value of the configured connection
Database size	524,272 TB	524,272 TB
Number of databases per SQL server instance	32,767	32,767
Number of file groups per Database	32,767	32,767
Number of files in each database	32,767	32,767
File size (data)	16 TB	16 TB
File size (log)	2 TB	2 TB
Number of foreign key table references for each table 4	253	253
Identifier length (in characters)	128	128
Number of instances per computer	All SQL Server versions (except workgroup) have 50 instances on an independent server. Workgroup supports a maximum of 16 instances on each computer. SQL Server supports 25 instances in a Failover cluster.	50 instances on the independent server. 25 instances in the Failover cluster.
Length of the string containing the SQL statement (batch size) 1	65,536 * network packet size	65,536 * network packet size
Number of locks for each connection	Maximum number of locks per server	Maximum number of locks per server
The number of locks per SQL server instance is 5	2,147,483,647 at most	Limited by memory only
Nested stored procedure level 6	32	32
Number of nested subqueries	32	32
Nested trigger Layers	32	32
Number of non-clustered indexes for each data table	999	999
Number of non-repeated expressions in the group by clause if any of the following Clauses exist: Cube, rollup, grouping sets, with cube, with Rollup	32	32
Number of grouping sets generated by operators in the group by clause	4,096	4,096
Number of parameters for each stored procedure	2,100	2,100
Number of parameters for each user-defined function	2,100	2,100
Number of references for each data table	253	253
Number of rows in each data table	Restricted by available storage space	Restricted by available storage space
3 tables per Database	Limited by the number of objects in the database	Limited by the number of objects in the database
Number of partitions of each partition table or index	1,000	1,000
Number of non-index column statistics	30,000	30,000
Number of tables for each select statement	256	256
Trigger count per table: 3	Limited by the number of objects in the database	Limited by the number of objects in the database
Number of columns in each update Statement (wide table)	4096	4096
User connection	32,767	32,767
XML Index	249	249

1. The network packet size refers to the data packet size of the table format data stream (TDS) used for communication between applications and the relational database engine. The default packet size is 4 kb, which is controlled by the "Network packet size" option.

2 in SQL Server, the maximum number of bytes of any index key cannot exceed 900. You can use a variable-length column with a maximum size of more than 900 to define the key, provided that the data of the inserted rows in these columns does not exceed 900 bytes. In SQL Server, You can include non-key columns in non-clustered indexes to avoid the limit of 900 bytes for the maximum index key size.

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

4. Although tables can contain an infinite number of foreign key constraints, we recommend that you set the maximum number to 253. According to the hardware configuration that carries the SQL Server, specifying the extra foreign key constraints may cause high overhead for the query optimizer.

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

6. If there are more than eight databases accessed by the stored procedure, or there are more than two databases accessed by alternate means, the system will receive an error message.

7. If a table contains one or more XML indexes, the clustering key of the XML column is limited to 15 columns because it is added to the primary XML index. In SQL Server, non-clustered indexes can contain non-key columns to avoid the limit of up to 16 key columns. For more information, see index with included columns.

8. SQL Server supports row overflow storage. Row overflow Storage enables columns with variable length to be pushed out of the row. Only the 24-byte root is stored in the master record of variable-length columns outside the push trip. Therefore, the valid row limit in this version is higher than the valid row limit in earlier versions of SQL Server. For more information, see the "Row overflow data exceeds 8 KB" topic in SQL Server books online.

 

 

The following table specifies the maximum size and maximum number of objects defined in SQL Server replication.

SQL Server replication object	Maximum size/quantity-SQL Server (32-bit)	Maximum size/quantity-SQL Server (64-bit)
Project (merged and released)	256	256
Project (snapshot release or transaction release)	32,767	32,767
Column 1 in the table (merge and publish)	246	246
Number of columns in the table 2 (SQL Server snapshot release or transaction release)	1,000	1,000
Column 2 in the table (Oracle snapshot release or transaction release)	995	995
Number of bytes of the columns used in the row filter (merged and published)	1,024	1,024
Number of bytes of the columns used in the row filter (snapshot release or transaction release)	8,000	8,000

1 If you use row tracking for conflict detection (default setting), the base table can contain a maximum of 1,024 columns, but you must filter these columns from the project to publish a maximum of 246 columns. If column tracing is used, the base table can contain a maximum of 246 columns. For more information about the trace level, see the "Trace Level" section of how merge replication detects and resolves conflicts.

2. A base table can contain the maximum number of columns allowed in the Publishing Database (1024 in SQL Server). If the number of these columns exceeds the maximum value specified for the publishing type, you must filter these columns from the project.