Object Oriented SQL Programming with SQL Server 2005

In this post, I have attempted a crude implementation of Object Oriented SQL Programming using the APPLY operator in SQL Server 2005. I feel that giving some thought in these lines, we can bring in more flexibility, abstraction and reusability to the way we query the database and, may be, create a new style for data access.

There is only one rule that I am going to follow here. All table access will be done using an INLINE TABLE VALUED function. No query will directly access the table. I would like to illustrate it with a small example.

I am going to use the AdventureWorks database and write queries to fetch sales order information. To follow the rules defined, I will be creating two functions to get the order header and detail information respectively.

CREATE FUNCTION GetOrderHeader(@OrderID int)
RETURNS TABLE
AS
RETURN(
         SELECT * FROM Sales.SalesOrderHeader 
         WHERE SalesOrderID = coalesce(@OrderID,SalesOrderID)
      )


CREATE FUNCTION GetOrderDetail(@OrderID int,@DetailID int)
RETURNS TABLE
AS
RETURN(
         SELECT * FROM Sales.SalesOrderDetail 
         WHERE SalesOrderID = coalesce(@OrderID,SalesOrderID)
           AND SalesOrderDetailID = coalesce(@DetailID,SalesOrderDetailID)
      )

With the above 2 functions in place, here are some of the queries I can perform to fetch Order Information

--Fetch all order header information
select * from GetOrderHeader(NULL) A 

--Fetch order header information for order ID 43659
select * from GetOrderHeader(43659) A 

--Fetch all order header/detail information
select * from GetOrderHeader(NULL) A 
CROSS APPLY GetOrderDetail(A.SalesOrderID,NULL) B

--Fetch order header/detail information for Order ID 43659
select * from GetOrderHeader(43659) A 
CROSS APPLY GetOrderDetail(A.SalesOrderID,NULL) B

--Fetch information for Order ID 43659 and Detail ID 1 
select * from GetOrderHeader(43659) A 
CROSS APPLY GetOrderDetail(A.SalesOrderID,1) B

Adding another function to fetch the product information

CREATE FUNCTION GetProductInfo(@ProductID int,@Name nvarchar(50))
RETURNS TABLE
AS
RETURN(
         SELECT * FROM Production.Product 
         WHERE ProductID = coalesce(@ProductID,ProductID)
           AND Name like '%' + isnull(@Name,'') + '%' 
)

I can now search for orders which has a particular product using this query

select * from GetOrderHeader(43659) A 
CROSS APPLY GetOrderDetail(A.SalesOrderID,NULL) B
CROSS APPLY GetProductInfo(B.ProductID,'Mountain') C

According to me, the above query is easier to read and much more maintainable than the one that we will usually write:

SELECT * FROM Sales.SalesOrderHeader A 
INNER JOIN Sales.SalesOrderDetail B 
        ON A.SalesOrderID = B.SalesOrderID
INNER JOIN Production.Product 
        ON B.ProductID = C.ProductID
WHERE SalesOrderID = 43659
  AND Name like '%Mountain%'

This type of querying through functions has its own advantages.

• For instance, there might be different types of data access that can happen in a particular table(filter, check for existence, etc). Each of this requirement can be a implemented as a seperate function and functions will be written around a particular object, like Orders.
• Sometimes, a table may be normalized for better data access. The function can join the normalized tables, code tables, if any. All these can be abstracted from the query
• Query will be focus on business implementation while the function will focus on data access.

Having said all these, SQL Server engine performs joins better than apply. So, none of the above suggestions can actually be implemented in a production system. This post is just an attempt to show that there is a scope for changing the way data access is done and if it is a better option or not is something that I leave as an open question. So, what do you think?