Thank this author by sharing:
By Steve Jones,
2007/01/26 (first published: 2004/11/01)
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.
Return to Steve Jones' home
Where there are differences in SQL Server 2005, I have bolded the entry
65,536 * Network Packet Size
Bytes per short string column
Bytes per text, ntext, image, varchar(max), nvarchar(max), varbinary(max), or XML column
231 -2 bytes/p>
231 -1 bytes/p>
Bytes per GROUP BY, ORDER BY
Bytes per index
Bytes per foreign key
Bytes per primary key
900 - Not listed, but since this will be an index, the index guidelines should apply.
Bytes per row
Bytes in source text of a stored procedure
Lesser of batch size or 250 MB or 128MB3
Clustered indexes per table
Columns in GROUP BY, ORDER BY
Limited only by number of bytes
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement
Columns per index
Columns per foreign key
Columns per primary key
Columns per base table
Columns per SELECT statement
Columns per INSERT statement
Connections per client
Maximum value of configured connections (32,767 max)
Databases per instance of SQL Server
Filegroups per database
Files per database
File size (data)
File size (log)
Foreign key table references per table
Identifier length (in characters)
Instances per computer
Length of a string containing SQL statements (batch size)
65,536 * Network packet size
Locks per connection
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
no limit, at least according to Beta 2 BOL
Nested trigger levels
Nonclustered indexes per table
Objects concurrently open in an instance of SQL Server
2,147,483,647 per database (depending on available memory)
Objects in a database
Parameters per stored procedure
Parameters per user-defined function
REFERENCES per table
Rows per table
Limited by available storage
Tables per database
Limited by number of objects in a database
Tables per SELECT statement
Triggers per table
UNIQUE indexes or constraints per table
249 nonclustered and 1 clustered
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.
Maximum Records in a Table Limit
Maximum size of a database that SQL Server 2005 clustered allows
Maximum row size in SQL Server 2000.
Error : Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)" in S...
Database mdf file size limits?
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.