Problem
When developing code for an n-tier system, one common mistake client and logic tier developers can make is to write code that directly SELECTs, INSERTs, UPDATEs, and DELETEs data from core application tables defined in the database tier. Database access code written in the client and/or logic tiers of an n-tier system should never execute queries directly against the core tables of your data model.
Solution
By allowing direct access to core tables for SELECT, UPDATE, DELETE, or INSERT, the ability to change or evolve your data model is hindered as new features are incorporated into or removed from your business application. The end result is a tight coupling of the data model to logic tier and/or client tier code.
Consider the situation of a simple column add to a table. This action could now become a tedious and risky proposition if references to the table are scattered throughout your logic/client code. In addition, allowing direct access is also a security risk to your data. Creating an API into your data along with granting appropriate permissions eliminates this tight integration as well as minimizes security issues. The greatest benefit, however, is that an API affords you the freedom to modify the database as the business evolves.
SQL Server provides several methods for producing an API that consumers of your data can reference when the need arises to perform a database action. API access into the database should be developed with the understanding that any developed views, functions, or stored procedures that are utilized by data consumers will rarely have parameters change (if at all) and that the result set returned is always consistent regardless of what happens to the tables or code within the underlying API.
The chief SQL Server API tools at your disposal are Views, Stored Procedures, and User Defined Functions.
SQL Server Views
Views are simply pre-defined SELECT statements against tables in your data model. They are primarily used for simplification of data presentation and to secure your data by only exposing those columns the user needs to see. Views do not store any data (the exception is Indexed Views, which do) but are treated by SQL Server like actual tables.
The syntax to create a view is as follows
CREATE VIEW <view name>
AS
<pre-defined SELECT statement>
GO The following syntax creates a View called V_ORDERS which returns a listing of all orders and the customers who called in the order without also returning or allowing access to customer specific information that the end user does not need to know about:
CREATE VIEW DBO.V_ORDERS
AS
SELECT C.CUSTOMERID, C.CUSTOMERNAME, OH.ORDERID, OH.ORDERDATE, CSR.CSRID, CSR.CSRFIRSTNAME, CSR.CSRLASTNAME
FROM DBO.CUSTOMER C
JOIN DBO.ORDERHEADER OH ON OH.CUSTOMERID = C.CUSTOMERID
JOIN DBO.CSR ON CSR.CRSID = OH.CSRID
GOThis example uses the above View to retrieve all orders entered by CSR (customer service rep) Gates:
SELECT CUSTOMERNAME, ORDERID, ORDERDATE, CSRFIRSTNAME, CSRLASTNAME
FROM DBO.V_ORDERS
WHERE CSRLASTNAME = 'Gates'
ORDER BY CUSTOMERNAME, ORDERDATESQL Server Stored Procedures
Stored Procedures are programs that contain frequently executed business logic grouped together into a single point of reference. Stored Procedures accept parameters and they offer a powerful way to perform SELECTs, UPDATEs, DELETEs, and INSERTs against your database without allowing the caller to know what the database schema looks like.
Stored Procedures are defined as follows
CREATE PROCEDURE DBO.<procedure name> <parameters>
AS
<SQL DDL and DML statements>
GOThe following example creates a stored procedure version of the View example that lists the orders a CSR has entered
CREATE PROCEDURE DBO.GET_CSR_ORDERS @CSRLASTNAME VARCHAR(50)
AS
SELECT C.CUSTOMERID, C.CUSTOMERNAME, OH.ORDERID, OH.ORDERDATE, CSR.CSRID, CSR.CSRFIRSTNAME, CSR.CSRLASTNAME
FROM DBO.CUSTOMER C
JOIN DBO.ORDERHEADER OH ON OH.CUSTOMERID = C.CUSTOMERID
JOIN DBO.CSR ON CSR.CRSID = OH.CSRID
WHERE CSR.CSRLASTNAME = @CSRLASTNAME
ORDER BY CUSTOMERNAME, ORDERDATE
GOSQL Server User Defined Functions
User Defined Functions (UDFs) are a feature that allows database developers to create standalone snippets of code that can be used in standard Transact-SQL statements. They fall into one of three categories: Scalar Functions, Inline Functions, and Multi Statement Functions.
UDFs have a number of limitations. Among their limitations:
- You cannot create temp tables in functions (you can access table variables, however)
- You cannot execute stored procedures from a function
- You cannot execute dynamic SQL in a function
- You cannot update database tables within a function
- You cannot use non-deterministic functions such as GETDATE()
The function types are summarized below
- Scalar Functions accept zero or more parameters and return a single value.
- Inline Functions work similar to Views. They allow parameters and return row sets. They only allow for a single SELECT statement to be performed.
- Multi Statement Functions also work similar to Views but, unlike Inline Functions, they allow for complex Transact-SQL. They work like a parameterized View.
In my opinion, functions are not the best choice for API exposure to consumers of your database. Generally speaking, their implementation should be limited to specialized processing such as string or date manipulation that are commonly executed in your database code.
The choice of which API functionality to use is entirely up to you as it depends on the business problem to be solved. My preference is to create stored procedures for all data access and data manipulation and to use views for simple reporting since I can easily define the report sorting on a report by report basis via an ORDER BY clause. I only use functions to perform common non-database actions that manipulate data such as concatenating data, building strings, and date calculating.
Next Steps
- Examine your non-database tier code for any place where direct access to core tables is performed and consider replacing this logic with an API.
- Read more about Views and Stored Procedures
- Read more about the User Defined Function types
- Read more about Indexed Views
