Comparing Access SQL with SQL Server TSQL

Comparing Access SQL with SQL Server TSQL

If you migrate your Access data to SQL Server or you create an Access solution with SQL Server as the back-end database, it’s vital that you know the differences between Access SQL and SQL Server Transact SQL (TSQL). What follows are the important variations you need to know so your solution works as intended.

For more information, see Access SQL: basic concepts, vocabulary, and syntax and Transact-SQL Reference.

Syntax and expression differences

There are a few syntax and expression differences that require conversion. The following table summarizes the most common ones.

Difference

Access SQL

SQL Server TSQL

Relational database attribute

Usually called a field

Usually called a column

String literals

Quote ("), such as "Mary Q. Contrary"

Apostrophe ('), such as 'Mary Q. Contrary'

Date literals

Pound sign (#), such as #1/1/2019#

Apostrophe ('), such as '1/1/2019'

Multiple wildcard character

Asterisk (*), such as "Cath*"

Percent (%), such as 'Cath%'

Single wildcard character

Question Mark (?), such as "Cath?"

Underscore (_), such as "Cath_"

Modulo operator

MOD operator, such as Value1 MOD Value2

Percent (%), such as Value1 % Value2

Boolean values

WHERE Bitvalue = [True | False]

Or

WHERE Bitvalue = [-1 | 0]

WHERE Bitvalue = [1 | 0]

Parameters

[<A name that is not a defined column>]

Or

In SQL view, use the SQL Parameters Declaration

@ParamName

Notes   

  • Access uses quote characters (") around table names and objects. T-SQL can use them for table names with spaces, but this is not standard naming practice. In most cases, object names should be renamed without spaces, but queries must also be rewritten to reflect new table names. Use brackets [ ] for tables that cannot be renamed but which do not conform to naming standards. Access also adds extra parentheses around parameters in queries, but they can be removed in T-SQL.

  • Consider using the canonical date format, yyyy-mm-dd hh:nn:ss, which is an ODBC standard for dates stored as characters that provides a consistent way to represent them across databases and preserves the date sort order.

  • To avoid confusion when comparing Boolean values, you can use the following comparison for Access and SQL Server:

    • Test for false value    WHERE Bitvalue = 0

    • Test for true value    WHERE Bitvalue <> 0

Null values

A null value is not an empty field that means “no value at all”. A null value is a placeholder that means that data is missing or unknown. Database systems that recognize null values implement “three-valued logic”, which means something can be true, false, or unknown. If you don’t properly handle null values, you can get incorrect results when making equality comparisons or evaluating WHERE clauses. Here is a comparison of how Access and SQL Server handle null values.

Disable null values in a table

in Access and SQL Server, the default experience is that null values are enabled. To disable null values in a table column, do the following:

  • In Access, set a field's Required property to Yes.

  • In SQL Server, add the NOT NULL attribute to a column in a CREATE TABLE statement.

Test for null values in a WHERE clause

Use the IS NULL and IS NOT NULL comparison predicates:

  • In Access, use IS NULL or IS NOT NULL. For example:

    SELECT … WHERE column IS NULL.
  • In SQL Server, use IS NULL or IS NOT NULL. For example:

    SELECT … WHERE field IS NULL

Convert null values with functions

Use the null functions to protect your expressions and return alternative values:

  • In Access, use the NZ (value, [valueifnull]) function which returns 0 or another value. For example:

    SELECT AVG (NZ (Weight, 50) ) FROM Product
  • In SQL Server, use the ISNULL(Value, replacement_value) function which returns 0 or another value. For example:

    SELECT AVG (ISNULL (Weight, 50)) FROM Product

Understand Database options

Some database systems have proprietary mechanisms:

  • In Access, there are no database options that pertain to Null.

  • In SQL Server you can use the SET ANSI_NULLS OFF option for direct equality comparisons with NULL using the = and <> operators. We recommend that you avoid using this option because it is deprecated, and it can confuse others who rely on ISO-compliant null-handling.

Conversion and casting

Whenever you are working with data or programming, there is a persistent need to convert from one data type to another. The process of conversion can be simple or complex. Common issues that you need to think about are: implicit or explicit conversion, the current date and time regional settings, rounding or truncation of numbers, and data type sizes. There’s no substitute for thorough testing and confirming of your results.

In Access, you use the Type Conversion Functions, of which there are eleven, each starting with the letter C, one for each data type. For example, to convert a floating point number to a string:

CStr(437.324) returns the string "437.324".

In SQL Server, you primarily use the CAST and CONVERT TSQL functions, although there are other Conversion Functions for specialized needs. For example, to convert a floating point number to a string:

CONVERT(TEXT, 437.324) returns the string "437.324"

DateAdd, DateDiff, and DatePart functions

These commonly used date functions are similar (DateAdd, DateDiff, and DatePart) in Access and TSQL, but the use of the first argument differs.

  • In Access, the first argument is called the interval, and it’s a string expression that requires quotes.

  • In SQL Server, the first argument is called the datepart, and it uses keyword values that don’t require quotes.

    Component

    Access

    SQL Server

    Year

    "yyyy"

    year, yy, yyyy

    Quarter

    "q"

    quarter, qq, q

    Month

    "m"

    month, mm, m

    Day of Year

    "y"

    dayofyear, dy, y

    Day

    "d"

    day, dd, d

    Week

    "ww"

    wk, ww

    Day of Week

    "w"

    weekday, dw

    Hour

    "h"

    hour, hh

    Minute

    "n"

    minute, mi, n

    Second

    "s"

    second, ss, s

    Millisecond

    millisecond, ms

Functions comparison

Access queries can contain calculated columns that sometimes use Access Functions to get results. When you migrate queries to SQL Server, you need to replace the Access function with an equivalent TSQL function if one is available. If there is no corresponding TSQL function, then you can usually create a computed column (The TSQL term used for a calculated column) to do what you want. TSQL has a wide array of functions and it’s to your benefit to see what’s available. For more information, see What are the SQL database functions?.

The following table shows which Access function has a corresponding TSQL function.

Access category

Access function

TSQL function

Conversion

Chr Function

CHAR

Conversion

Day Function

DAY

Conversion

FormatNumber Function

FORMAT

Conversion

FormatPercent Function

FORMAT

Conversion

Str Function

STR

Conversion

Type Conversion Functions

CAST and CONVERT

Date/Time

Date function

CURRENT_TIMESTAMP

Date/Time

Day Function

DATEFROMPARTS

Date/Time

DateAdd Function

DATEADD

Date/Time

DateDiff Function

DATEDIFF

DATEDIFF_BIG

Date/Time

DatePart Function

DATEPART

Date/Time

DateSerial Function

DATEFROMPARTS

Date/Time

DateValue Function

DATENAME

Date/Time

Hour Function

TIMEFROMPARTS

Date/Time

Minute Function

TIMEFROMPARTS

Date/Time

Month Function

MONTH

Date/Time

Now Function

SYSDATETIME

Date/Time

Second Function

TIMEFROMPARTS

Time Function

TIMEFROMPARTS

Date/Time

TimeSerial Function

TIMEFROMPARTS

Date/Time

Weekday Function

DATEPART

DATENAME

Date/Time

Year Function

YEAR

DATEFROMPARTS

Domain Aggregate

DFirst, DLast Functions

FIRST_VALUE

LAST_VALUE

Math

Abs Function

ABS

Math

Atn Function

ATAN

ATN2

Math

Cos Function

COS

ACOS

Math

Exp Function

EXP

Math

Int, Fix Functions

FLOOR

Math

Log Function

LOG

LOG10

Math

Rnd Function

RAND

Math

Round Function

ROUND

Math

Sgn Function

SIGN

Math

Sin Function

SIN

Math

Sqr Function

SQRT

Program Flow

Choose Function

CHOOSE

Program Flow

IIf Function

IIF

Statistical

Avg Function

AVG

SQL Aggregate

Count Function

COUNT

COUNT_BIG

SQL Aggregate

Min, Max Functions

MIN

MAX

SQL Aggregate

StDev, StDevP Functions

STDEV

STDEVP

SQL Aggregate

Sum Function

SUM

SQL Aggregate

Var, VarP Functions

VAR

VARP

Text

Format Function

FORMAT

Text

LCase Function

LOWER

Text

Left Function

LEFT

Text

Len Function

LEN

Text

LTrim, RTrim, and Trim Functions

TRIM

LTRIM

RTRIM

Text

Replace Function

REPLACE

Text

Right Function

RIGHT

Text

StrReverse Function

REVERSE

Text

UCase Function

UPPER

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×