module 13 designing and implementing user-defined functions
DESCRIPTION
Module 13 Designing and Implementing User-Defined Functions. Module Overview. Overview of Functions Designing and Implementing Scalar Functions Designing and Implementing Table-valued Functions Implementation Considerations for Functions Alternatives To Functions. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/1.jpg)
Module 13Designing and
Implementing User-Defined Functions
![Page 2: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/2.jpg)
Module Overview• Overview of Functions• Designing and Implementing Scalar Functions• Designing and Implementing Table-valued Functions• Implementation Considerations for Functions• Alternatives To Functions
![Page 3: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/3.jpg)
Lesson 1: Overview of Functions• Types of Functions• System Functions
![Page 4: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/4.jpg)
Types of Functions
Scalar Functionsü
Table-valued Functions
Inline vs Multi-Statement Functions
ü
ü
System Functionsü
Functions cannot modify the data in the database
![Page 5: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/5.jpg)
System Functions• SQL Server includes a large number of built-in functions• Most functions are scalar functions
• Other types of functions are also provided
Configuration Cryptographic CursorData Type Date and Time MathematicalMetadata ODBC ReplicationSecurity String SystemStatistical Text and Image Trigger
Rowset Aggregate Ranking
![Page 6: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/6.jpg)
Lesson 2: Designing and Implementing Scalar Functions• What is a Scalar Function?• Creating Scalar Functions• Deterministic and Non-deterministic Functions• Demonstration 2A: Scalar Functions
![Page 7: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/7.jpg)
What Is a Scalar Function?
Scalar Functions: Return a single data valueü Can return any data type except rowversion, cursor and table when implemented in T-SQLü
Can return any data type except for rowversion, cursor, table, text, ntext and image when implemented in managed code
ü
![Page 8: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/8.jpg)
Creating Scalar Functions
Scalar UDFs return a single data-typeü
Scalar UDFs usually include parametersü
CREATE FUNCTION dbo.RectangleArea (@X1 float, @Y1 float, @X2 float, @Y2 float)RETURNS floatAS BEGIN RETURN ABS(@X1 - @X2) * ABS(@Y1 - @Y2);END;
CREATE FUNCTION must be the only statement in a batchü
![Page 9: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/9.jpg)
Deterministic and Non-deterministic Functions
Type DescriptionDeterministic Functions
Always return the same result given a specific input for a given database state
Non-deterministic functions
May return different results given a specific input
Built-in functions Are deterministic or non-deterministic depending on how they are applied
CREATE FUNCTION dbo.TodayAsString(@Format int = 112)RETURNS VARCHAR(20)AS BEGIN RETURN CONVERT(VARCHAR(20), CAST(SYSDATETIME() AS date) , @Format);END;GOSELECT OBJECTPROPERTY(OBJECT_ID('dbo.TodayAsString'), 'IsDeterministic');
![Page 10: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/10.jpg)
Demonstration 2A: Scalar FunctionsIn this demonstration, you will see:• How to create scalar user-defined functions• How to query scalar user-defined functions• How to determine if a scalar user-defined function is
deterministic• How to drop scalar user-defined functions
![Page 11: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/11.jpg)
Lesson 3: Designing and Implementing Table-valued Functions• What are Table-valued Functions?• Inline Table-valued Functions• Multi-statement Table-valued Functions• Demonstration 3A: Implementing Table-valued Functions
![Page 12: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/12.jpg)
What are Table-valued Functions?
Table-valued Functions: Return a TABLE data-typeü Have a function body with only a single SELECT statement when created inline
Construct a table within the function and then return it for multi-statement TVFs
ü
ü
Are queried like a tableü
Are often used like parameterized viewsü
![Page 13: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/13.jpg)
Inline Table-Valued Functions
CREATE FUNCTION Sales.GetLastOrdersForCustomer (@CustomerID int, @NumberOfOrders int)RETURNS TABLEASRETURN (SELECT TOP(@NumberOfOrders) soh.SalesOrderID, soh.OrderDate, soh.PurchaseOrderNumber FROM Sales.SalesOrderHeader AS soh WHERE soh.CustomerID = @CustomerID ORDER BY soh.OrderDate DESC, soh.SalesOrderID DESC );GOSELECT * FROM Sales.GetLastOrdersForCustomer(17288,2);
• Are comprised of a single result set and have no function body
• Have a returned table definition taken from a SELECT statement
• Can be seen as a parameterized view
![Page 14: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/14.jpg)
Multi-statement Table-valued Functions
CREATE FUNCTION dbo.GetDateRange (@StartDate date, @NumberOfDays int)RETURNS @DateList TABLE (Position int, DateValue date)AS BEGIN DECLARE @Counter int = 0; WHILE (@Counter < @NumberOfDays) BEGIN INSERT INTO @DateList VALUES(@Counter + 1, DATEADD(day,@Counter,@StartDate)); SET @Counter += 1; END; RETURN;END;GOSELECT * FROM dbo.GetDateRange('2009-12-31',14);
• Function body enclosed by BEGIN and END• Definition of returned table must be supplied• Table variable populated within function body and then returned
![Page 15: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/15.jpg)
Demonstration 3A: Implementing Table-valued FunctionsIn this demonstration, you will see:• How to create a table-valued function• How to query a table-valued function• How to drop a table-valued function
![Page 16: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/16.jpg)
Lesson 4: Implementation Considerations for Functions• Performance Impacts of Scalar Functions• Performance Impacts of Multi-statement Table-valued
Functions• Controlling Execution Context• Using the EXECUTE AS Clause• Guidelines for Creating Functions• Demonstration 4A: Execution Context
![Page 17: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/17.jpg)
Performance Impacts of Scalar Functions• Code for scalar functions is not incorporated into the
surrounding query Different to views in this respect
• Scalar functions used in SELECT lists or WHERE clause predicates can suffer significant performance-related issues
SELECT soh.CustomerID, soh.SalesOrderID FROM Sales.SalesOrderHeader AS sohWHERE soh.SalesOrderID = dbo.GetLatestBulkOrder(soh.CustomerID)ORDER BY soh.CustomerID, soh.SalesOrderID;
![Page 18: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/18.jpg)
Performance Impacts of Multi-statement Table-valued Functions• Code for table-valued functions is not in-lined into the
surrounding query Inline TVFs are an exception Performance can be poor except where executed only one time
in a query Very common cause of performance problems CROSS APPLY can cause table-valued functions to be
repeatedly executed
SELECT c.CustomerID, c.AccountNumber, glofc.SalesOrderID, glofc.OrderDate FROM Sales.Customer AS cCROSS APPLY Sales.GetLastOrdersForCustomer(c.CustomerID,3) AS glofcORDER BY c.CustomerID,glofc.SalesOrderID;
![Page 19: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/19.jpg)
Controlling Execution Context
Sales.Order(Owner: John)
Ted(No permissions)
Function(Owner: Pat)
GetOrders
Ted(EXECUTE permission) Pat
Pat(SELECT permission)
CREATE FUNCTION GetOrders RETURNS TABLEASRETURN ( SELECT * FROM Sales.Order )
CREATE FUNCTION Sales.GetOrders RETURNS TABLEWITH EXECUTE AS 'Pat'ASRETURN ( SELECT * FROM Sales.Order );
![Page 20: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/20.jpg)
The EXECUTE AS ClauseThe 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 Sales.GetOrders RETURNS TABLEWITH EXECUTE AS {CALLER | SELF | OWNER | ‘user_name’ }ASRETURN ( SELECT * FROM Sales.Order );
![Page 21: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/21.jpg)
Guidelines for Creating Functions
Determine function typeü
Create one function for one taskü
Consider the performance impacts of how you intend to use the functionü
Qualify object names inside functionü
Consider the ability of SQL Server to index function resultsüFunctions cannot contain structured exception handlingü
![Page 22: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/22.jpg)
Demonstration 4A: Execution Context In this demonstration, you will see how to alter the
execution context of a function
![Page 23: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/23.jpg)
Lesson 5: Alternatives to Functions• Comparing Table-valued Functions and Stored Procedures• Comparing Table-valued Functions and Views
![Page 24: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/24.jpg)
Comparing Table-valued Functions and Stored Procedures• Both can often achieve similar outcomes• Some source applications can only call one or the other• Functions
are easier to consume the output of in code can return table output in a variable cannot have data-related side effects often cause significant performance issues when multi-statement
• Stored procedures can alter the data can execute dynamic SQL statements can include detailed exception handling can return multiple result sets
![Page 25: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/25.jpg)
Comparing Table-valued Functions and Views• Can often achieve similar outcomes• Views
can be consumed by almost all applications very similar to tables can be updatable can have INSTEAD OF triggers associated with them
• Table-valued Functions are like parameterized views can often lead to significant performance problems (multi-
statement TVFs) are not in-lined into surrounding queries can only be updatable when inline
![Page 26: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/26.jpg)
Lab 13: Designing and Implementing User-Defined Functions• Exercise 1: Formatting Phone Numbers • Exercise 2: Modifying an Existing Function• Challenge Exercise 3: Resolve a Function-related
Performance Issue (Only if time permits)
Logon information
Estimated time: 45 minutes
Virtual machine 623XB-MIA-SQLUser name AdventureWorks\AdministratorPassword Pa$$w0rd
![Page 27: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/27.jpg)
Lab ScenarioThe existing marketing application includes some functions. Your manager has requested your assistance in creating a new function for formatting phone numbers. She also needs you to modify an existing function to improve its usability.Finally, if you have time, she would also like you to explore a performance-related problem with another existing function.
![Page 28: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/28.jpg)
Lab Review• When might it be practical to use an Inline Table-Valued
Function?• What is the biggest concern about the use of scalar
functions?• Why would you alter a function rather than dropping and
recreating it?
![Page 29: Module 13 Designing and Implementing User-Defined Functions](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816266550346895dd2d112/html5/thumbnails/29.jpg)
Module Review and Takeaways• Review Questions• Best Practices