SQL Server Maximum Capacity

SQLLeader.com

Object

Maximum sizes/numbers

Maximum sizes/numbers

Maximum sizes/numbers

SQL Server 7 (32-bit)

SQL Server 2000 (32-bit)

SQL Server 2005 (32-bit)

Batch size

65,536 * Network Packet Size

65,536 * Network Packet Size

65,536 * Network Packet Size

Bytes per short string column

8,000

8,000

8,000

Bytes per text, ntext, image, varchar(max), nvarchar(max), varbinary(max), or XML column

2³¹ -2 bytes

2³¹ -2 bytes

2³¹ -1 bytes

Bytes per GROUP BY, ORDER BY

8,060

8,060

8,060

Bytes per index

900

900

9001

Bytes per foreign key

900

900

??²

Bytes per primary key

900

900

900 - Not listed, but since this will be an index, the index guidelines should apply.

Bytes per row

8,060

8,060

8,060

Bytes in source text of a stored procedure

Lesser of batch size or 250 MB or 128MB³

Lesser of batch size or 250 MB or 128MB³

128MB

Clustered indexes per table

1

1

1

Columns in GROUP BY, ORDER BY

Limited only by number of bytes

Limited only by number of bytes

Limited only by number of bytes

Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement

10

10

10

Columns per index

16

16

16^{4 5}

Columns per foreign key

16

16

164

Columns per primary key

16

16

164

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 (32,767 max)

Maximum value of configured connections (32,767 max)

Maximum value of configured connections (32,767 max)

Database size

1,048,516 terabytes

1,048,516 terabytes

1,048,516 terabytes

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 terabytes

32 terabytes

32 terabytes

File size (log)

32 terabytes

32 terabytes

32 terabytes

Foreign key table references per table

253

253

253

Identifier length (in characters)

128

128

128

Instances per computer

16

16

50

Length of a string containing SQL statements (batch size)

65,536 * Network packet size

65,536 * Network packet size

65,536 * Network packet size

Locks per connection

Maximum locks per server

Maximum locks per server

Maximum locks per server

Locks per instance of SQL Server

Up to 2,147,483,647

Limited only by memory

Limited to 60% of memory

Nested stored procedure levels

32

32

32

Nested subqueries

32

32

no limit, at least according to Beta 2 BOL

Nested trigger levels

32

32

32

Nonclustered indexes per table

249

249

249

Objects concurrently open in an instance of SQL Server

2,147,483,647 per database (depending on available memory)

2,147,483,647 per database (depending on available memory)

2,147,483,647 per database (depending on available memory)

Objects in a database

2,147,483,647

2,147,483,647

2,147,483,647

Parameters per stored procedure

2,100

2,100

2,100

Parameters per user-defined function

2,100

2,100

2,100

REFERENCES per table

253

253

253

Rows per table

Limited by available storage

Limited by available storage

Limited by available storage

Tables per database

Limited by number of objects in a database

Limited by number of objects in a database

Limited by number of objects in a database

Tables per SELECT statement

256

256

256

Triggers per table

Limited by number of objects in a database

Limited by number of objects in a database

Limited by number of objects in a database

UNIQUE indexes or constraints per table

249 nonclustered and 1 clustered

249 nonclustered and 1 clustered

249 nonclustered and 1 clustered

Footnotes

¹ - By including nonkey columns in the index, you can exceed the 900 byte limit as these columns (used in covering queries) are not computed as part of the 900 byte limit.

² - I could not find this listed in the SQL Server 2005 Beta 2 BOL.

³ - The Maximum Capacity Specifications shows the less or the batch size or 250MB, however Books Online shows 128MB in the entry for

⁴ - The 16 column limit is for key columns. Additional columns can be included (as in footnote 1) beyond the 15.

⁵ - Not valid for XML indexes.