Microsoft Access Versus SQL Server

Here's a comparison of Microsoft Access and SQL Server from a technical perspective, as well as from a functionality standpoint.

  • Screenshot of the Microsoft Access 2016 Welcome Screen

    Microsoft Access

    Microsoft Access is a desktop relational database management system (RDBMS) that comes as part of the Office suite of products. It is often used by small businesses and corporate departments for building small database applications that serve a specific purpose.

    Access has lower specifications than SQL Server (see below), so there are many things it can't do that SQL Server can.

    It's for this reason that Access has traditionally been used for smaller applications, with a small number of users accessing it simultaneously.

    One reason you might choose to use Access over SQL Server is for compatibility/sharing. Many corporate environments and small businesses have Office installed. Not so many have SQL Server. You can often email an Access database file to someone and they can just double-click to open it.

    Access is generally a lot easier to use than SQL Server — especially for beginners. It has a uniform interface that is consistent with the rest of the Office suite which can be great if you're an Office user.

    Access also includes wizards that walk you through the process of doing something new.

    You can also create forms straight from within Access. Not something you can do with SQL Server — you'll need a separate application for that. Plus, generating a report is a lot easier in Access. In fact, forms and reports can be generated within a single click in Access.

    Another reason you might use Access instead of SQL Server is money. If you already have Access installed as part of the Microsoft Office suite, purchasing SQL Server is an extra expense that may not be necessary — depending on your situation. SQL Server can be quite expensive, depending on the licence (although there are free options available).

  • Screenshot of SQL Server Management Studio.

    SQL Server

    SQL Server is a more robust database management system than Access. SQL Server was designed to have many hundreds, or even thousands of users accessing it at any point in time. Microsoft Access on the other hand, doesn't handle this type of load very well.

    This makes SQL Server well suited for database driven websites. Access is not a suitable solution for a database driven website — unless it has a very small amount of traffic (like you and a few of your friends). Even then, you may find yourself getting errors due to multiple users trying to access the database at the same time.

    SQL Server is also often the database behind corporate CRMs, business inventories, and other mission-critical applications. The typical requirements of such applications go way beyond the capabilities of a desktop system like Access.

    SQL Server includes advanced database administration tools that enable organisations to schedule tasks, receive alerts, optimize databases, configure security accounts/roles, transfer data between other disparate sources, and much more.

    Many features are optional, and can be added during installation. Optional components include Reporting Services, Analysis Services, Master Data Services, Distributed Replay Controller, Data Quality Services, R Server, PolyBase Query Service for External Data, and many more.

    Even the main administration console (SQL Server Management Studio) isn't included with installation — it needs to be installed separately. The possible reasoning behind this is that most SQL Server installations are only accessed remotely via an application (at least, in a production environment). Administering SQL Server from the same machine is usually reserved for developers or DBAs in a development environment.

    Basically, SQL Server includes a lot of advanced features that many Access users will never need. But many of these advanced features are considered crucial for any medium to large scale business.

    However, just because it has many (optional) advanced features, doesn't mean that you have to reserve SQL Server for the big jobs. There are free editions available (Express and Developer editions) that can be perfect for smaller projects.

Technical Specifications & Limitations

Here's a look at some of the main differences in the limitations between Microsoft Access and SQL Server.

Attribute Access 2016 SQL Server 2016
Maximum database size (including all objects and data) 2 GB, minus the space needed for system objects. 524,272 terabytes
Maximum data size Whatever's left over after deducting the system objects from 2 GB. 16 terabytes
Maximum number of simultaneous users / concurrent connections 255 32,767
Maximum number of columns/fields per table 255

1,024 for nonwide tables

30,000 for wide tables

Number of characters in a Text field (Access) Bytes per varchar(max), varbinary(max), xml, text, or image column (SQL Server) 255 2^31-1
Number of characters in a Long Text field (Access), or per ntext or nvarchar(max) column (SQL Server)

65,535 when entering data through the user interface;

1 gigabyte of character storage when entering data programmatically.4,000 when the UnicodeCompression property of the fields is set to Yes. This limit also applies to OLE Object fields.

Number of indexes in a table 32 including indexes created internally to maintain table relationships, single-field and composite indexes.

999 nonclustered indexes per table.

8 indexes per memory-optimised table.

Number of characters in an SQL statement  Approximately 64,000*

65,536 * Network packet size

Network Packet Size is the size of the tabular data stream (TDS) packets used to communicate between applications and the relational Database Engine. The default packet size is 4 KB, and is controlled by the network packet size configuration option.

Number of objects in a database 32,768  2,147,483,647
Number of fields/columns per foreign key 10  16
Number of fields/columns per primary key  10 16
Number of fields/columns per index  10


If the table contains one or more XML indexes, the clustering key of the user table is limited to 15 columns because the XML column is added to the clustering key of the primary XML index.

In SQL Server, you can include nonkey columns in a nonclustered index to avoid the limitation of a maximum of 16 key columns.

 Number of fields/columns in a recordset/SELECT statement  255  4,096
Number of nested subqueries  50*  32

*Might be lower if the query includes multivalued lookup fields.


  1. "Access 2016 Specifications". Microsoft Office Support website. Retrieved 5 June 2016.
  2. "Maximum Capacity Specifications for SQL Server". SQL Server 2016 Technical Documentation. The Microsoft Developer Network.  Retrieved 5 June 2016.