Introduction
Often people in newsgroups ask about some comparison of Microsoft
SQL Server and Microsoft Access. In this article, I compare Microsoft
SQL Server 2000 with Microsoft Access 2000 regarding hardware and
software requirements, price, features and products limits.
Platform comparison
SQL Server 2000 only works on Windows-based platforms, including Windows 9x,
Windows NT, Windows 2000 and Windows CE.
Microsoft Access 2000 can be installed under the following operating
systems: Microsoft Windows 95, Windows 98, Windows 98 Second Edition,
Windows Millennium Edition (Windows Me), Windows NT 4.0 with
Service Pack 6 (SP6), Windows 2000, or Windows XP or later.
Hardware requirements
To install SQL Server 2000, you should have the Intel or compatible
platforms and the following hardware:
| Hardware | Requirements |
|---|---|
| Processor | Pentium 166 MHz or higher |
| Memory | 32 MB RAM (minimum for Desktop Engine), 64 MB RAM (minimum for all other editions), 128 MB RAM or more recommended |
| Hard disk space | 270 MB (full installation), 250 MB (typical), 95 MB (minimum), Desktop Engine: 44 MB Analysis Services: 50 MB minimum and 130 MB typical English Query: 80 MB |
Microsoft Access 2000 is included in the Professional and Developer
Editions of Microsoft Office 2000, but can be purchased separately
also. If you install Access 2000 with other Office 2000 products,
the hardware requirement can be increased in comparison with the
single Access 2000 installation.
To install Microsoft Access 2000, you should have the following hardware:
| Hardware | Requirements |
|---|---|
| Processor | Pentium 75 MHz or higher |
| Memory | 8 MB of RAM required for Access 2000,
|
| Hard disk space | Access 2000 requires over 30 MB of hard disk space |
Software requirements
SQL Server 2000 comes in six editions: Enterprise, Standard, Personal,
Developer, Desktop Engine and SQL Server CE (a compatible version for
Windows CE) and requires the following software:
| Operating System | Enterprise Edition | Standard Edition | Personal Edition | Developer Edition | Desktop Engine | SQL Server CE |
|---|---|---|---|---|---|---|
| Windows CE | No | No | No | No | No | Yes |
| Windows 9x | No | No | Yes | No | Yes | No |
| Windows NT 4.0 Workstation with Service Pack 5 | No | No | Yes | Yes | Yes | No |
| Windows NT 4.0 Server with Service Pack 5 | Yes | Yes | Yes | Yes | Yes | No |
| Windows NT 4.0 Server Enterprise Edition with Service Pack 5 | Yes | Yes | Yes | Yes | Yes | No |
| Windows 2000 Professional | No | No | Yes | Yes | Yes | No |
| Windows 2000 Server | Yes | Yes | Yes | Yes | Yes | No |
| Windows 2000 Advanced Server | Yes | Yes | Yes | Yes | Yes | No |
| Windows 2000 DataCenter | Yes | Yes | Yes | Yes | Yes | No |
| Windows XP Professional | No | No | Yes | Yes | Yes | No |
In comparison with SQL Server 2000, Microsoft Access 2000 does not
have any editions. Access 2000 can be installed under the following
operation systems: Microsoft Windows 95, Windows 98, Windows 98
Second Edition, Windows Millennium Edition (Windows Me), Windows NT 4.0
with Service Pack 6 (SP6), Windows 2000, or Windows XP or later.
Price comparison
SQL Server 2000 is currently available under two licensing options:
- Processor license.
- Server/per-seat client access license (CAL).
The processor license requires a single license for each CPU in the
computer running SQL Server 2000 and includes unlimited client access.
You can buy this license when you do not know the number of the clients
(for example, if your users will connect to SQL Server 2000 through
the internet). This license usually is cheaper than Server/Per-Seat CAL
when there are many users connected to SQL Server databases.
The Server/per-seat client access license (CAL) requires a license for
the server and the licenses for each client device. You can use this
licensing option when the customers do not need access beyond the
firewall and the number of clients is low (for example, 10-20 users
for SQL Server 2000 Standard Edition or 30-40 users for SQL Server 2000
Enterprise Edition).
| Licensing Options | SQL Server 2000 Standard Edition | SQL Server 2000 Enterprise Edition |
|---|---|---|
| Processor | $4,999 per processor | $19,999 per processor |
| Server/Per-Seat CAL | with 5 CALs – $1,489 with 10 CALs – $2,249 | with 25 CALs – $11,099 |
Microsoft Access 2000 like Office 2000 is no longer available at retail
for individual purchases. Microsoft Access 2002 and Office XP can be
purchased now. The process of obtaining a previous version of a Microsoft
product is called “downgrading.” Downgrade rights apply to volume license
customers only.
This is the current price for Microsoft Access 2002. Because Access 2002
is included in the Professional and Developer Editions of Microsoft
Office XP, the prices of the Office XP Professional Edition and Office XP
Developer Edition are also included.
| Products | New User Price | Upgrade Price |
|---|---|---|
| Microsoft Access 2002 | $339 | $109 |
| Office XP Professional Edition | $579 | $329 |
| Office XP Developer Edition | $799 | $549 |
Features comparison
Microsoft Access 2000 falls into the desktop category and works best for
individuals and workgroups managing megabytes of data. In comparison with
SQL Server 2000, Access uses file-server architecture, rather than
client-server architecture. Access 2000 has many restrictions in comparison
with SQL Server 2000 and cannot be used in the case where you want to build a
stable and efficient system with many concurrent users.
Some SQL Server 2000 and Access 2000 restrictions:
| Feature | Access 2000 | SQL Server 2000 |
|---|---|---|
| SMP support | Not Supported | Supported |
| Tables | Relational tables | Relational tables, Temporary tables |
| Triggers | Not Supported | AFTER triggers, INSTEAD OF triggers |
| Procedures | Not Supported | Microsoft T-SQL statements |
| User-defined functions | Not Supported | Scalar functions, Inline table-valued functions, Multistatement table-valued functions |
| Views | Not Supported | Supported |
| Transaction logging | Not Supported | Supported |
| Recovery | Recovery to last backup | Recovery to last backup, recovery to the point of failure, recovery to a specific point in time |
| Integration with Windows NT security | Not Supported | Supported |
SQL Server 2000 and Access 2000 limits
Some SQL Server 2000 and Access 2000 products limits:
| Feature | SQL Server 2000 | Access 2000 |
|---|---|---|
| database size | 1,048,516 TB | 2 GB plus linked tables size |
| objects in a database | 2,147,483,647 | 32,768 |
| user name length | 128 | 20 |
| password length | 128 | 14 |
| table name length | 128 | 64 |
| column name length | 128 | 64 |
| index name length | 128 | 64 |
| Number of concurrent users | limited by available memory | 255 |
| columns per table | 1024 | 255 |
| table size | limited by available storage | 1 GB |
| number of indexes in a table | 250 | 32 |
| number of columns in an index | 16 | 10 |
| bytes per row | 8060 | 2000 |
| number of tables in a query | 256 | 32 |
| columns per SELECT statement | 4096 | 255 |
| nested subqueries | 32 | 50 |
| number of enforced relationships | 253 | 32 |
| SQL statement size | 65,536 * Network packet size (4 KB, by default) | approximately 64,000 |
Conclusion
If you need to make a scalability, security and robustness system,
you should use SQL Server instead of Access. Because Access 2000 does
not support atomic transactions, it does not guarantee that all changes
performed within a transaction boundary are committed or rolled back.
SQL Server is integrated with Windows NT security, but Access is not.
Access 2000 databases cannot be restored to the point of failure,
while SQL Server databases can be.
This makes administering Access 2000 databases more expensive than
administering SQL Server 2000 databases.
Therefore, you should use Access 2000 database, only when you need to store
a small amount of data in a single user (or few users) environment,
or when you have very low resources, such as memory or disk.
In other cases, use MSDE or SQL Server.
Literature
1. SQL Server 2000 Books Online
2. Office 2000 System Requirements
3. Maximum Capacity Specifications
4. Microsoft Access 2000 Maximum Capacities
5. Microsoft SQL Server: Microsoft Access 2000 Data Engine Options