1 / 20

Mastering User-Defined Functions in SQL Server: Types, Creation, and Execution Context

This module focuses on implementing functions in SQL Server, detailing various function types, such as scalar, inline table-valued, and multi-statement table-valued functions. It covers the creation of these functions, the importance of deterministic vs. nondeterministic functions, and best practices for function design. Additionally, it explains how to control execution context using the EXECUTE AS clause, establishing trust relationships, and demonstrating practical exercises to create and utilize user-defined functions. Enhance your SQL skills by mastering these critical concepts.

meredith-anchoret
Télécharger la présentation

Mastering User-Defined Functions in SQL Server: Types, Creation, and Execution Context

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. Module 8:Implementing Functions

  2. Module 8:Implementing Functions • Introducing Functions • Working with Functions • Controlling Execution Context

  3. Lesson 1: Introducing Functions • Types of Functions • What Is a Scalar Function? • What Is an Inline Table-Valued Function? • What Is a Multi-Statement Table-Valued Function?

  4. Types of Functions Types of Functions Scalar Functions ü Inline Table-Valued Functions ü Multi-Statement Table-Valued Functions ü Built-in Functions ü

  5. What Is a Scalar Function? Scalar Functions: Return a single data value ü Can be either inline or multi-statement ü Can return any data type except for text, ntext, image, cursor, and timestamps ü CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ = default ] [ READONLY ] } [ ,...n ] ] ) RETURNS return_data_type

  6. What Is an Inline Table-Valued Function? Inline Table-Valued Function: Returns a TABLE data-type ü Has no function body ü Is comprised of a single result set ü CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] [ READONLY ] } [ ,...n ] ] ) RETURNS TABLE

  7. What Is a Multi-Statement Table-Valued Function? Multi-statement Table-Valued Function: Returns a TABLE data-type ü Has a function body defined by BEGIN and END blocks ü Defines a table-type variable and schema ü Inserts rows from multiple Transact-SQL statements into the returned table ü CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] [READONLY] } [ ,...n ] ] ) RETURNS @return_variable TABLE <table_type_definition>

  8. Demonstration: Creating Functions • In this demonstration, you will see how to: • How to create an inline scalar function • Call your function from a Transact-SQL statement

  9. Lesson 2: Working with Functions • Deterministic and Nondeterministic Functions • Guidelines for Creating Functions • Rewriting Stored Procedures as Functions

  10. Deterministic and Nondeterministic Functions Determines whether a user defined function is deterministic or not SELECT [IsDeterministic] = objectproperty(object_id('dbo.myUDF'), 'IsDeterministic')

  11. Guidelines for Creating Functions Determine function type ü Create one function for one task ü Create, test, and troubleshoot ü Qualify object names inside function ü Consider ability of SQL Server 2008 to index function results ü

  12. Rewriting Stored Procedures as Functions Convert your stored procedure to a function if: It is expressible as a single SELECT statement ü It does not perform update operations ü It does not require a dynamic EXECUTE statement ü It only returns one result set ü Its primary purpose is to build intermediate results ü

  13. Lesson 3: Controlling Execution Context • What Is Execution Context? • The EXECUTE AS Clause • Extending Impersonation Context

  14. What Is Execution Context? Ted (No permissions) Pat (SELECT permission) Sales.Order (Owner: John) GetOrders Ted (EXECUTE permission) Function (Owner: Pat) Pat CREATE FUNCTION GetOrders RETURNS TABLE AS RETURN ( SELECT * FROM Sales.Order ) CREATE FUNCTION GetOrders RETURNS TABLE WITH EXECUTE AS 'Pat' AS RETURN ( SELECT * FROM Sales.Order )

  15. The EXECUTE AS Clause The Execute AS Clause: Enables Impersonation ü Provides access to modules via impersonation ü Can be used to impersonate server-level principals or logins via the EXECUTE AS LOGIN statement ü Can be used to impersonate database level principals or users via the EXECUTE AS USER statement ü CREATE FUNCTION GetOrders RETURNS TABLE WITH EXECUTE AS { CALLER | SELF | OWNER | ‘user_name’ } AS RETURN ( SELECT * FROM Sales.Order )

  16. Extending Impersonation Context Required conditions for extending impersonation scope: Authenticator must be trusted in target scope ü Source database must be marked as trustworthy ü

  17. Lab: Implementing Functions • Exercise 1: Creating Functions • Exercise 2: Controlling Execution Context Logon information Estimated time: 60 minutes

  18. Lab Scenario Adventure Works maintains a list of special offers and discounts for various products throughout the year that applies to both customers and resellers. Currently, this information is only accessible directly from the Sales.SpecialOffer table. A new requirement is to retrieve this information by using user-defined functions. For this, you need to create a scalar user-defined function named GetCurrencyRate within the Sales schema that retrieves the latest currency conversion rate for a specific currency. You must then establish a trust relationship between the AdventureWorks2008 and AdventureWorksDW2008 databases to enable the GetCurrencyRate function to retrieve the currency data. Finally, you need to create a multi-statement table-valued user-defined function named GetCurrencyDiscountedProducts within the Sales schema that that uses a complex query to retrieve products that have a discount.

  19. Lab Review • When might it be practical to use an Inline Table-Valued Function? • When would you use the Authenticate permission?

  20. Module Review and Takeaways Review Questions Real-world Issues and Scenarios

More Related