Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Maximum Capacity Specifications in SQL Server 2005

By Steve Jones, (first published: 2004/11/01)

Growing Up?

Every version of SQL Server has usually grown itself physically, allowing more capacity, scalability, etc. in each version. With the announcement of Yukon, now known as SQL Server 2005, I was excited to think about not having to answer the 8060 question. You know, how do you store more than 8060 bytes in a row. Actually you can't store that much, but you get close.

So I was surprised to see what some research into the new maximums of SQL Server 2005 didn't seem to show much growth. I included the 32-bit values, although I don't know if the 64-bit values are any different. My research started with books online for Beta 2, but the search didn't show up a Maximum Capacity page. So I went to Google, my first stop.

I stumbled upon this page for Maximum Capacity specifications for SQL Server 2005, but it appears to be a cut and past job of the MS page for SQL 7 and 2000. After double checking a few things, I decided it wasn't accurate for some reason. Now BOL has been known to have errors, and I wouldn't be surprised if much of it was cut and pasted from BOL 2000 and then edited, so don't take this information as the gospel, but based on my digging in places like BOL, MSDN, Technet, etc., this is what I pieced together.

The page size is the same, one that I wasn't sure would change, but I thought for sure they'd somehow allow a row to cross pages. The index sizes aren't bigger, although I'd be surprised if anyone is creating indexes this large. I think you're probably making a mistake if you are. It was nice to see instances go up, especially as more applications are starting to use MSDE. While I haven't seen anyone with more than 10 Access apps on a machine, I'm sure there are a few out there.

©dkranch.net 2004
Return to Steve Jones' home

The Specs

Where there are differences in SQL Server 2005, I have bolded the entry

Object Maximum sizes/numbers
SQL Server 7 (32-bit)
Maximum sizes/numbers
SQL Server 2000 (32-bit)
Maximum sizes/numbers
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

231 -2 bytes/p>

231 -2 bytes/p>

231 -1 bytes/p>

Bytes per GROUP BY, ORDER BY

8,060

8,060

8,060

Bytes per index

900

900

9001

Bytes per foreign key

900

900

??2

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

Lesser of batch size or 250 MB or 128MB3

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

164 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
1 - 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.

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

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

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

5 - Not valid for XML indexes.


Total article views: 40905 | Views in the last 30 days: 23
 
Related Articles
FORUM

MS SQL Server 2005 Limits

Maximum Records in a Table Limit

FORUM

Maximum size of a database that SQL Server 2005 clustered allows

Maximum size of a database that SQL Server 2005 clustered allows

FORUM

Maximum row size in SQL Server 2000.

Maximum row size in SQL Server 2000.

FORUM

Error : Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)" in SQL 2005

Error : Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)" in S...

FORUM

Size Limits?

Database mdf file size limits?

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones