SQL Server – Maximum Capacity Specifications Comparison

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. The table has the technical specification comparison between SQL Server 7.0, SQL Server 2000 and SQL Server 2005 databases. Also this comparison will show the entire maximum and minimum specification of the various databases like SQL Server 7.0, SQL Server 2000 and SQL Server 2005 databases.

Maximum Sizes/Numbers

Object

SQL Server 7.0

SQL Server 2000

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, 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

900²

900²

Bytes per foreign key

900

900

900

Bytes per primary key

900

900

900

Bytes per row

8,060

8,060

8,060⁸

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

16⁷

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 table⁴

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 ¹

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

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,647⁵

Nested stored procedure levels⁶

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 Server³

2,147,483,647 (or available memory)

2,147,483,647 (or available memory)

Objects in a database

2,147,483,647³

2,147,483,647³

2,147,483,647³

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 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

XML indexes

N/A

N/A

249