Last month I covered a few small T-SQL enhancements in SQL Server 2008, including the enhanced VALUES clause, support for ISO week as a date part, and enhanced binary-character conversions. This month I'll cover four additional enhancements: composable DML, the ability to declare and initialize variables in the same statement, compound assignment operators, and more reliable object dependency information.
Composable DML
SQL Server 2005 introduced support for the OUTPUT clause, allowing modification statements such as INSERT, UPDATE, and DELETE to return output with information from modified rows. The initial implementation of the OUTPUT clause doesn't let you filter output rows; namely, if the modification statement affects 50 rows, an OUTPUT clause will return 50 rows. The only flexibility is that you can select which attributes or expressions, based on existing attributes, you want to return from the modified rows. In addition, you can control the target of the OUTPUT clause (caller, table, or both). Because you can't filter output rows, if you want to store a subset of the output rows in a target table, you must apply a two-step process: You have to output the rows into a staging table, then insert into the final target table the filtered rows that you need from the staging table.
SQL Server 2008 enhances support for the OUTPUT clause with a new feature called composable DML. This feature lets you insert into a target table a subset of rows returned from a modification statement. The composable DML feature is implemented in the form of an INSERT SELECT statement, possibly with a WHERE clause. The FROM clause of the SELECT statement defines a derived table that is based on a modification statement with an OUTPUT clause. To demonstrate this feature, create the Orders and OrdersAudit tables by running the code in Listing 1.
Suppose that you need to purge orders placed prior to 2007, and you need to audit deleted rows only if they represent orders placed by customers A or D. Instead of implementing a two-step process, you can implement the task in one step using composable DML like so:
INSERT INTO dbo.OrdersAudit(orderid, orderdate, empid, custid, qty)
SELECT orderid, orderdate, empid, custid, qty
FROM (DELETE FROM dbo.Orders
OUTPUT deleted.*
WHERE orderdate
WHERE custid IN ('A', 'D');
Table 1 shows the contents of the OrdersAudit table after running this code.
Note that the current implementation of composable DML allows only an INSERT SELECT statement against the derived table with a WHERE clause. No other querying elements are allowed in the outer statement (e.g., joins, grouping). Also note that similar restrictions that apply to the OUTPUT clause also apply to composable DML. That is, the target table can be a permanent table, temporary table, or table variable. But the target can't be a table expression (e.g., a view), have triggers, participate in a primary key–foreign key relationship, or participate in merge replication or updatable subscriptions for transactional replication.
SQL Server 2008 supports the OUTPUT clause and composable DML with the new MERGE statement. For an example of using composable DML with the MERGE statement, run the code in Listing 2. This example demonstrates how you can audit output rows from a MERGE statement—but only those rows that were affected by an INSERT action; output rows that were affected by other actions won't be audited.
Declare and Initialize Variables
SQL Server 2008 introduces the ability to declare and initialize variables in the same statement. The initialization value can be any self-contained expression that returns a scalar value. For example, Listing 3 demonstrates declaring and initializing variables with a constant, a function, and a scalar subquery. Table 2 shows the content of the variables after the initialization.
Compound Assignment Operators
Like the previous enhancement I described, compound assignment operators is another small syntactical improvement. Prior to SQL Server 2008 if you wanted to assign a value to a column, variable, or parameter, and that value was the result of applying an operator (e.g., addition or subtraction) to that column and another operand, you had to refer to that column again in the expression. For example, if you wanted to add 10 to a column called col1 in an UPDATE statement, you had to specify the following assignment in the statement's SET clause: col1 = col1 + 10.
With compound assignment operators you don't have to repeat the target name; rather, you specify: col1 += 10. SQL Server 2008 supports compound assignment operators wherever assignment is supported, such as in an UPDATE statement, MERGE statement, SET statement, and assignment SELECT statement. Compound assignment operators include arithmetic operations (+=, -=, *=, /=, %=), bitwise (|=, &=, ^=), and even string concatenation (+=). The following code demonstrates using the various compound assignment operators in the context of an assignment SELECT statement:
DECLARE
@a AS INT = 10, @b AS INT = 10, @c AS INT = 10, @d AS INT = 10, @e AS INT = 10,
@f AS INT = 10, @g AS INT = 10, @h AS INT = 10,
@i AS VARCHAR(10) = 'abc';
SELECT
@a += 2, @b -= 2, @c /= 2, @d *= 2, @e %= 2, -- arithmetic
@f |= 2, @g &= 2, @h ^= 2, -- bitwise
@i += 'def'; -- concatenate
SELECT @a AS a, @b AS b, @c AS c, @d AS d, @e AS e, @f AS f, @g AS g, @h AS h, @i AS i;
Table 3 shows the values of the variables after the assignment.
SQL Dependencies
Prior to SQL Server 2008, object dependency information that SQL Server kept track of was unreliable. The main reason for this unreliability was the combination of support for deferred name resolution and the fact that dependency information was tracked based on object IDs. Deferred name resolution means that you can create objects that depend on other objects even if the other objects don't exist. For example, you can create a stored procedure that refers to a table that doesn't exist. Since a nonexistent object doesn't have an object ID yet, when you create an object that refers to a nonexistent object, SQL Server has nothing to record as dependency info. Even if you later create the referenced object, it's too late, because SQL Server records dependency information only when creating referencing objects if the referenced objects exist.
SQL Server 2008 records dependency information based on name references. So even if you create an object that refers to another object that doesn't yet exist, SQL Server can parse and record the name references. If an object doesn't exist when you query the dependency information, SQL Server can show you the dependencies by name, without the ID. If the object is created later, SQL Server can then return the ID as well.
Note that SQL Server 2008 doesn't provide dependency information for dynamic code or Common Language Runtime (CLR) code. It provides information for dependencies that appear in static code. You get information about schema-bound and non–schema-bound objects, cross-database, and even cross-server dependencies.
To demonstrate querying the new object dependency information, run the code in Listing 4 to create several objects in the tempdb database for test purposes. SQL Server 2008 introduces three objects that return dependency information: the sys.sql_expression_dependencies catalog view and the sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities dynamic management functions (DMFs).
The sys.sql_expression_dependencies view provides object dependencies by name. It replaces the older sys.sql_dependencies view. For example, the following query shows all dependencies in the current database:
SELECT
OBJECT_SCHEMA_NAME(referencing_id) AS srcschema,
OBJECT_NAME(referencing_id) AS srcname,
referencing_minor_id AS srcminorid,
referenced_id,
referenced_schema_name AS tgtschema,
referenced_entity_name AS tgtname,
referenced_minor_id AS tgtminorid
FROM sys.sql_expression_dependencies;
This query produces the output in Table 4. Notice that the dependencies in the dynamic SQL code weren't identified, whereas all dependencies that appear in the static code were identified. Also notice that the dependency of Proc1 on the nonexistent Proc3 was identified by name, but since Proc3 doesn't exist, the referenced_id attributes for Proc3 is NULL.
The sys.dm_sql_referenced_entities DMF provides all entities that the input entity references. Those are the entities that the input entity depends on. For example, the following code returns all entities that dbo.V1 depends on:
SELECT
referenced_schema_name AS objschema,
referenced_entity_name AS objname,
referenced_minor_name AS minorname,
referenced_class_desc AS class
FROM sys.dm_sql_referenced_entities('dbo.V1', 'OBJECT');
This code returns the output in Table 5. The output tells you that dbo.V1 depends on the table dbo.T1 and the column dbo.T1.col1.
The sys.dm_sql_referencing_entities DMF provides all entities that reference the input entity. Those are the entities that depend on the input entity. For example, the following code returns all entities that depend on dbo.T1:
SELECT
referencing_schema_name AS objschema,
referencing_entity_name AS objname,
referencing_class_desc AS class
FROM sys.dm_sql_referencing_entities('dbo.T1', 'OBJECT');
This code returns the output in Table 6. The output shows you that dbo.Proc2 and dbo.V1 depend on dbo.T1.
Little Changes Can Bring Big Improvements
SQL Server 2008 includes numerous small enhancements. Some changes, such as compound assignment operators and the ability to declare and initialize variables in the same statement, are just syntactical improvements that let you shorten your code a bit. Other changes, such as composable DML and the new object dependency information, are more significant, providing improved performance and reliability.
Listing 1: Code to Create Orders and OrdersAudit Tables
SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
CREATE TABLE dbo.Orders
(
orderid INT NOT NULL,
orderdate DATE NOT NULL,
empid INT NOT NULL,
custid VARCHAR(5) NOT NULL,
qty INT NOT NULL,
CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);
GO
INSERT INTO dbo.Orders
(orderid, orderdate, empid, custid, qty)
VALUES
(30001, '20060802', 3, 'A', 10),
(10001, '20061224', 1, 'A', 12),
(10005, '20061224', 1, 'B', 20),
(40001, '20070109', 4, 'A', 40),
(10006, '20070118', 1, 'C', 14),
(20001, '20070212', 2, 'B', 12),
(40005, '20080212', 4, 'A', 10),
(20002, '20080216', 2, 'C', 20),
(30003, '20080418', 3, 'B', 15),
(30004, '20060418', 3, 'C', 22),
(30007, '20060907', 3, 'D', 30);
IF OBJECT_ID('dbo.OrdersAudit', 'U') IS NOT NULL
DROP TABLE dbo.OrdersAudit;
CREATE TABLE dbo.OrdersAudit
(
auditlsn INT NOT NULL IDENTITY,
loginname SYSNAME NOT NULL DEFAULT (SUSER_SNAME()),
posttime DATETIME NOT NULL DEFAULT (CURRENT_TIMESTAMP),
orderid INT NOT NULL,
orderdate DATE NOT NULL,
empid INT NOT NULL,
custid VARCHAR(5) NOT NULL,
qty INT NOT NULL,
CONSTRAINT PK_OrdersAudit PRIMARY KEY(auditlsn)
);
Listing 2: Code to Illustrate Composable DML with MERGE
INSERT INTO dbo.CustomersAudit(custid, companyname, phone, address)
SELECT custid, companyname, phone, address
FROM (MERGE INTO dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
ON TGT.custid = SRC.custid
WHEN MATCHED THEN
UPDATE SET
TGT.companyname = SRC.companyname,
TGT.phone = SRC.phone,
TGT.address = SRC.address
WHEN NOT MATCHED THEN
INSERT (custid, companyname, phone, address)
VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address)
OUTPUT $action AS action,
inserted.custid,
inserted.companyname,
inserted.phone,
inserted.address) AS D
WHERE action = 'INSERT';
Listing 3: Code to Declare and Initialize Variables
DECLARE
@v1 AS INT = 10,
@v2 AS DATE = SYSDATETIME(),
@v3 AS NVARCHAR(2000) = (STUFF(
(SELECT N',' + QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = N'dbo'
AND TABLE_NAME = N'Orders'
ORDER BY ORDINAL_POSITION
FOR XML PATH(')), 1, 1, N'));
SELECT @v1 AS v1, @v2 AS v2, @v3 AS v3;
Listing 4: Code to Create Objects for SQL Dependencies Example
USE tempdb;
IF OBJECT_ID('dbo.Proc1', 'P') IS NOT NULL DROP PROC dbo.Proc1;
IF OBJECT_ID('dbo.Proc2', 'P') IS NOT NULL DROP PROC dbo.Proc1;
IF OBJECT_ID('dbo.V1', 'V') IS NOT NULL DROP VIEW dbo.V1;
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
GO
CREATE PROC dbo.Proc1
AS
EXEC('EXEC dbo.Proc2');
EXEC dbo.Proc3;
GO
CREATE PROC dbo.Proc2
AS
SELECT * FROM dbo.T1;
GO
CREATE TABLE dbo.T1(col1 INT);
GO
CREATE VIEW dbo.V1
AS
SELECT col1 FROM dbo.T1;
GO