What are the capacities of Access, SQL Server, and MSDE?

Here are some guidelines for database size, object number and naming limitations contrasted across Access 2000/XP, SQL Server 7.0, SQL Server 2000, and MSDE 2000. The main reason for this chart was to answer the question "how many rows can I get into a table?" -- which doesn't have a direct answer, but is limited by many of the variables listed below (not to mention disk space!). 
 

Parameter	Access 2000/XP	SQL Server 7.0	SQL Server 2000	MSDE 2000
Number of instances per server	n/a	n/a	16	16
Number of databases per instance / server	n/a	32,767	32,767	32,767
Number of objects per database	32,768	2,147,483,647	2,147,483,647	2,147,483,647
Number of users per database	n/a	16,379	16,379	16,379
Number of roles per database	n/a	16,367	16,367	16,367
Overall size of database (excluding logs)	2 GB	1,048,516 TB	1,048,516 TB1	2 GB
Number of columns per table	255	1024	1024	1024
Number of rows per table	limited by storage	limited by storage	limited by storage	limited by storage
Number of bytes per row (Excluding TEXT/MEMO/IMAGE/OLE)	2 KB	8 KB	8 KB	8 KB
Number of columns per query	255	4,096	4,096	4,096
Number of tables per query	32	256	256	256
Size of procedure / query	64 KB	250 MB	250 MB	250 MB
Number of input params per procedure / query	1995	1,024	2,100	2,100
Size of SQL statement / batch	64 KB	64 KB	64 KB	64 KB
Depth of subquery nesting	50	32	32	32
Number of indexes per table	32	250 (1 clustered)	250 (1 clustered)	250 (1 clustered)
Number of columns per index	10	16	16	16
Number of characters per object name	64	128	128	128
Number of concurrent user connections	2552	32,7673	32,7673	54

1. Using a federated database in SQL Server 2000, you can have 32,767 databases of 1,048,516 TB each, which is probably more space than anyone will ever need (though that phrase itself has proven dangerous to say <G>).
2. This is how many concurrent users Access will allow, however this number is much smaller when Access is used in a web-based environment (see Article #2195).
3. SQL Server allows 32,767 concurrent connections, or the number of licenses allowed, whichever is lower.
4. MSDE has a performance throttler that kicks in when more than 5 workloads / batches are being run at once (see Article #2343).
5. The Query Designer interface in Access limits you to 199 parameters, though it is possible (but not recommended) to create a query with even more parameters.