Advertisements

Comparing Access SQL with SQL Server TSQL

Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010.

If you migrate your Access data to SQL Server or you design an Access solution with SQL Server as the back-end database, it’s crucial that you understand the distinctions between Access SQL and SQL Server Transact SQL (TSQL). In this article, the significant differences you must know to ensure your solution works in your desired way will be examined.

For further information, read Access SQL: basic concepts, vocabulary, and syntax and Transact-SQL Reference.

Syntax and expression differences

There are a few syntax and expression variations that mandate conversion. The following table summarises the most familiar ones.

DifferenceAccess SQLSQL Server TSQL
Relational database attributeUsually called a field.Usually called a column.
String literalsQuote (“), such as “Mary Q. Contrary”Apostrophe (‘), such as ‘Mary Q. Contrary’.
Date literalsPound sign (#), such as #1/1/2019#Apostrophe (‘), such as ‘1/1/2019’
Multiple wildcard characterAsterisk (*), such as “Cath*”Percent (%), such as ‘Cath%’
Single wildcard characterQuestion Mark (?), such as “Cath?”Underscore (_), such as “Cath_”
Modulo operatorMOD operator, such as Value1 MOD Value2.Percent (%), such as Value1 % Value2
Boolean valuesWHERE Bitvalue = [True | False]OrWHERE Bitvalue = [-1 | 0]WHERE Bitvalue = [1 | 0]
Parameters[<A name that is not a defined column>]OrIn SQL view, use the SQL Parameters Declaration.@ParamName

Notes   

  • Access employs quote characters (“) around table names and objects. T-SQL can apply them for table names with spaces, but this contrasts with typical naming practice. In many cases, object names should be renamed excluding spaces, but queries have to also be updated to represent new table names. Utilise brackets [ ] for tables that are unable to be renamed but which are redefine from the status quo of naming standards. Access even appends extra parentheses around parameters in queries, but they can be erased in T-SQL.
  • Take into account using the canonical date format, yyyy-mm-dd hh:nn:ss, which is an ODBC standard for dates held as characters that supplies a standardised way to denote them throughout databases and maintains the date sort order.
  • To evade confusion when analysing Boolean values, you can apply 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 the opposite of an empty field that means “no value at all”. Instead, a null value is a placeholder that symbolises that data is missing or unknown. Database systems that recognise null values enforce “three-valued logic”, reflecting that something can be true, false, or unknown. If you don’t effectively handle null values, you can obtain inaccurate results during the process of 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 standard experience is that null values are allowed. To disable null values in a table column, do the following:

  • In Access, format a field’s Required property to Yes.
  • In SQL Server, insert 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 concludes:

  • 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

Deploy the null functions to preserve your expressions and capture 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 pioneering mechanisms:

  • In Access, there are no database options that relate to Null.
  • In SQL Server, you can apply the SET ANSI_NULLS OFF option for direct equality comparisons with NULL employing the = and <> operators. We advise 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

Anytime you are working with data or programming, there is a constant need to convert from one data type to another. The process of conversion can be easy or difficult. Common issues that you must consider are: implicit or explicit conversion, the actual date and time regional settings, rounding or truncation of numbers, and data type sizes. There’s no replacement for intricate testing and reaching of your results.

In Access, you utilise the Type Conversion Functions, of which there are eleven, each beginning with the letter C, one for every 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 mainly use the CAST and CONVERT TSQL functions, even though there are other Conversion Functions for dedicated 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 frequently used date functions are related (DateAdd, DateDiff, and DatePart) in Access and TSQL, but the application of the first argument varies.

  • In Access, the first argument is named the interval, and it’s a string expression that needs quotes.
  • In SQL Server, the first argument is known as the datepart, and it employs keyword values that don’t require quotes. ComponentAccessSQL Server Year”yyyy”year, yy, yyyyQuarter”q”quarter, qq, qMonth”m”month, mm, mDay of Year”y”dayofyear, dy, yDay”d”day, dd, dWeek”ww”wk, wwDay of Week”w”weekday, dwHour”h”hour, hhMinute”n”minute, mi, nSecond”s”second, ss, sMillisecondmillisecond, ms

Functions comparison

Access queries can include calculated columns that often utilise Access Functions to obtain results. Once you migrate queries to SQL Server, you must replace the Access function with an equivalent TSQL function if one is available. If there is no matching TSQL function, then you can usually create a computed column (The TSQL term used for a calculated column) to do as you intend. TSQL has a broad array of functions and it’s to your advantage to peruse what’s available. For more information, see What are the SQL database functions?.

The following table presents which Access function has a relevant TSQL function.

Access categoryAccess functionTSQL function
ConversionChr FunctionCHAR
ConversionDay FunctionDAY
ConversionFormatNumber FunctionFORMAT
ConversionFormatPercent FunctionFORMAT
ConversionStr FunctionSTR
ConversionType Conversion FunctionsCAST and CONVERT
Date/TimeDate functionCURRENT_TIMESTAMP
Date/TimeDay FunctionDATEFROMPARTS
Date/TimeDateAdd FunctionDATEADD
Date/TimeDateDiff FunctionDATEDIFFDATEDIFF_BIG
Date/TimeDatePart FunctionDATEPART
Date/TimeDateSerial FunctionDATEFROMPARTS
Date/TimeDateValue FunctionDATENAME
Date/TimeHour FunctionTIMEFROMPARTS
Date/TimeMinute FunctionTIMEFROMPARTS
Date/TimeMonth FunctionMONTH
Date/TimeNow FunctionSYSDATETIME
Date/TimeSecond FunctionTIMEFROMPARTS
Time FunctionTIMEFROMPARTS
Date/TimeTimeSerial FunctionTIMEFROMPARTS
Date/TimeWeekday FunctionDATEPARTDATENAME
Date/TimeYear FunctionYEARDATEFROMPARTS
Domain AggregateDFirst, DLast FunctionsFIRST_VALUELAST_VALUE
MathAbs FunctionABS
MathAtn FunctionATANATN2
MathCos FunctionCOSACOS
MathExp FunctionEXP
MathInt, Fix FunctionsFLOOR
MathLog FunctionLOGLOG10
MathRnd FunctionRAND
MathRound FunctionROUND
MathSgn FunctionSIGN
MathSin FunctionSIN
MathSqr FunctionSQRT
Program FlowChoose FunctionCHOOSE
Program FlowIIf FunctionIIF
StatisticalAvg FunctionAVG
SQL AggregateCount FunctionCOUNTCOUNT_BIG
SQL AggregateMin, Max FunctionsMINMAX
SQL AggregateStDev, StDevP FunctionsSTDEVSTDEVP
SQL AggregateSum FunctionSUM
SQL AggregateVar, VarP FunctionsVARVARP
TextFormat FunctionFORMAT
TextLCase FunctionLOWER
TextLeft FunctionLEFT
TextLen FunctionLEN
TextLTrim, RTrim, and Trim FunctionsTRIMLTRIMRTRIM
TextReplace FunctionREPLACE
TextRight FunctionRIGHT
TextStrReverse FunctionREVERSE
TextUCase FunctionUPPER
Advertisements

Leave a Reply

%d bloggers like this: