Maximum values for various objects referenced by SQL Server

server| Object | Maximum maximum capacity description
New Information-September 2001

The first table describes for all Microsoft? SQL Server? The 2000 version has the same maximum capacity. The second and third tables describe the capacity that differs depending on the version of SQL Server 2000 and the operating system.

The following table describes the maximum (quantity or size) of the various objects that are defined in the Microsoft SQL Server database or referenced in Transact-SQL statements. Does the following table not contain Microsoft SQL Server Windows? CE Edition.
Maximum (quantity or Size) object SQL Server 7.0SQL Server 2000 Batch size 65,536 * network packet size 165,536 * Network packet size 1 The number of bytes per sorted string column 8,0008,000 each text, ntext, or The number of bytes in the image column is 2 GB-22 GB-2 each GROUP by, the number of bytes per order by 8,0608,060 the number of bytes per index 9009002 the number of bytes per foreign key 900900 bytes per primary key 900900 per line of bytes 8, 0608,060 the number of bytes in the source text of the stored procedure the smaller batch size, or the smaller of the size of MB batch, or MB. The number of clustered indexes in each datasheet 11GROUP by, the number of columns in the order by is limited by the byte number of each GROUP by, ORDER BY clause To limit the number of columns or expressions in a group by with CUBE or with ROLLUP statement by the number of bytes per group BY, or by clause 10 the number of columns per index 1616 the number of columns per foreign key 1616 the number of columns per primary key 1616 columns for each underlying datasheet Number 1,0241,024 the number of columns per SELECT statement 4,0964,096 the number of columns per INSERT statement 1,0241,024 the number of connections per client The maximum value of the connection has been configured the maximum database size for the connection 1,048,516 TB31, 048,516 TB3 The number of databases per SQL Server instance 32,76732,767 the number of filegroups per database 256256 The number of files per database 32,76732,767 file size (data) TB32 TB file Size (log) 4 TB32 TB foreign key table for each datasheet reference 253253 identifier length (in words characters) 128128 instances per computer Vacant 16 containing SQL statements string length (batch size) 65,536 * network packet size 165,536 * Network packet size 1 number of locks per connection maximum locks per server maximum number of locks per server 2,147,483,647 (static) of each SQL Server instance
SQL Server 40% Memory (dynamic) 2,147,483,647 (static)
SQL Server 40% Memory (dynamic) Nesting of stored procedures layer 3232 nested subqueries 3232 nesting triggers layer 3232 number of nonclustered indexes per datasheet 249249SQL The number of objects opened concurrently in the instance of the server 42, 147,483,647 (or available memory) 2,147,483,647 (or available) the number of objects in each database 2,147,483,64742,147,483,6474 the number of parameters per stored procedure 1,0242,100 each data table REFERENCE number 253253 The number of rows per datasheet is limited by available storage resources limited by available storage resources The number of data tables per database is limited by the number of objects in the database 4 is limited by the number of objects in the database 4 each SELECT The number of data tables for a statement 256256 the number of triggers per data table is limited by the number of objects in the database 4 limited by the number of objects in the database 4 the number of UNIQUE indexes per datasheet or constraints 249 nonclustered indexes and one clustered index 249 nonclustered indexes and one clustered index

1 network packet size is the size of the tabular Data Scheme (TDS) packet used for communication between the application and the relational database engine. The default packet size is 4 KB and is controlled by the network packet size configuration option.
2 in SQL Server 2000, the maximum number of bytes for any key cannot exceed 900. You can define keys using variable-length columns, which can be larger than 900 if you do not insert rows with more than 900 bytes of data in such columns. For more information, see the maximum value for an index key.
3 when using SQL Server Desktop Engine or Microsoft Data Engine (MSDE) 1.0, the size of the database cannot exceed 2 GB.
4 database objects include all tables, views, stored procedures, extended stored procedures, triggers, rules, default values, and constraints. The total number of objects in a database must not exceed 2,147,483,647.
The maximum number of processors supported by the SQL Server 2000 version
The following table lists the maximum number of processors that the database engine can use on symmetric multiprocessing (SMP) computers in each version of SQL Server 2000. The number of processors installed on the SQL Server computer can be greater than the number of processors required by this version of the database engine, but the database engine uses no more processors than the number specified in the following table. For example, you can install SQL Server Standard Edition on a Windows Advanced server computer with 8 processors, but the database engine uses no more than 4 processors.



Operating system

Enterprise Edition

Standard Edition

Personal Edition

Development version

Desktop Engine

SQL Server CE Enterprise Evaluation Microsoft Windows DataCenter3242322 vacant 32Windows Advanced Server84282 vacant 8Windows 2000 Server44242 vacant 4Windows Professional vacant vacant 222 vacant 2Microsoft Windows NT? 4.0 Server Enterprise 88282 vacant 8Windows NT 4.0 Server44242 vacant 4Windows NT 4.0 Workstation vacant vacant 222 Vacant 2Microsoft Windows 98 vacant Vacant 1 Use Deskto P Engine1 Vacant Vacant Microsoft Windows CE vacant vacant vacant vacant vacant 1 vacant
Maximum amount of physical memory supported by the SQL Server 2000 release
The following table lists the maximum amount of physical memory or RAM that the data engine in each SQL Server version 2000 can support.



Operating system

Enterprise Edition

Standard Edition

Personal Edition

Development version

Desktop Engine

SQL Server CE Enterprise Evaluation Windows DATACENTER64 GB2 GB2 GB64 GB2 GB vacant Gbwindows Pro Advanced Server8 GB2 GB2 GB8 GB2 GB Vacant 8 Gbwindows Server4 GB2 GB2 GB4 GB2 GB Vacant 4 Gbwindows 2 Professional vacant vacant 2 GB2 GB2 GB vacant gbwindows NT 4.0 Server Enterprise 3 G B2 GB2 GB3 GB2 GB Vacant 3 gbwindows NT 4.0 Server2 GB2 GB2 GB2 GB2 GB vacant 2 gbwindows NT 4.0 Workstation vacant vacant 2 GB2 GB2 GB vacant 2 GB