Comparing Access SQL with SQL Server TSQL

Syntax and expression differences

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

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.