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