In a similar manner, you can create and use the dbo.fn_perrow_getdate() function and a view to return the result of the nondeterministic GETDATE() function:
CREATE VIEW VGetDate AS SELECT GETDATE() AS dt
GO
CREATE FUNCTION dbo.fn_perrow_getdate() RETURNS
datetime
AS BEGIN RETURN (SELECT dt FROM
VGetDate) END
GO
You could then, for example, invoke the function from your INSERT SELECT statements.
Unfortunately, Microsoft closed these back doors in the latest beta builds of SQL Server 2005, and chances are that it will block them in a future service pack of SQL Server 2000. Meanwhile, you can create functions of your own to handle row atomicity.
Inline UDFs
The second UDF back door I want to discuss this month is fully supported, but I consider it a back door because few people know about it. SQL Server supports two types of table-valued UDFs (UDFs that return a table): multistatement and inline. The former UDF has a body containing T-SQL statements whose purpose is to populate the returned table. The latter is called inline because it contains one SELECT statement that returns a table, and you embed that statement in the query that invokes the function. SQL Server supports modifying data through inline table-valued functions, much like you can with views. I like to think of inline UDFs as parameterized views because they're similar in how you write them and in how the optimizer treats them. The only difference I see is that inline UDFs let you use input parameters and views don't.
To see how to modify data through inline functions, run the code that Listing 1 shows to create the fn_CustOrders() UDF in the Northwind database. This UDF accepts a customer ID as an argument and returns a table containing the orders of that customer. To test the function, run the following query to return the orders of customer ALFKI:
SELECT * FROM fn_CustOrders(N'ALFKI')
You can now insert, update, and delete values from the Orders table through the fn_CustOrders() UDF. And, as with views, you can grant users permission to modify data only through the UDF without giving them direct access to the Orders table. In this way, you allow modifications to orders of only one customer at a time. For example, run the following code, which returns ALFKI's orders, adds 1 day to the order date, and returns customer ALFKI's orders after the modification, then rolls back the transaction:
BEGIN TRAN
SELECT * FROM
fn_CustOrders(N'ALFKI')
UPDATE fn_CustOrders(N'ALFKI') SET OrderDate = OrderDate + 1
SELECT * FROM
fn_CustOrders(N'ALFKI')
ROLLBACK TRAN
Figure 1 shows the results of running this code. Callout A shows ALFKI's orders before the modification, and callout B shows them after the modification. Notice that the order dates in callout B are exactly 1 day later than the ones in callout A. Similarly, when your business rules demand, you can restrict users to inserting and deleting the orders of only one customer at a time, only through the function, and only if the users have the correct permissions on the function.
To Use or Not to Use?
As you've seen, SQL Server invokes nondeterministic functions such as RAND() and GETDATE() only once per query, which might limit you in providing solutions to some problems. Also, you're not allowed to invoke nondeterministic functions within your UDFs. You can use UDF back doors to get around this limitation, at least for now. However, I strongly recommend not using these back doors; in the latest SQL Server 2005 beta builds, the doors were closed, so they might also be closed in a future SQL Server 2000 service pack. But to limit the rows that a modifying statement can affect, you can modify data through inline UDFs—a fully supported feature that most people overlook.
So why bother knowing about back doors that are going to be closed? For one thing, you might need to maintain code that other people wrote. Some programmers use the back doors because they need the functionality. I hope that Microsoft's SQL Server developers will soon realize how important it is to introduce those features as supported functionality. Next month, I'll discuss back doors to views.