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