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.
