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.
-
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).
-
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. |
2^30-1 |
| 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 | 16 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.
Sources
- "Access 2016 Specifications". Microsoft Office Support website. Retrieved 5 June 2016.
- "Maximum Capacity Specifications for SQL Server". SQL Server 2016 Technical Documentation. The Microsoft Developer Network.  Retrieved 5 June 2016.