Often people in newsgroups ask about some comparison of Access and SQL Server databases. In this article, I want to tell you about some general characteristics of Access 97/2000 and SQL Server 7.0/2000, about their restrictions, and about the comparison of these products.
Access 97/2000
Microsoft offers a full family of database tools for the desktop, the server, and for open connectivity. For the desktop Microsoft offers Microsoft Access 97/2000.
The Microsoft Access 97/2000 is a full-featured multiuser relational database management system that designed for the Microsoft Windows operating systems (such as Windows 9x, Windows NT, Windows 2000). Access 97/2000 is extremely visually oriented and easy to use. It makes extensive use of drag-and-drop and visual design for queries, forms, and reports.
Access 97/2000 comes with an integrated development environment (IDE), including incremental compilation, a fully interactive visual debugger, breakpoints, and single step-through. These capabilities combine to make Microsoft Access an extremely powerful platform for developing client-server database solutions.
There are general Access 97/2000 characteristics:
| Object | Maximum sizes/numbers |
| Database size |
1 Gb |
| Number of characters in an object name |
64 |
| Number of characters in a password |
14 |
| Number of characters in a user name or group name |
20 |
| Number of concurrent users |
255 |
| Number of characters in a table name |
64 |
| Number of characters in a field name |
64 |
| Number of fields in a table |
255 |
| Number of characters in a Text field |
255 |
| Number of characters in a Memo field |
65,535 / 1 Gb |
Though the maximum database size is 1 Gb, but your database can include linked tables in other files, and its total size is limited only by available storage capacity.
Number of characters in a Memo field can be up to 65,535 characters when entering data through the user interface or can be up to 1 Gb when entering data programmatically.
Access 2000 can lock on the row level in comparison with Access 97 page level's lock.
SQL Server 7.0/2000
For the server engine in client-server solutions Microsoft provides the Microsoft SQL Server 7.0/2000.
There are no many differences in the internal architecture between SQL Server 7.0 and SQL Server 2000. SQL Server 2000 has the same features as SQL Server 7.0: autogrow features, new storage engine, complete row level locking and so on. So, the general characteristics of these versions are the same.
There are general SQL Server 97/2000 characteristics:
| Object | Maximum sizes/numbers |
| Batch size |
65,536 * Network Packet Size |
| Bytes per short string column |
8,000 |
| Bytes per text, ntext,or image column |
2 GB-2 |
| Bytes per index |
900 |
| Bytes per foreign key |
900 |
| Bytes per primary key |
900 |
| Bytes per row |
8,060 |
| Bytes in source text of a stored procedure |
Lesser of batch size or 250 MB |
| Clustered indexes per table |
1 |
| Columns per index |
16 |
| Columns per foreign key |
16 |
| Columns per primary key |
16 |
| Columns per base table |
1,024 |
| Columns per SELECT statement |
4,096 |
| Columns per INSERT statement |
1,024 |
| Connections per client |
Maximum value of configured connections |
| Database size |
1,048,516 TB |
| Databases per instance of SQL Server |
32,767 |
| Filegroups per database |
256 |
| Files per database |
32,767 |
| File size (data) |
32 TB |
| Identifier length (in characters) |
128 |
| Locks per connection |
Max. locks per server |
| Nested stored procedure levels |
32 |
| Nested subqueries |
32 |
| Nested trigger levels |
32 |
| Nonclustered indexes per table |
249 |
| Objects in a database |
2,147,483,6474 |
| Parameters per stored procedure |
1,024 |
| REFERENCES per table |
253 |
| Rows per table |
Limited by available storage |
| Tables per database |
Limited by number of objects in a database |
| Tables per SELECT statement |
256 |
| Triggers per table |
Limited by number of objects in a database |
| UNIQUE indexes or constraints per table |
249 nonclustered and 1 clustered |
SQL Server 2000 Enterprise Edition on the Windows 2000 DataCenter can support up to 32 CPU and up to 64GB physical memory (RAM) on a computer!
