246 T-SQL ENHANCEMENTS Query Result SELECT id, parent

246 T-SQL ENHANCEMENTS Query Result SELECT id, parent FROM RollupAccount WHERE id = 1000 1000 0 Anchor SELECT id, parent FROM DetailAccount . . . RollupAccount 2001 2002 10001000Recursive Call 1 Children of 1000 2003 1000 SELECT id, parent FROM DetailAccount . . . RollupAccount 3007 2003 Recursive Call 23008 2003 Children of 2001,3004 2002 2002, and 2003 3005 2002 3006 2002 3001 2001 3002 2001 SELECT id, parent FROM DetailAccount . . . RollupAccount 4001 4002 30023002 Recursive Call 3 Children of previous Recursive Call 4 No results, recursion stops SELECT id, parent FROM DetailAccount . . . RollupAccount Figure 7-6: Recursive Query The previous batch creates a CTE named Rollup. There are three parts to a CTE when it is used to do recursion. The anchor, which initializes the recursion, is first. It sets the initial values of Rollup. In this case, Rollupis initialized to a table that has a single row representing the rollup account with id=1000. The anchor may not make reference to the CTE Rollup. The recursive call follows a UNION ALL keyword. UNION ALL must be used in this case. It makes reference to the CTE Rollup. The recursive call will be executed repeatedly until it produces no results. Each time it is called, Rollupwill be the results of the previous call. Figure 7-6 shows the results of the anchor and each recursive call. First the anchor is run, and it produces a result set that includes only the account 1000. Next the recursive call is run and produces a resultset that consists of all the accounts that have as a parent account 1000. The recursive call runs repeatedly, each time joined with its own previous result to produce the children of the accounts selected in the previous recursion. Also note that the recursive call itself is a UNION ALL because the accounts are spread out between the DetailAccounttable and the RollupAccounttable. After the body of the CTE, the SELECT statement just selects all the results in Rollup that is, the UNION of all the results produced by calls in the CTE body. Now that we can produce a list of all the accounts by walking through the hierarchy from top to bottom, we can use what we learned to calculate the value of each account. To calculate the values of the accounts, we must work from the bottom up that is from the detail accounts up to the rollup account 1000. This

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

T-SQL LANGUAGE ENHANCEMENTS SELECT id, balance FROM Rollup

T-SQL LANGUAGE ENHANCEMENTS SELECT id, balance FROM Rollup WHERE id = 2001 id balance – 2001 36 (1 row(s) affected) This query shows a view name, Rollup, that we can query to find the values of all the accounts in the chart of accounts or an individual account. Let s look at how we can do this. To start with, we will make a recursive query that just lists all the account numbers, starting with the top rollup account, 1000. The query that follows does this. WITH Rollup(id, parent) AS ( anchor SELECT id, parent FROM RollupAccount WHERE id = 1000 UNION ALL recursive call SELECT R1.id, R1.parent FROM ( SELECT id, parent FROM DetailAccount UNION ALL SELECT id, parent FROM RollupAccount ) R1 JOIN Rollup R2 ON R2.id = r1.parent ) selecting results SELECT id, parent FROM Rollup GO id parent – 1000 0 2001 1000 2002 1000 2003 1000 3007 2003 3008 2003 3004 2002 3005 2002 3006 2002 3001 2001 3002 2001 4001 3002 4002 3002 (13 row(s) affected)

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

T-SQL ENHANCEMENTS but no balance associated with it.

T-SQL ENHANCEMENTS but no balance associated with it. The SQL batch that follows builds and populates these two tables for the accounts shown in Figure 7-5. CREATE TABLE DetailAccount(id INT PRIMARY KEY, parent INT, balance FLOAT) CREATE TABLE RollupAccount(id INT PRIMARY KEY, parent INT) INSERT INTO DetailAccount VALUES (3001, 2001, 10) INSERT INTO DetailAccount VALUES(4001, 3002, 12) INSERT INTO DetailAccount VALUES(4002, 3002, 14) INSERT INTO DetailAccount VALUES(3004, 2002, 17) INSERT INTO DetailAccount VALUES(3005, 2002, 10) INSERT INTO DetailAccount VALUES(3006, 2002, 25) INSERT INTO DetailAccount VALUES(3007, 2003, 7) INSERT INTO DetailAccount VALUES(3008, 2003, 9) INSERT INTO RollupAccount VALUES(3002, 2001) INSERT INTO RollupAccount VALUES(2001, 1000) INSERT INTO RollupAccount VALUES(2002, 1000) INSERT INTO RollupAccount VALUES(2003, 1000) INSERT INTO RollupAccount VALUES(1000, 0) Note that this example does not include any referential integrity constraints or other information to make it easier to follow. A typical thing to do with a chart of accounts it to calculate the value of all the rollup accounts or, in some cases, the value of a particular rollup account. In Figure 7-5 (shown earlier) the value of the rollup accounts is shown in gray, next to the account itself. We would like to be able to write a SQL batch like the one that follows. SELECT id, balance FROM Rollup a handy view id balance – 1000 104 2001 36 2002 52 2003 16 3001 10 3002 26 3004 17 3005 10 3006 25 3007 7 3008 9 4001 12 4002 14 (13 row(s) affected)

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

