In a similar manner, you can split large updates into batches. For example, say you need to change in .NET

Creating QR Code in .NET In a similar manner, you can split large updates into batches. For example, say you need to change

the CustomerID OLDWO to ABCDE wherever it appears in the LargeOrders table. Here's the solution you would use in SQL Server 2000 when relying on the SET ROWCOUNT option: SET ROWCOUNT 5000; WHILE 1 = 1 BEGIN UPDATE dbo.LargeOrders SET CustomerID = N'ABCDE' WHERE CustomerID = N'OLDWO'; IF @@rowcount < 5000 BREAK; END SET ROWCOUNT 0;

And here's the solution in SQL Server 2005 using UPDATE TOP: WHILE 1 = 1 BEGIN UPDATE TOP(5000) dbo.LargeOrders SET CustomerID = N'ABCDE' WHERE CustomerID = N'OLDWO'; IF @@rowcount < 5000 BREAK; END

When you're done experimenting with the batch modifications, drop the LargeOrders table: IF OBJECT_ID('dbo.LargeOrders') IS NOT NULL DROP TABLE dbo.LargeOrders;

The new APPLY table operator applies the right-hand table expression to every row of the left-hand table expression. Unlike a join, where there's no importance to the order in which each of the table expressions is evaluated, APPLY must logically evaluate the left table expression first. This logical evaluation order of the inputs allows the right table expression to be correlated with the left onesomething that was not possible prior to SQL Server 2005. The concept can probably be made clearer with an example. Run the following code to create an inline table-valued function called fn_top_products: IF OBJECT_ID('dbo.fn_top_products') IS NOT NULL DROP FUNCTION dbo.fn_top_products; GO CREATE FUNCTION dbo.fn_top_products (@supid AS INT, @catid INT, @n AS INT) RETURNS TABLE AS RETURN SELECT TOP(@n) WITH TIES ProductID, ProductName, UnitPrice FROM dbo.Products WHERE SupplierID = @supid AND CategoryID = @catid ORDER BY UnitPrice DESC; GO

The function accepts three inputs: a supplier ID (@supid), a category ID (@catid), and a requested number of products (@n). The function returns the requested number of products of the given category, supplied by the given supplier, with the highest unit prices. The query uses the TOP option WITH TIES to ensure a deterministic result set by including all products that have the same unit price as the least expensive product returned. The following query uses the APPLY operator in conjunction with fn_top_products to return, for each supplier, the two most expensive beverages. The Category ID for beverages is 1, so 1 is supplied for the parameter @catid. This query generates the output shown in Table 7-6: SELECT S.SupplierID, CompanyName, ProductID, ProductName, UnitPrice FROM dbo.Suppliers AS S CROSS APPLY dbo.fn_top_products(S.SupplierID, 1, 2) AS P;

SupplierID CompanyName 18 18 Aux joyeux eccl siastiques Aux joyeux eccl siastiques ProductID ProductName 38 39 C te de Blaye Chartreuse verte UnitPrice 263.50 18.00

SupplierID CompanyName 16 16 16 1 1 23 20 7 12 10 Bigfoot Breweries Bigfoot Breweries Bigfoot Breweries Exotic Liquids Exotic Liquids Karkki Oy Leka Trading Pavlova, Ltd. Plutzer Lebensmittelgro m rkte AG Refrescos Americanas LTDA

ProductID ProductName 35 67 34 2 1 76 43 70 75 24 Steeleye Stout Laughing Lumberjack Lager Sasquatch Ale Chang Chai Lakkalik ri Ipoh Coffee Outback Lager Rh nbr u Klosterbier Guaran Fant stica

Specifying CROSS with the APPLY operator means that there will be nothing in the result set for a row in the left table expression for which the right table expression dbo.fn_top_products(S.SupplierID, 1, 2) is empty. Such is the case here, for example, for suppliers that don't supply beverages. To include results for those suppliers as well, use the OUTER keyword instead of CROSS, as the following query shows: SELECT S.SupplierID, CompanyName, ProductID, ProductName, UnitPrice FROM dbo.Suppliers AS S OUTER APPLY dbo.fn_top_products(S.SupplierID, 1, 2) AS P;

This query returns 33 rows. The result set with OUTER APPLY includes left rows for which the right table expression yielded an empty set, and for these rows the right table expression's attributes are NULL. There's a nice side-effect that resulted from the technology added to SQL Server's engine to support the APPLY operator. Now you are allowed to pass a column reference parameter from an outer query to a table-valued function. As an example of this capability, the following query returns, for each supplier, the lower of the two most expensive beverage prices (assuming there are at least two), generating the output shown in Table 7-7: SELECT S.SupplierID, CompanyName, (SELECT MIN(UnitPrice) FROM dbo.fn_top_products(S.SupplierID, 1, 2) AS P) AS Price FROM dbo.Suppliers AS S;