Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Abstract Data with SQL Server Views, Stored Procedures and Functions


By:   |   Last Updated: 2007-08-22   |   Comments (6)   |   Related Tips: More > Views

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


Last Updated: 2007-08-22


get scripts

next tip button



About the author
MSSQLTips author Armando Prato Armando Prato has close to 30 years of industry experience and has been working with SQL Server since version 6.5.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, May 31, 2013 - 5:12:51 PM - Kris Hokanson Back To Top

Armando,

This is a very good, succinct and simple post which validated my thoughts on data abstraction.  IMO tools like LINQ and EF have their place but their effectiveness starts to dwindle when you get outside of ideal schema designs or development evironments.

The use of views to provide the flexibility to modify the underlying schema without greatly impacting existing applications should prove very useful in my current environment.

Thank you for an excellent article.


Wednesday, February 02, 2011 - 4:07:35 PM - Armando Prato Back To Top

 

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

 

 


Monday, January 31, 2011 - 12:07:01 PM - Armando Prato Back To Top

 

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.


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

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 - 10:37:43 AM - Armando Prato Back To Top

 

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 - 8:47:22 AM - Richard Schaefer Back To Top

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.


Learn more about SQL Server tools