1 / 11

SQL Server 2008 –Views, Functions and Stored Procedures

SQL Server 2008 –Views, Functions and Stored Procedures. Learningcomputer.com. Important Database objects. Tables (we already looked at these) Primary and Foreign keys Views Functions Stored Procedures Triggers. Views.

mircea
Download Presentation

SQL Server 2008 –Views, Functions and Stored Procedures

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server 2008 –Views, Functions and Stored Procedures Learningcomputer.com

  2. Important Database objects • Tables (we already looked at these) • Primary and Foreign keys • Views • Functions • Stored Procedures • Triggers

  3. Views • A view can be thought of as either a virtual table or a stored query • It contains rows and columns from one or more tables just like a regular SQL statement • The data is created on the fly when the view is run • It can be called from within a query, stored procedure or even another view

  4. Advantages of using a View • Views can simplify the complex queries • Views can be used as security mechanisms by granting permission on a view and not on the table to hide sensitive information • Another benefit is code reuse and code simplification using the complex t-sql • Present the users a table view of the data stored in base tables • Views can be indexed just like tables to increase performance • One disadvantage is that they do create a little overhead as you are adding another layer, another is not updateable

  5. View Demos • Using AdventureWorks2008 database which can be download here http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004 • Two methods • SSMS (SQL Server Management Studio) • We have created two additional fields on Employee table, SSN_user and Salary_user. The objective is to create a view on this table and show just these fields FirstName, LastName, JobTitle, BirthDate, LoginID • TSQL • We have another view named vw_Sales_Products. This has information on Sales Order and Products

  6. Functions • Function are used to return a value to the calling program • They can also accept input parameters • Can be used in SELECT statement • Two Types • System Functions • User defined Functions • System Functions like GETDATE(), COUNT(*) • User defined Functions can include Scalar valued(return one value) or Table valued (returns a table)

  7. Function demos • Two Examples • First one is dbo.fnAvgUnitPrice takes ProductID as an input, processes the data and returns Average price from SalesOrderHeader table • Second one uses the function in a Select statement to show information from Order plus the Average Price • We have saved the TSQL in this file Using Functions.sql

  8. Stored Procedure • A stored procedure is a group of Transact-SQL statements compiled into a single execution plan. • Invoke other procedures and functions • SQL Server stored procedures return data in four ways: • Output parameters, which can return data • Return codes or Raise exceptions • A result set for each SELECT statement contained in the stored procedure. • A global cursor that can be referenced outside the stored procedure

  9. Advantages of using Stored Procedures • Stored procedures can use input and output parameters • Executes faster than writing T-SQL as it is compiled and part of the database • Code reuse and portability • Typically used to enforce Business Rules • This is another security mechanism which allows the DBA to give access to the stored procedure only and not the underlying data • One disadvantage is that they cannot be used in SELECT statement

  10. Demos • In the first one, we want to find Employees that were terminated. In addition to displaying the results we would like to send an email using Database Mail to a Manager regarding this. Using code from sp_send_dbmail_termination.sql • In the second one, we use the TSQL from the previous Function, dbo.fnAvgUnitPrice. One difference is that we can use the output from stored procedure to fill a temp table that we can use some where else. Using script tmp_Order.sql

  11. Triggers • Triggers are special class of stored procedure defined to execute automatically when an UPDATE, INSERT, or DELETE statement is issued against a table. • In other words a trigger is invoked when a specified database activity occurs • Triggers can be used to: • Enforce business rules • Set complex default values • Update views • Implement referential integrity actions • Triggers can roll back the transactions that caused them to be fired • Triggers are created at the table level.

More Related