SQL syntax differences between SQL Server and access

 
Introduction: SQL Server and Microsoft Access are both Microsoft products. The former is used for medium-sized enterprise applications, and the latter is representative of small databases, which is easy for beginners to master. When developing general information systems, especially the standalone version, most of them use access. Although SQL server also has a personal desktop edition, it consumes a large amount of system resources during running.

In the situation where LAN is widely used, online software is widely used. Many software have transitioned from a standalone version to a network application in the C/S mode, and data sets are obtained from the server through SQL statements, return the query result to the client.

Access provides query objects. The design view and SQL view can be used during design, which is very convenient. SQL statements in the SQL view can be used in SQL Server, but is it completely available? The answer is no. Table 1 summarizes the important differences between Microsoft Access and Microsoft SQL servers SQL syntax.

Table 1

SQL syntax Element
Microsoft
Access
Microsoft SQL
Server
 
Identifier
It cannot exceed 64 characters.

Keywords and special characters are allowed.

It can start with any character.
For SQL Server 6.5:

It cannot exceed 30 characters.
Keywords and special characters are not allowed.
It must start with a letter.
The SQL Server 7.0 identifier is fully compatible with access.
 
Output Field
Multiple Output fields can have the same name.
Multiple output field names are not supported in the view.
 
Date separator number
Pound sign (#)
Marker (')
 
Boolean constant
True, false; On, off; yes, no.
Integer: 1 (true), 0 (false)
 
String connection
And number (&)
Plus sign (+)
 
Wildcard
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.
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.
 
Top
If there is an order by clause, the hierarchy is automatically included.
SQL Server 6.5 is not supported.

SQL Server 7.0 requires a clear with ties clause.
 
Create Index
Allows you to create ascending and descending indexes.

The primary key can be declared. There is no null value and the null value is ignored.

 
Drop Index
Syntax:

Drop index <index Name> On <Table Name>
Syntax:

Drop index <Table Name>, <index Name>
 
Distinctrow
Supported (a single record can be selected ).
Not supported.
 
Owneraccess
Supported (permission control during execution ).
Not supported.
 
Table in Union
Supported (the following syntax can be used to specify a table:

Table <tablename>
Not supported.
 
Order by in unions
Yes. Multiple sorts can be achieved by clauses in the Union query.
Yes. A Sort can be performed by clauses at the end of a statement.
 
Transform
Yes. Used for cross tabulation query.
Not supported.
 
Parameters
Supported (recorded in SQL ).
Not supported.
 

 

Take the northwind database as an example. To obtain the same query result, the SQL statement changes accordingly.

Access:

Select * from products where productname like 'C *';

Select * from products where productname like 'C? Ang ';

SQL Server:

Select * from products where productname like 'C %'

Select * from products where productname like 'C _ ang'

In the development process, pay special attention to the changes in wildcard characters.