Comparison of Access 97/2000 with SQL Server 7.0/2000 database

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!

Add Feedback