SQL Server and Access Query Syntax

SQL Server and Access Query Syntax

SQL Server and Access Query Syntax The following table shows the corresponding differences between SQL Server and Access query syntax. Access query syntax SQL Server query syntax ORDER BY in queries ORDER BY in views not supported DISTINCTROW DISTINCT String concatenation with “&” String concatenation with “+” Supported clauses/operators: SELECT SELECT TOP N INTO FROM WHERE GROUP BY HAVING UNION (ALL) ORDER BY WITH OWNERACCESS Supported clauses/operators: SELECT SELECT TOP N INTO FROM WHERE GROUP BY HAVING UNION (ALL) ORDER BY COMPUTE FOR BROWSE OPTION Not Supported: COMPUTE, FOR BROWSE, OPTION Not Supported: WITH OWNERACCESS Aggregate functions: AVG COUNT(column) COUNT(*) MIN MAX FIRST LAST STDEV, STDEVP SUM VAR, VARP Aggregate functions: AVG([ALL | DISTINCT] expression) COUNT([ALL | DISTINCT] expression) COUNT(*) GROUPING (column_name) MAX(expression) MIN(expression) STDEV, STDEVP SUM([ALL | DISTINCT] expression) VAR, VARP Not supported: FIRST, LAST TRANSFORM (SELECT statement) PIVOT WITH ROLLUP, WITH CUBE on SELECT statements MAKE TABLE, ALTER TABLE Supported clauses: CONSTRAINT ADD COLUMN DROP COLUMN DROP INDEX Also, stand-alone statement: DROP INDEX CREATE TABLE, ALTER TABLE Supported clauses: CONSTRAINT ADD COLUMN DROP COLUMN Stand-alone statement: DROP INDEX Regards Mitesh