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
- “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.