T-SQL LANGUAGE ENHANCEMENTS Although your opinion may differ,

T-SQL LANGUAGE ENHANCEMENTS Although your opinion may differ, the CTE syntax is a bit clear and more encapsulated; that is, there is only one place that defines what orders are missing part number 90. Also, in theory, the CTE is giving the optimizer a bit more information in that it is telling the optimizer it plans on using Missingmore than once. The CTE is also part of another feature of SQL Server 2005 that is also part of the SQL:1999 standard. It is called a recursive query. This is especially useful for a chart of accounts in an accounting system or a parts explosion in a bill of materials. Both of these involve tree-structured data. In general, a recursive query is useful anytime tree-structured data is involved. We will look at an example of a chart of accounts to see how recursive queries work. Figure 7-5 shows a simple chart of accounts containing two kinds of accounts: detail accounts and rollup accounts. Detail accounts have an actual balance associated with them; when a posting is made to an accounting system, it is posted to detail accounts. In Figure 7-5, account 4001 is a detail account that has a balance of $12. Rollup accounts are used to summarize the totals of other accounts, which may be detail accounts or other rollup accounts. Every account, except for the root account, has a parent. The total of a rollup account is the sum of the accounts that are its children. In Figure 7-5 account 3002 is a rollup account, and it represents the sum of its two children, accounts 4001 and 4002. In practice, one of the ways to represent a chart of accounts is to have two tables: one for detail accounts and the other for rollup accounts. A detail account has an account number, a parent account number, and a balance for columns. A rollup account has an account number and a parent 1000 2001 2002 2003 3001 3002 3004 3005 3006 3007 3008 4001 4002 $10 $12 $14 $17 $10 $25 $7 $9$26 $36 $52 $16 $104 Detail Accounts Rollup Accounts Figure 7-5: A Chart of Accounts

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

T-SQL ENHANCEMENTS SELECT MakeCall AS Action, S.SalesPersonID, S.SalesQuota,

T-SQL ENHANCEMENTS SELECT MakeCall AS Action, S.SalesPersonID, S.SalesQuota, (SELECT SUM(SD.UnitPrice * SD.OrderQty) FROM SalesOrderHeader SH JOIN SalesOrderDetail SD ON SH.SalesOrderId = SD.SalesOrderId AND SD.ProductID=90 AND SH.SalesPersonID=S.SalesPersonID ) FROM SalesPerson S WHERE EXISTS ( SELECT * FROM SalesOrderHeader SH JOIN SalesOrderDetail SD ON SH.SalesOrderID = SD.SalesOrderID AND SD.ProductID = 90 AND SH.SalesPersonID = S.SalesPersonID ) UNION SELECT Relax AS Action, S.SalesPersonID, S.SalesQuota, 0 FROM SalesPerson S WHERE NOT EXISTS ( SELECT * FROM SalesOrderHeader SH JOIN SalesOrderDetail SD ON SH.SalesOrderID = SD.SalesOrderID AND SD.ProductID = 90 AND SH.SalesPersonID = S.SalesPersonID ) Notice that the subquery is reused in a number of places once in the calculation of the value of the sales involved in the missing part and then again, twice more, in finding the salespeople involved in sales with and without the missing part. Now let s produce the same report using a CTE. WITH Missing(SP, AMT) AS( SELECT SH.SalesPersonID, SUM(SD.UnitPrice * SD.OrderQty) FROM SalesOrderHeader SH JOIN SalesOrderDetail SD ON SH.SalesOrderId = SD.SalesOrderId AND SD.ProductID=90 GROUP BY SH.SalesPersonID ) SELECT MakeCall AS Action, S.SalesPersonID, S.SalesQuota, Missing.AMT FROM Missing JOIN SalesPerson S ON Missing.SP = S.SalesPersonID UNION SELECT Relax AS Action, S.SalesPersonID, S.SalesQuota, 0 FROM SalesPerson S WHERE S.SalesPersonID NOT IN (SELECT SP FROM Missing) The MissingCTE is a table that has a row for each salesperson who has an order that depends on the missing part, and the value of what is missing. Notice that the Missing table is used in one part of the query to find the value of the missing parts and in another to determine whether a sales person should MakeCall or Relax .

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

