Microsoft Access and SQL Server are two relational database management systems from Microsoft. Each has its own strengths and weaknesses. There are many cases where Access is the ideal tool for the job. There are other times where a more sophisticated solution like SQL Server is more appropriate.

When trying to decide which one to use, a side-by-side comparison of the technical specifications of each system can help greatly. Below is a side-by-side comparison of some of the technical limitations of each system.

 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.

Note that technical specifications is only one of many factors you should take into consideration when selecting a RDBMS. Other factors to consider might include:

  • The features included in each RDBMS
  • Cost to purchase
  • Cost to maintain
  • Training requirements
  • Interoperability with other systems
  • Security requirements
  • Ability to customise

Sources

  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.