Abstract Data with SQL Server Views, Stored Procedures and Functions

By: Armando Prato | Read Comments (5) | Print Armando has over 24 years of industry experience and has been working with SQL Server since version 6.5. Related Tips: More

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.

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 GO

This 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, ORDERDATE

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> GO

The 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 GO

User Defined Functions (UDFs) are a feature added to SQL Server 2000 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 in the 2000 and 2005 Books Online
• Read more about the User Defined Function types in the 2000 and 2005 Books Online
• Read more about Indexed Views in the 2000 and 2005 Books Online

Related Tips: More | Become a paid author

Last Update: 8/22/2007

Share:

Share

Tweet

Free SQL Server Learning The SQL Server Security THREAT - It’s Closer Than You Think Tutorial - SQL Server Performance Tuning and Monitoring

Comments and Feedback:

Monday, January 31, 2011 - 8:47:22 AM - Richard Schaefer

Read The Tip

I disagree 100% with the direction of this article. The use of either third party code generation (i.e. CodeSmith) or VS 2010 with Entity Framework and LINQ to Entity enables simple refactoring of base database entities and regeneration of the Data Access Layer without writing hundreds of Stored Procedures. This results in an isolatable DAL that can be wired to a clean Business Layer. If you're using VS 2010 you can then engineer Layer Architecture drawings and ensure your developers aren't violating development protocols. While the code generated by LINQ isn't yet optimal, the developer and DBA time saved by using these technologies more than offsets the minor performance hit. The only time our organization would recommend hand-coding Stored Procedures, Views and UDFs for data access is in a situation where high-volume online performance criteria could not be met. We've yet to have one of those.

Monday, January 31, 2011 - 10:37:43 AM - Armando Prato

Read The Tip

Let me guess.... you're a application developer?   Application tools come and go (VB to VB.NET for instance) while relational databases have stayed relatively stable over the last 20 years or so.  The database is more than just a storage container.   Moreover, if you're happy with likely less than optimal code that is probably wasting system resources, then don't let me dissuade you.   Do what you think works for you.

Monday, January 31, 2011 - 11:15:27 AM - Richard Schaefer

Read The Tip

You guessed wrong. I'm an Enterprise Architect with responsibility for development environments as well as infrastructure standards. The choice to move to code generation/LINQ was discussed across all our architectural domains and approved. The benefits to using code generation and parameterized SQL over putting logic into the database are significant, no matter the database platform. While LINQ is a domain-specific tool there are numerous code generation utilities that operate in the other domains (i.e. Java) that work as well, if not better. The general movement is toward this type of development. DBAs need to embrace it, understand it and support it, not demand the creation of SPs, Views and UDFs that serve no purpose other than to ensure continued work for application DBAs. Newer database platforms provide less and less support for the old approach. TSQL and PL/SQL are dinosaurs that haven't adapted to the needs of modern systems. The database should be used for the storage of data with appropriate structure via constraints and indexes to ensure logical data integrity. Beyond that newer tools that provide a faster, simpler and more object-oriented approach that is in line with development methodologies are far superior for data retrieval and update in 99.9% of all cases.

Monday, January 31, 2011 - 12:07:01 PM - Armando Prato	Read The Tip
I have worked with LINQ and Hibernate only because I've had to dig into them to figure out why memory is getting bloated or disk reads are hammering the I/O subsystem.   So, as I stated, if it works for you don't let me dissuade you from using it.  However, they do not come without penalties.

Wednesday, February 02, 2011 - 4:07:35 PM - Armando Prato	Read The Tip
Richard, there's an upcoming MSSQLTips webcast that may be of interest to you regarding this.  It may be worthwhile to at least check it out. https://www2.gotomeeting.com/register/712705994

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Try the award winning SQL diagnostic manager as a free 14-day trial! It takes just 5 minutes to connect your SQL Databases to source control. Got 5 minutes? Get started now. Need SQL Server help and not sure where to turn? Reach out to the Edgewood consultants for a Health Check. Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise. Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. Free Learning - Introduction to SQL Azure Delivered by Herve Roggero on Wednesday, June 13 @ 3:00 PM EST