T-SQL LANGUAGE ENHANCEMENTS a SalesDetail for each item

T-SQL LANGUAGE ENHANCEMENTS a SalesDetail for each item that that was sold in that sale. Each Sales Header lists the ID of the salesperson who made the sale. Each Sales Detail entry lists a part number, its unit price, and the quantity of the part sold. The stock room has just called the Big Boss and told him that they are out of part number 90. The Big Boss calls you and wants you to make a report that lists the ID of each salesperson. Along with the ID, the Big Boss wants the text MakeCall listed if a salesperson made a sale that depends on part number 90 to be complete. Otherwise, he wants the text Relax printed. Just to ensure that the report lights a fire under the salespeople, the Big Boss also wants each line to list the value of the sale and the salesperson s sales quota. Before we actually make use of the CTE, let s first write a query that finds all the IDs of salespeople who have sales that depend on part number 90. SELECT DISTINCT SH.SalesPersonId FROM SalesOrderHeader SH JOIN SalesOrderDetail SD ON SH.SalesOrderId = SD.SalesOrderId AND SD.ProductID = 90 SalesPersonId GO SalesPersonId 14 21 22 more rows (14 row(s) affected) But the Big Boss has asked for a report with lines that look like this. Action SalesPersonID SalesQuota Value – MakeCall 22 250000.0000 2332.7784 … more lines Relax 35 250000.0000 0 Each line number has the ID of a salesperson. If that salesperson has an order that depends on part number 90, the first column says MakeCall and the last column has the value involved in the order. Otherwise, the first column says Relax and the last column has 0 in it. Without CTEs, we could use a subquery to find the salespeople with orders that depend on the missing part to make the report the Big Boss wants, as in the SQL batch that follows.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

T-SQL ENHANCEMENTS that references the MathConsttable. Note that

T-SQL ENHANCEMENTS that references the MathConsttable. Note that the syntax of the WITHclause is very similar to that of a VIEW. One way to think of a CTE is as a VIEWthat lasts only for the life of the query expression at the end of the CTE. In the example, MathConstacts like a VIEWthat is referenced in the query expression at the end of the CTE. It is possible to define multiple tables in a CTE. A SQL batch follows that shows another trivial usage of a CTE that defines two tables, again shown just to make the syntax clear. WITH MathConst(PI, Avogadro) AS (SELECT 3.14159, 6.022e23), second table Package(Length, Width) AS (SELECT 2, 5) SELECT * FROM MathConst, Package PI Avogadro Length Width – 3.14159 6.022E+23 2 5 (1 row(s) affected) In this example, the CTE produced two tables, and the query expression merely joined them. Both of the previous examples could have been done without using CTEs and, in fact, would have been easier to do without them. So what good are they? In once sense, a CTE is just an alternate syntax for creating a VIEW that exists for one SQL expression, or it can be thought of as a more convenient way to use a derived table that is, a subquery. However, CTEs are part of the SQL-92 standard, so adding them to SQL Server increases its standards compliance. In addition, CTEs are implemented in other databases, so ports from those databases may be easier with the addition of CTEs. In some cases, CTEs can save a significant amount of typing and may provide extra information that can be used when the query plan is optimized. Let s look at an example where this is the case. For this example, we will use three tables from the AdventureWorks database, a sample database that is distributed with SQL Server. We will use the SalesPerson, SalesHeader, and SalesDetail tables. The Sales Person table lists each salesperson that works for AdventureWorks. For each sale made at AdventureWorks, a SalesHeader is entered along with

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

T-SQL LANGUAGE ENHANCEMENTS update records, this populates both

