Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 14, 2003

A Comparison of SQL Server 2000 with Access 2000

By Alexander Chigrik

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,
plus 4 MB of RAM for each application running simultaneously,
plus memory for the operation system:
   
  • 16 MB of RAM for Windows 95 or Windows 98
  •    
  • 32 MB of RAM for Windows Me or Windows NT
  •    
  • 64 MB of RAM for Windows 2000
  •    
  • 128 MB of RAM for Windows XP
  • 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

    » See All Articles by Columnist Alexander Chigrik



    MS SQL Archives

    Comment and Contribute

     


    (Maximum characters: 1200). You have characters left.

     

     




    Latest Forum Threads
    MS SQL Forum
    Topic By Replies Updated
    SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
    Need help changing table contents nkawtg 1 August 17th, 03:02 AM
    SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
    SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















    Thanks for your registration, follow us on our social networks to keep up-to-date