SQL Server Express 与 MS Access
最近与我一起工作的一位同事告诉我，SQL Express 和 MS Access 本质上是同一件事；这似乎不是一个准确的说法。我知道您可以将 Access 转换为 SQL DB，也许在本质上它们是相似的，但我假设 SQL DB 引擎和用于运行 access 的引擎并不相同。不仅如此，我知道的SQL语句语法等也不一样。
我主要是想了解，以便对版本有更多了解。
A colleague I work with recently told me that SQL Express and MS Access were essentially the same thing; that does not seem to be an accurate statement. I know you can convert Access to a SQL DB and maybe under the covers they are similar, but I would assume that the SQL DB engine and what is used to run access are not the same. Not only that, but the SQL statement syntax, etc. I know are not the same.
I am mainly trying to understand so that I am more informed about the versions.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
嗯，不，不一样。
首先，我需要澄清一些术语。 MS Access 是一种快速应用程序开发 (RAD) 工具，可让您快速构建与关系数据绑定的表单和报告。它配备了基于文件的数据库引擎（Jet/ACE）。
Access RAD 工具可与许多不同的后端数据库（Jet、SQL Server、任何支持 ODBC 的数据库等）一起使用。我必须假设您的同事专门评论了 Jet/ACE，即 MS Access 使用的数据库引擎。
我认为 Jet/ACE 数据库引擎 和 MS SQL Server Express 是指Jet/ACE 基于文件，而SQL Server Express 使用客户端/服务器模型。这意味着 SQL Server Express 需要正在运行的服务来提供对数据存储的访问。在某些情况下，这可能会使部署变得复杂。
SQL Server Express 实际上只是 SQL Server 的缩减版本：最大数据库大小为 4GB（2008R2 中为 10GB），仅使用单个物理 CPU 等。施加这些限制是为了防止大型组织使用免费提供的 Express 版本代替完整的 SQL Server 安装。结果是 SQL Server Express 提供了真正无缝的 SQL Server 升级路径。 （一般来说）它也是比 Jet/ACE 更强大、功能更齐全的数据库管理系统。
相似点
差异
Um, no, not the same.
First off, I need to clear up some terminology. MS Access is a Rapid Application Development (RAD) tool that allows you to quickly build forms and reports that are bound to relational data. It comes with a file-based database engine (Jet/ACE).
Access the RAD tool can be used with many different backend databases (Jet, SQL Server, any db that supports ODBC, etc). I have to assume your colleague was specifically commenting on Jet/ACE, ie the database engine that MS Access uses.
I think the single biggest difference between the Jet/ACE database engine and MS SQL Server Express is that Jet/ACE is file-based and SQL Server Express uses a client/server model. This means that SQL Server Express requires a running service to provide access to the datastore. This can complicate deployment in some scenarios.
SQL Server Express is really just a throttled-back version of SQL Server: max database size of 4GB (10GB in 2008R2), only uses a single physical CPU, etc. These limitations are imposed to prevent large organizations from using the freely available Express edition in place of a full-blown SQL Server install. The upshot to this is that SQL Server Express offers a truly seamless upgrade path to SQL Server. It is also (generally speaking) a more robust and fully featured database management system then Jet/ACE.
Similarities
Differences
我想你的同事想到的是SQL Server CE，这是一个超轻量级嵌入式数据库，在数据库管理方面（IMO）仍然远远优于 Access。 SQL Express 甚至无法与 Access 进行比较而不冒犯前者。
I think what your colleague had in mind was SQL Server CE, which is a super-lightweight embedded database, which is still (IMO) far superior to Access in database-management aspect. SQL Express cannot even be compared with Access without offending the former.
以下是这两种产品的数据表，因此您可以看到有关这两种数据库之间差异的一些确凿事实。
使用权：
http://office.microsoft.com/en-us /access-help/access-specations-HP005186808.aspx
SQL（Express 列在最右列）：
http://www.microsoft.com/sqlserver/2008/en /us/editions-compare.aspx
我一直读到的评论是，Access 非常适合单用户单访问数据库使用，一旦您扩展到单用户之外，就可以看看其他地方。虽然这可能有点牵强，但 Access 在多用户环境中确实表现不佳。根据经验，我们有一个客户忽略并忽略了我们将后端数据库从 Access 迁移到 SQL 的请求，并且在很多情况下我们不得不从备份中恢复，或者由于损坏而使 Access 数据库脱机。
它们是两种完全不同的技术，具有两个不同的目标市场。数据库引擎确实不同，正如您提到的 T-SQL 与 Access SQL 不同。
您可以通过创建 SSIS 包或其他工具来执行导入，将 Access 数据库“扩展”为 SQL，但这会将 Access 架构和数据迁移到真正的 SQL 数据库。它的作用不仅仅是附加 Access 数据库等。
每当您需要“真正的”数据库时，我都强烈建议您查看通过 Access 提供的任何 SQL 版本。
Here are the datasheets for both products so you can see some hard facts on the difference between the two databases.
Access:
http://office.microsoft.com/en-us/access-help/access-specifications-HP005186808.aspx
SQL (Express is listed on the far right column):
http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx
The comment I have always read is that Access is great for single user single access database use, the minute you scale beyond a single user look elsewhere. While that may be a "bit" of a stretch, Access really does not do well in a multi-user environment. From experience we've had a client who has ignored and ignored our requests to migrate a backend database from Access to SQL, and there have been numerous occasions where we have had to restore from backups, or take the Access database offline due to corruption.
They are two completely different technologies with two different target markets. The database engines are indeed different, as you mention T-SQL is different than Access SQL.
You can "scale up" an Access database to SQL by creating an SSIS package or other tool to do the import, but this takes the Access schema and data and migrates it to a true SQL database. It does more than just attach the Access database or the like.
Anytime you need a "real" database I'd highly recommend looking at any of the SQL versions that are available over Access.
请记住，如果使用正确的方法，使用 MS-Access 就不会受到大小限制。例如，没有理由不拥有许多 2 到 4 Gig 表，每个表单独包含在自己的数据库中。您的 ODBC 应用程序可以打开与多个 MS-Access 数据库的连接并查询每个数据库中的单个表。因此，您可以拥有一个包含数万亿条记录的数据库，这些记录存储在多个 MDB 文件中。我工作过的一家公司使用单个 MS-Access 数据库来运行以 MS-Access 表单完成的问题跟踪系统。他们一次只能使用一个人，因为共享问题会锁定 MS-Access。我为数据库编写了一个 Win32 Perl 本机 Windows GUI 用户界面，该界面更擅长字段/记录验证，并且我的 ODBC 代码能够管理同时用户访问的连接。我通过 Perl 程序管理每个用户数据库的打开、读取、写入和关闭。我没有打开数据库。我没有为每个用户维护持久连接，而是仅维护足够长的连接以检索记录进行编辑。然后我关闭连接，直到将记录写回数据库为止。另外，我通过维护一个用户登录表来编写自己的记录锁定程序逻辑，该表包含用户当前正在编辑的记录的记录 ID，然后在不再编辑该记录时删除该条目。当另一个用户去编辑同一条记录时，程序会检查该记录当前是否已打开以供其他用户编辑。该系统运行完美。 MS-Access 永远不会通过 ODBC 和多用户访问而锁定。我什至将数据库的密码嵌入到我编译的 Perl 程序中，这样除了通过我的 Perl 程序之外，任何人都无法访问 Access 数据库中的数据。
Just remember that with MS-Access you don't have size limitations if you play your cards right. There is no reason, for example, not to have many 2 to 4 Gig tables each contained singularly in their own database. Your ODBC applications can open a connection to multiple MS-Access databases and query the single table in each. So you can have a database containing trillions of records, stored in multiple MDB files. One company I went to work for was using a single MS-Access database to run a issue tracking system done in MS-Access forms. They could only use it one person at a time because of sharing issues that would lock MS-Access up. I wrote a Win32 Perl native Windows GUI user-interface to the database that was better at field/record validation, and my ODBC code was able to manage the connection for simultaneous user access. I managed the opening and reading and writing and closing of the database for each user through my Perl program. I did not leave the database open. I did not maintain a persistent connection for each user, but instead only maintained a connection long enough to retrieve a record for edit. Then I closed the connection until it was time to write the record back to the database. Also, I wrote my own record locking program logic by maintaining a user login table that contained the record id of the record a user was currently editing, then erased that entry when no longer editing that record. When another user went to edit the same record, the program checked if that record was currently open for edit by another user. The system worked flawlessly. MS-Access never locked up via ODBC and multi-user access. I even embedded the password to the database in my compiled Perl program so that no one could get to the data in the Access database other than through my Perl program.