T-SQL enhancements
MERGE statement
The MERGE Transact-SQL statement performs INSERT, UPDATE, or
DELETE operations on a target table or view based on the results of a
join with a source table. You can use MERGE statement in the ISQL
painter and in PowerScript using dynamic SQL. For example
|
1 2 3 4 5 6 7 8 9 10 |
String mySQL mySQL = "MERGE INTO a USING b ON a.keycol = b.keycol " & + "WHEN MATCHED THEN "& + "UPDATE SET col1 = b.col1,col2 = b.col2 " & + "WHEN NOT MATCHED THEN " & + "INSERT (keycol, col1, col2, col3)" & + "VALUES (b.keycol, b.col1, b.col2, b.col3) " & + "WHEN SOURCE NOT MATCHED THEN " & + "DELETE;" EXECUTE IMMEDIATE :Mysql; |
Using the MERGE statement in ISQL
A MERGE statement must be terminated by a semicolon. By default
the ISQL painter uses a semicolon as a SQL terminating character, so
to use a MERGE statement in ISQL, the terminating character must be
changed to a colon (:), a forward slash (/), or some other special
character.
Grouping sets
GROUPING SETS is an extension of the GROUP BY clause that lets you
define multiple groupings in the same query. GROUPING SETS produce a
single result set, making aggregate querying and reporting easier and
faster. It is equivalent to a UNION ALL operation for differently
grouped rows.
The GROUPING SETS, ROLLUP, and CUBE operators are added to the
GROUP BY clause. A new function, GROUPING_ID, returns more
grouping-level information than the existing GROUPING function. (The
WITH ROLLUP, WITH CUBE, and ALL syntax is not ISO compliant and is
therefore obsolete.)
The following example uses the GROUPING SETS operator and the
GROUPING_ID function:
|
1 2 3 4 5 6 7 8 9 |
SELECT EmpId, Month, Yr, SUM(Sales) AS Sales FROM Sales GROUP BY GROUPING SETS((EmpId, ROLLUP(Yr, Month))); SELECT COL1, COL2, SUM(COL3) AS TOTAL_VAL, GROUPING(COL1) AS C1, GROUPING(COL2) AS C2, GROUPING_ID(COL1, COL2) AS GRP_ID_VALUE FROM TEST_TBL GROUP BY ROLLUP (COL1, COL2); |
You can use the GROUPING SETS operator in the ISQL painter, in
PowerScript (embedded SQL and dynamic SQL) and in DataWindow objects
(syntax mode).
Row constructors
Transact-SQL now allows multiple value inserts within a single
INSERT statement. You can use the enhanced INSERT statement in the ISQL
painter and in PowerScript (embedded SQL and dynamic SQL). For
example:
|
1 |
INSERT INTO Employees VALUES ('tom', 25, 5), ('jerry', 30, 6), ('bok', 25, 3); |
When including multiple values in a single INSERT statement with
host variables, you must set the DisableBind DBParm to 1. If you use
literal values as in the above example, you can insert multiple rows in
a single INSERT statement regardless of the binding setting.
Compatibility level
In SQL Server 2008, the ALTER DATABASE statement allows you to set
the database compatibility level (SQL Server version), replacing the
sp_dbcmptlevel procedure. You can use this syntax in the ISQL painter
and in PowerScript (dynamic SQL). For example:
|
1 2 3 4 5 |
ALTER DATABASE <database_name> SET COMPATIBILITY_LEVEL = {80 | 90 | 100} 80 = SQL Server 2000 90 = SQL Server 2005 100 = SQL Server 2008 |
Compatibility level affects behaviors for the specified database
only, not for the entire database server. It provides only partial
backward compatibility with earlier versions of SQL Server. You can use
the database compatibility level as an interim migration aid to work
around differences in the behaviors of different versions of the
database.
Table hints
The FORCESEEK table hint overrides the default behavior of the
query optimizer. It provides advanced performance tuning options,
instructing the query optimizer to use an index seek operation as the
only access path to the data in the table or view that is referenced by
the query. You can use the FORCESEEK table hint in the ISQL painter, in
PowerScript (embedded SQL and dynamic SQL), and in DataWindow objects
(syntax mode).
For example:
|
1 |
Select ProductID, OrderQty from SalesOrderDetail with (FORCESEEK); |