Analyze the 10 major differences between SQL Syntax of SQL Server and access Database

During project creation, the most common databases are Sqlserver and Access databases! However, you will often find many SQL Execution Problems. Here we will sort out the differences and summarize the ten differences.
The ACCESS structure is simple and easy to handle, and can also meet the requirements of most website programs. It is also a test tool for beginners. ACCESS is a small database. Since ACCESS is a small database, it has its fundamental limitations:

1) The database is too large. Generally, when the ACCESS database reaches about 50 MB, the performance will drop sharply!

2) websites are frequently accessed. When more than 100 people are online, the processing speed may be affected!

3) the number of records is too large. When the number of records reaches about 0.1 million, the performance will drop sharply!

Microsoft's high-end solution for High and Low ACCESS: it switched to Sqlserver, but there are some differences in syntax.

1. Differences between select and update statements

Select and update are basically the same for single table operations. However, the difference between the update statement during multi-Table operations is that the ACCESS statement differs from the UPDATE statement in SQLSERVER:

UPDATE statements for updating multiple tables in SQLSERVER:
UPDATE Table1 SET a. Name = B. Name FROM Table1 a, Table2 B WHERE a. ID = B. ID;

SQL statements with the same functions in ACCESS:
UPDATE Table1 a, Table2 B SET a. Name = B. Name WHERE a. ID = B. ID;

Comparison: The UPDATE statement in ACCESS does not have the FROM clause. All referenced tables are listed after the UPDATE keyword, which is the same as mysql;

2. delete statement

In SQLSERVER: DELETE from Table1 where id> 1;

Access: DELETE * from Table1 where id> 1;

Comparison: In SQLSERVER, as long as the select statement in the select statement is consistent with the delete operation syntax, no *;

3. Differences between calculated fields after

SQLSERVER:
SELECT a. sum (num) as sum_num, sum (num) * num as all_num;

Access:
SELECT a, sum (num) as sum_num, sum_num * num as all_num;

The comparison result is as follows:

In SQLSERVER: The AS field cannot be used AS a database field for calculation.

Access: The AS field can be used AS a database field for calculation.

4. "." and 「!」 Difference

SQLSERVER:
SELECT Table1.a AS Table1a, Table2. B AS Table2b FROM Table1, Table2;

Access:
SELECT Table1! A AS Table1a, Table2! B AS Table2b FROM Table1, Table2;

Comparison :「!」 Rarely used. You can also use "." in ACCESS, but do not omit;

5. Date separator number

SQLSERVER: single quotes (')

Access in: Well number (#)

6. constants represented by Boolean

SQL Server: INTEGER: 1 (true), 0 (false)

In Access: True, False; On, Off; Yes, No; INTEGER:-1 (True), 0 (False ).

7. String connection

SQLSERVER: plus sign (+)

Access: and (&)

8. wildcard characters

SQLSERVER:

Percent (%) matches zero or more characters.

The underscore (_) matches a single character.

The prefix (^) indicates that it is not in the list.

No character corresponding to the pound character.

Access:

The asterisk (*) matches zero or more characters.

Question mark (?) Matches a single character.

Exclamation point (!) This means that the instance is not in the list.

The pound sign (#) indicates a single number.

9. DROP INDEX

In SQLSERVER: Drop Index <Table Name>. <Index Name>

Access: Drop Index <Index Name> ON <Table Name>

10. Add an ID column to the table

SQLSERVER: alter table <table Name> add <column Name> bigint identity () not null