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 |Need a Union query to accomplish a full outer join |

|view | |

|Wildcards: |Wildcards: |

|_ for one character |? for one character |

|% for any number of characters |* for any number of characters |

|Getdate() |Date() |

| |Now() |

|Two types of division: |Three types of division |

|/ – regular division if operands are floats |/ – regular division |

|/ – interger division if operands are integers |\ – integer division - it truncates result to integer |

|% – returns the remainder |It can operate on floating point numbers: the numbers are rounded first, then integer |

| |division is performed. |

| |mod – returns the remainder |

|ISNULL (value1, value2) |NZ (value1, value2) |

|Returns value1 if value1 is not null, value2 if value1 is null. |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: |Aggregate functions include those for T-SQL and in addition, these: |

|SUM |STDEV |

|COUNT |VAR |

|MAX |FIRST |

|MIN |LAST |

|AVG | |

|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 |IIF (Boolean_expression, truevalue, falsevalue) |

|    WHEN Boolean_expression THEN result_expression | |

|        [ ...n ] | |

|    [ ELSE else_result_expression  ] | |

|END | |

| |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 |

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download