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
Where there are differences in SQL Server 2005, I have bolded the entry
Batch size
65,536 * Network Packet Size
Bytes per short string column
8,000
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
8,060
Bytes per index
900
9001
Bytes per foreign key
??2
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
128MB
Clustered indexes per table
1
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
10
Columns per index
16
164 5
Columns per foreign key
164
Columns per primary key
Columns per base table
1,024
Columns per SELECT statement
4,096
Columns per INSERT statement
Connections per client
Maximum value of configured connections (32,767 max)
Database size
1,048,516 terabytes
Databases per instance of SQL Server
32,767
Filegroups per database
256
Files per database
File size (data)
32 terabytes
File size (log)
Foreign key table references per table
253
Identifier length (in characters)
128
Instances per computer
50
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
32
Nested subqueries
no limit, at least according to Beta 2 BOL
Nested trigger levels
Nonclustered indexes per table
249
Objects concurrently open in an instance of SQL Server
2,147,483,647 per database (depending on available memory)
Objects in a database
2,147,483,647
Parameters per stored procedure
2,100
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
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.
To read the rest of this article, and access thousands of other articles, we ask you to register on the site and subscribe to our newsletters.
We ask you to register on the site and subscribe to our newsletters. Subscribing to our newsletters gets you:
We ask that you give the newsletter a try for a week. Over 200,000 SQL Server Professionals a day find it entertaining and useful. If not, you are welcome to unsubscribe at anytime.
Steve Jones Editor, SQLServerCentral.com
