Designed for Development
The disconnected DataSet object in ADO.NET helps you build scalable Web applications by keeping locking to a minimum and freeing you from having to maintain state. However, these same features open up application-design concerns. Most important, the DataSet object's in-memory XML data store, with its own set of programmed relationships and referential integrity, is a potential black hole. If you don't implement good database programming practices, your DataSet objects could be enormous. Even today, you can trace most performance problems to an application-coding problem—typically caused by either a misunderstanding of the database design or inefficient data-access methods. ADO.NET will likely exacerbate these kinds of problems because programmers—not database designers—will now be responsible for constructing constraints and relationships. In addition, because the DataSet object handles all the application data, you must take special care when programming against large databases. DataSet shifts much of the data management to the client, which might not be able to efficiently handle vast amounts of data.
How can you eliminate these potential loopholes in ADO.NET? First, become intimately involved in the application-design process. If you're a DBA, help the database developers understand and programmatically implement the underlying database design for the application. Become familiar with application-development languages and tools so that you can understand how different ADO.NET data-access mechanisms work against the database. And review the SQL code that you use to populate ADO.NET DataSet objects to verify that applications aren't retrieving or replicating extraneous data. ADO.NET is the new kid on the data-access block, and like all new technologies, it offers some great benefits and some potential liabilities. Tying up the potential loopholes in ADO.NET could make a world of difference in the scalability and performance of your database applications.
—Michael Otey
Editorial, "ADO.NET Loopholes"
October 2001, InstantDoc ID 22132
Retrieving Identity Values with ADO.NET
SQL Server's Identity columns provide a great way of letting SQL Server automatically assign unique values to rows that an application adds to a table. When adding a row to a table, the application doesn't need to know anything about the unique Identity number that SQL Server assigned. SQL Server handles that part automatically. However, in some cases, the application or the user needs to know the value assigned to the Identity column as soon as it's added to the database. For example, an order-entry application that uses an Identity column for the Order ID might need to provide a confirming order number to the client immediately. To make this information instantly available, you need a way to retrieve the value of the Identity column and display it in your application. You can set this up in ADO.NET, using an output parameter for a stored procedure that performs the insert action, as Listing 8 shows. This stored procedure inserts an employee record into the Northwind database's Employees table and returns the Identity field EmployeeID as an output parameter. The important point to notice about this stored procedure is the use of the SCOPE_IDENTITY() function that retrieves the Identity value that SQL Server assigned and copies that value into the @Identity variable that the stored procedure returned.
After creating the stored procedure, you can assign it to the InsertCommand property of a SqlDataAdapter. Then, anytime an application uses the SqlDataAdapter to insert a row, the stored procedure returns the value to the current row in the DataTable.
—Michael Otey, March 2004
Custom Aggregate Product
SQL Server 2000 doesn't support developing new aggregate functions; it lets you use only the built-in ones (e.g., SUM, MIN, MAX, AVG, COUNT). If you want to provide a custom aggregation such as the aggregate product of elements within a group, you need to use some tricky code to come up with a solution. To demonstrate one such unconventional technique, first run the code in Listing 9 to create and populate the table T1. You need to provide an aggregate product of the values within the value column for each group that appears in the groupid column. Figure 1 shows the desired result.
Often, you can get help in finding a T-SQL solution from the mathematics field. In this case, you're looking for a way to use the functions that T-SQL provides to produce the equivalent of a function that T-SQL doesn't provide. The following equation from the world of logarithms is the key to the solution:
LOGn(value1 * value2 * ... *
valueN) = LOGn(value1) +
LOGn(value2) + ... + LOGn(valueN)
The T-SQL expression SUM(LOG10(value)) is equivalent to the right side of the above equation. So, to calculate the product of the value elements, you need to raise 10 to the power of the right side of the equation: POWER(10., SUM(LOG10(value))).
At this point, you're close to solving the problem. The remaining obstacle is that LOG doesn't accept a zero or a negative value as an argument. To account for zeros, you can use a CASE expression that returns a zero if the group has at least one value of zero. To account for negative values, you can calculate the LOG function with the absolute value as input and multiply the result by -1 if an odd number of negative values exist. Listing 10 shows the complete solution. See T-SQL Black Belt, "Adding Performance," May 2001 (InstantDoc ID 20131) for more explanation of this technique.
—Itzik Ben-Gan, March 2004