T-SQL LANGUAGE ENHANCEMENTS update records, this populates both the inserted and deleted tables DECLARE @changes TABLE (id INT, oldValue VARCHAR(15), newValue VARCHAR(15)) UPDATE outputtbl SET col1 = updated OUTPUT inserted.id, deleted.col1, inserted.col1 INTO @changes WHERE id < 5 SELECT * FROM @changes GO id oldValue newValue - - 3 row5 updated 4 row6 updated (2 row(s) affected) Common Table Expressions and Recursive Queries A Common Table Expression, or CTE, is an expression that produces a table that is referred to by name within the context of a single query. The general syntax for a CTE follows. [WITH [,...n] ] ::= expression_name [(column_name [,...n])] AS () The following SQL batch shows a trivial usage of a CTE just to give you a feeling for its syntax. WITH MathConst(PI, Avogadro) AS (SELECT 3.14159, 6.022e23) SELECT * FROM MathConst GO PI Avogadro 3.14159 6.022E+23 (1 row(s) affected) The WITHclause, in effect, defines a table and its columns. This example says that a table named MathConst has two columns named PI and Avogadro. This is followed by a SELECTstatement enclosed in parentheses after an ASkeyword. And finally, all this is followed by a SELECTstatement

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

T-SQL ENHANCEMENTS the changes. In SQL Server 2005

T-SQL ENHANCEMENTS the changes. In SQL Server 2005 the INSERT, UPDATE, and DELETE statements have been enhanced to support an OUTPUT clause so that a single round trip is all that is required to modify the database and determine what changed. You use the OUTPUT clause together with the inserted and deleted virtual tables, much as in a trigger. The OUTPUT clause must be used with an INTO expression to fill a table. Typically, this will be a table variable. The following example creates a table, inserts some data, and finally deletes some records. create table and insert data CREATE TABLE outputtbl (id INT IDENTITY, col1 VARCHAR(15)) go INSERT INTO outputtbl VALUES( row1 ) INSERT INTO outputtbl VALUES ( row2 ) INSERT INTO outputtbl VALUES ( row5 ) INSERT INTO outputtbl VALUES ( row6 ) INSERT INTO outputtbl VALUES ( row7 ) INSERT INTO outputtbl VALUES ( row8 ) INSERT INTO outputtbl VALUES ( row9 ) INSERT INTO outputtbl VALUES ( row10 ) make a table variable to hold the results of the OUTPUT clause DECLARE @del AS TABLE (deletedId INT, deletedValue VARCHAR(15)) delete two rows and return through the output clause DELETE outputtbl OUTPUT DELETED.id, DELETED.col1 INTO @del WHERE id < 3 SELECT * FROM @del GO deletedId deletedValue - 1 row1 2 row2 (2 row(s) affected) The previous example inserted the idand col1values of the rows that were deleted into the table variable @del. When used with an UPDATEcommand, OUTPUTproduces both a DELETED and an INSERTED table. The DELETED table contains the values before the UPDATE command, and the DELETED table has the values after the UPDATE command. An example follows that shows OUTPUT being used to capture the result of an UPDATE. Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services

T-SQL LANGUAGE ENHANCEMENTS INSERT INTO toptest2 VALUES( Niels1 ) INSERT

T-SQL LANGUAGE ENHANCEMENTS INSERT INTO toptest2 VALUES( Niels1 ) INSERT INTO toptest2 VALUES( Niels2 ) INSERT INTO toptest2 VALUES( Niels3 ) INSERT INTO toptest2 VALUES( Niels4 ) INSERT INTO toptest2 VALUES( Niels5 ) Niels1 and Niels2 are inserted INSERT top(2) toptest SELECT * FROM toptest2 SELECT * FROM toptest An additional difference between the TOP clause in previous versions of SQL Server and in SQL Server 2005 is that we now can use expressions for number definition. The following code shows a couple of examples of that (it uses the tables from the preceding example). declare 3 variables DECLARE @a INT DECLARE @b INT DECLARE @c INT set values SET @a = 10 SET @b = 5 SELECT @c = @a/@b use the calculated expression SELECT TOP(@c)* FROM toptest insert some more data in toptest INSERT INTO toptest VALUES( Niels6 ) INSERT INTO toptest VALUES( Niels7 ) INSERT INTO toptest VALUES( Niels8 ) use a SELECT statement as expression this should return 5 rows SELECT TOP(SELECT COUNT(*) FROM toptest2) * FROM toptest The next T-SQL enhancement we ll look at is something completely new in SQL Server: the OUTPUTclause. OUTPUT The execution of a DML statement such as INSERT, UPDATE, or DELETEdoes not produce any results that indicate what was changed. Prior to SQL Server 2005, an extra round trip to the database was required to determine

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services