T-SQL enhancements

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

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;

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 deprecated.)The following example uses the GROUPING SETS operator and the GROUPING_ID function:

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

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:

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.

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:

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.

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:

Select ProductID, OrderQty from SalesOrderDetail 

   with (FORCESEEK);