Transact SQL (SQL Server)

Comparisons/Differences between T-SQL and J-SQL

Transact SQL (SQL Server) / Jet SQL (Access)
Can use FULL OUTER JOIN syntax to get a Left and Right Join in one view / Need a Union query to accomplish a full outer join
Wildcards:
_ for one character
% for any number of characters / Wildcards:
? for one character
* for any number of characters
Getdate() / Date()
Now()
Two types of division:
/ – regular division if operands are floats
/ – interger division if operands are integers
% – returns the remainder / Three types of division
/ – regular division
\ – integer division - it truncates result to integer
It can operate on floating point numbers: the numbers are rounded first, then integer division is performed.
mod – returns the remainder
ISNULL (value1, value2)
Returns value1 if value1 is not null, value2 if value1 is null. / NZ (value1, value2)
Returns value1 if value1 is not null, value2 if value1 is null.
No DISTINCTROW / DISTINCTROW
Date delimiters are single quotes / Date delimiters are #
Aggregate functions include:
SUM
COUNT
MAX
MIN
AVG / Aggregate functions include those for T-SQL and in addition, these:
STDEV
VAR
FIRST
LAST
To use ORDER BY you need a TOP 100%
TOP N and TOP N WITHTIES / TOP N means with ties. (No option for not returning ties.)
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[ ELSE else_result_expression ]
END / IIF (Boolean_expression, truevalue, falsevalue)
Has these Date functions:
CDate(integer expression)returns the date corresponding to an integer value.
CLng(date expression) returns the integer for a date
CDbl(date/time expression) returns the full decimal number corresponding to the date/time value.
To get a crosstab query, must use CASE expressions / Has CrossTab queries
Can use column aliases in WHERE and ORDER BY clauses / Cannot use column aliases in WHERE and ORDER BY clauses