Wednesday, May 15, 2013

What are the differences between Access SQL and T-SQL (SQL Server)?

Access SQL and SQL Server's T-SQL have more in common than differences. They share much the same structure, syntax, and many functions. And yet for all that, the differences can be frustrating. There are many times when an experienced Access user will know exactly how to do what they want in Access, but cannot figure out how to do the same thing in T-SQL.

This is especially true when trying to convert an Access query to a Pass-Through query. A Pass-Through query passes the SQL Statement on directly to the Server database (ie: SQL Server, Oracle, etc.). The problem is it must be in the syntax used by the Server database.

 

Basic Differences

I'll start by just listing the basics, but I'll spend some time over the next few months expanding on them. The following is certainly not exhaustive, but they are differences that cause most of the confusion when first trying to use T-SQL.

 

Delimiters

  Type

  Access

  T-SQL

  Numeric   no delimiter   no delimiter

  Text

  " or ' (quote or apostrophe)

  ' (apostrophe)

  Date

  # (pound sign)

  ' (apostrophe)

  Wildcard

  *

  %

  Delimited Identifiers   [..]   [..] or “..”

 

Distinct and DistinctRow

Both Access and SQL Server have the DISTINCT predicate which follows the SELECT, but T-SQL does not have DISTINCTROW.

 

Joins

Both Access and T-SQL use INNER JOIN for inner joins. However, for outer joins, Access uses LEFT JOIN and RIGHT JOIN, while T-SQL uses LEFT OUTER JOIN and RIGHT OUTER JOIN. T-SQL also has a FULL JOIN, which Access lacks. (Note: In the comments, Michal points out that T-SQL can also use LEFT JOIN and RIGHT JOIN.  He’s quite correct.)

 

Built-in Functions

Type

Access

T-SQL

Conditional

IIF()

CASE, IF-THEN-ELSE

Conversion

Cdate(), Clng(), Cint(), etc

CAST()

Formatting

Format()

CONVERT()

Nulls

Nz()

ISNULL()

Aggregates

Min

Max

First

Last

Sum

Avg

Count

No equivalent

MIN

MAX

No equivalent

No equivalent

SUM

AVG

COUNT

COUNT DISTINCT

Current Date

Date(), Now()

GETDATE()

Domain Aggregate Functions

DMax(), DMin, DCount, DLookup, etc

No counterpart. Must use subqueries.

String

InStr()

Trim()

Mid()

CHARINDEX

RTRIM and LTRIM

SUBSTRING

String Concatenation

&, +

+

 

Parameter Queries

Parameter queries as known in Access SQL cannot be converted directly to a Pass-through query. The most common way to simulate parameters is to build the Pass-Through query in VBA code and then execute it.

 

Great Features of T-SQL you should know about

 

Comments

Access does not allow comments in queries, but T-SQL DOES! This is a great feature of SQL Server. There are two methods of commenting:

  • two dashes ( -- ) comments out a single line
  • blocks of text bracketed by /*…*/ comments out multiple lines

 

Variables

Variables are another thing which Access does not have that T-SQL does. Variables provide another way to parameterize a T-SQL query.

 

Count Distinct

As mentioned in the Functions list, T-SQL has a COUNT DISTINCT aggregate function, which Access lacks. You can read more about it here: COUNT DISTINCT in Access

 

Multiple SQL Statement and Temporary Tables

These two things go hand in hand. In Access, a "query" can only have a single SQL Statement. But in T-SQL, you can execute multiple SQL Statements. You can even use a Make-Table query (SELECT..INTO) to create a temporary table, which can be used in subsequent SQL Statements. You can use this feature in an Access Pass-Through query.

 

Conclusion

There are, of course, many more differences between Access SQL and T-SQL. What I've concentrated on here, are those "gotchas" that crop up when trying to convert an Access query to a Pass-through query in T-SQL syntax. I'll discuss the details of these differences in future posts.

3 comments:

MichaƂ Dziubek said...

IN T-SQL you can also use LEFT/RIGHT JOIN

Roger's Access Blog said...

Michal,

You can indeed use LEFT JOIN and RIGHT JOIN in T-SQL. Thanks for pointing that out. I've modified the post so I don't give misleading information.

This leads me to an odd realization. I was certain that T-SQL required the OUTER keyword, but after checking, I see I have pass-through queries which I converted from Access queries which do not have it, and I've never noticed.

Francis said...
This comment has been removed by a blog administrator.