1 / 58

User-Defined Functions in SQL Server 2005

User-Defined Functions in SQL Server 2005. Andrew Novick. 2007. Agenda. What are User-Defined Functions Three types: Scalar In-Line Table Valued Multi-statement Table Valued Why use User-Defined Functions What You Can’t Do With UDFs and Why!

apollo
Download Presentation

User-Defined Functions in SQL Server 2005

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. User-Defined Functionsin SQL Server 2005 Andrew Novick 2007

  2. Agenda • What are User-Defined Functions • Three types: • Scalar • In-Line Table Valued • Multi-statement Table Valued • Why use User-Defined Functions • What You Can’t Do With UDFs and Why! • Formatting, Naming and Documenting UDFs for Reuse • Debugging, Encryption and Schemabinding • Performance Implications of UDFs • System functions www.NovickSoftware.com

  3. Transact-SQL User-Defined Functions www.NovickSoftware.com

  4. Novick Software • The consulting company of Andrew Novick • Business Applications Design, Architecture, Programming, Project Management, Coaching • SQL Server, VB.Net, ASP.Net, XML • http://www.NovickSoftware.com Home of the www.NovickSoftware.com

  5. What are User-Defined Functions? • Transact-SQL routines used to encapsulate program logic. www.NovickSoftware.com

  6. Types of UDFs • T-SQL Scalar UDF • T-SQL Inline Table-valued UDF • T-SQL Multi-statement Table-valued UDF • CLR Scalar • CLR Multi-Row • CLR Aggregate www.NovickSoftware.com

  7. T-SQL: Scalar • T-SQL routine with zero or more parameters • Returns a single scalar value CREATE FUNCTION udf_Area ( @Length float , @Width float ) RETURNS float AS BEGIN RETURN @Length * @Width END www.NovickSoftware.com

  8. T-SQL: Inline • A View with parameters CREATE FUNCTION dbo.udf_AuthorsByLetter ( @Letter CHAR(1) ) RETURNS TABLE AS RETURN SELECT * FROM pubs..Authors WHERE LEFT(au_lname, 1) = @Letter www.NovickSoftware.com

  9. T-SQL: Multistatement • Returns a single defined table CREATE FUNCTION dbo.udf_FactorialsTAB (@N int ) RETURNS @Factorials TABLE (Number INT, Factorial INT) AS BEGIN DECLARE @I INT, @F INT SELECT @I = 1, @F = 1 WHILE @I < = @N BEGIN SET @F = @I * @F INSERT INTO @Factorials VALUES (@I, @F) SET @I = @I + 1 END -- WHILE RETURN END www.NovickSoftware.com

  10. CLR: Scalar • A C# or VB.Net shared function bound to a SQL Function definition www.NovickSoftware.com

  11. CLR: Multi-Row A C# or VB.Net Structure or Class that returns a resultset. www.NovickSoftware.com

  12. CLR: Aggregate • A C# or VB.Net Structure or Class implementing a required set of methods to produce an aggregate result www.NovickSoftware.com

  13. T-SQL User Defined Functions

  14. Scalar User-Defined Functions (1) • Written in T-SQL like a stored procedure • Takes zero or more parameters • Return a single value of a scalar data type:int varchar (30) numeric (18,3)varchar(max) varbinary(max) • Does not return Text, Timestamp, Image www.NovickSoftware.com

  15. Scalar User-Defined Functions (2) CREATE FUNCTION udf_Area ( @Length float , @Width float ) RETURNS float AS BEGIN RETURN @Length * @Width END www.NovickSoftware.com

  16. Using Scalar UDFs • SELECT List • SELECT dbo.udf_Area(5.5, 4) • SELECT … dbo.udf_Area (measured_length * 1000, total_width) FROM… • WHERE CLAUSE • JOIN CLAUSE • ORDER BY www.NovickSoftware.com

  17. Ways to Use Scalar User-Defined Functions? • In the SELECT list • In the WHERE clause • In a CHECK Constraint • In the ON clause of a JOIN • In an ORDER BY clause www.NovickSoftware.com

  18. Data Manipulation in T-SQL UDFs • May SELECT from tables and views • May not INSERT UPDATE or DELETEExcept to TABLE variables SELECT dbo.udf_Addr_ZIP5DistanceMI ('02451', '98052') www.NovickSoftware.com

  19. Profiling UDF Execution • Turn on T-SQL Statement Start/Complete events. • SQL Profiler has a big impact on duration and CPU time. • It’s difficult to use it as a measure of performance for UDFs of T-SQL Statement events are on. www.NovickSoftware.com

  20. Debugging Scalar UDFsin SQL Server 2000 • Query Analyzer Debug Menu is Disabled • To Debug create a Stored Procedure • Call the UDF in the SP • Step into the UDF • Visual Studio .Net Server Explorer allows for direct debugging • Every instance of SQL Server must be run as a domain user, not LocalSystem www.NovickSoftware.com

  21. Debugging Scalar UDFs in SQL Server 2005 • Use Visual Studio 2005 database project www.NovickSoftware.com

  22. Things You Can’t Do in UDFs • INSERT, UPDATE, DELETE rows • Except to TABLE variables • Not to TEMP tables • Execute Stored Procedures • Extended SPs that don’t return rowsets are OK • DBCC • RAISERROR • PRINT • Use nondeterministic built-in functions! • BEGIN CATCH…. END TRY www.NovickSoftware.com

  23. Deterministic Functions • Deterministic functions return the same result any time they are called with a specific set of input values. Udf_Area (5.5, 4) • Non-deterministic functions don’t. Getdate(), RAND() www.NovickSoftware.com

  24. Inline Table-Valued UDFs • Return TABLE • Are equivalent to a VIEW with parameters. • INSERT, UPDATE, DELETE are possible www.NovickSoftware.com

  25. Multistatement Table-Valued UDFs • Return a table that’s defined in the header • Multiple T-SQL statements • Can’t change the state of the database • My use extended stored procedures that don’t return rowsets www.NovickSoftware.com

  26. Encrypting UDFs • Protects the text of the UDF • Add the with Encryption keywordCREATED FUNCTION udf_xxx () Returns With Encryption • SQL Server 2000 encryption has been broken! Don’t count on it. www.NovickSoftware.com

  27. Schemabinding • Create Function () WITH SCHEMABINDING • Prevents the alteration of database objects that are referenced by the UDF • All objects UDFs and Views referenced must also be schemabound. • All Objects referenced must use two part names. www.NovickSoftware.com

  28. Schemabinding Recommendations • Always bind scalar UDFs when they don’t reference any other object. • Bind UDFs that reference data only when you have a special reason to do so. • Bind inline and multistatement UDFs only to protect your schema from changes. www.NovickSoftware.com

  29. Error Handling • You will not get the same chance to handle run-time errors that you do in stored procedures or triggers • For scalars, the best solution is usually to return NULL as the result of the function. • BEGIN CATCH…. END TRY not allowed! • Errors can be reported in the SQL Log. www.NovickSoftware.com

  30. System UDFs (1) • Defined in master • Owned by system_function_schema • Begin with “fn_” • All lower case letters • Referenced with special :: syntax www.NovickSoftware.com

  31. CLR Functions In SQL Server 2005 • Scalar User Defined Functions • Multi-Row User Defined Functions • User Defined Aggregate www.NovickSoftware.com

  32. SQLCLR Functions

  33. Types of SQLCLR Code in S2K5 • Stored Procedures • User Defined Functions • User Defined Aggregates • User Defined Types • Triggers www.NovickSoftware.com

  34. Assemblies • .Net Code compiled into an IL DLL • Assemblies must be added to S2K5 with CREATE ASSEMBLY • Bits are stored in the database’s sys.assembly_files table CREATE ASSEMBLY my_assembly_name FROM ‘\\myserver\directory…path\MyAssembly.dll’ www.NovickSoftware.com

  35. Code Access SecurityPERMISSION_SETs • SAFE • EXTERNAL_ACCESS • UNSAFE www.NovickSoftware.com

  36. PERMISSION_SET: Safe • May not access external resources: registry, file system, or network • May not use unmanaged code or PInvoke • May access data using the current context but not via SQLClient or any other data provider • No thread processing www.NovickSoftware.com

  37. PERMISSION_SET: EXTERNAL_ACCESS • May access external resources:registry, file system, network, environment variables • May not use unmanaged code or PInvoke www.NovickSoftware.com

  38. PERMISSION_SET: UNSAFE • May access external resources • May use unsafe code and PInvoke • Can use SQLClient and other data providers • Can use thread constructs • This is no more unsafe than extended stored procs www.NovickSoftware.com

  39. SQLCLR User-Defined Functions • Scalar • Return a single value • Multi-Statement UDFs • Returns a single result set • No .Net Inline UDFs www.NovickSoftware.com

  40. Creating a Scalar UDF • Create the assembly first • Then the T-SQL definition: CREATE FUNCTION udf_myFunction ( @Parm1 int -- First Parameter , @Parm2 varcharmax – 2nd Parm ) RETURNS BIT EXTERNAL NAME assemblyname.class.method www.NovickSoftware.com

  41. Creating Table Valued UDF • Create the assembly • Method returns an IEnumerable • CREATE FUNCTION script defines the schema of the result www.NovickSoftware.com

  42. Type Considerations • .Net reference types don’t represent NULL • System.Data.SQLTypes represent NULL • Use the SQLTypes when possible www.NovickSoftware.com

  43. Inprocess Data Provider • System.Data.SQLServer Provider • Implements the IData* interfaces • Parallels the SQLClient Provider • Exposes Additional Classes via SQLContext www.NovickSoftware.com

  44. User Defined Aggregates • Aggregates scalar values into another scalar • Uses the SqlUserDefinedAggregate attribute CREATE AGGREGATE [Product](@Value float)                             Returns [float]        EXTERNAL NAME [SampleAggregate].[SampleAggregate.Product]goGRANT EXEC ON dbo.Product TO PUBLICgo www.NovickSoftware.com

  45. User Defined Aggregate Class public class myAggregate { public void Accumulate (<input-type> value) { } public <return-type> Terminate() { } public void Init () { } public void Merge (myAggregate) { } } www.NovickSoftware.com

  46. Using the User Defined Aggregate SELECT dbo.product(sample) [Three integers]    FROM (SELECT CAST(1.0 as float) as Sample          UNION ALL SELECT 3          UNION ALL SELECT 5         ) Numbers (Result) Three integers  --------------- 15     www.NovickSoftware.com

  47. Performance Experiment • Test on 1,000,000 row table pinned in memory • 2 CPU system www.NovickSoftware.com

  48. Best Practices • Pick one .Net language • Build assemblies with SQL Server in mind • Use the most restrictive Permission Set possible • Move as much .Net/CLR code to the middle tier as possible • Test via the SQL Interface www.NovickSoftware.com

  49. Why Use .Net in S2K5 • Replace Extended SP’s with a safer alternative • Replace COM automation: sp_OA_* • Take advantage of .Net Framework classes • Run compute intense algorithms • Reuse code • Programmer productivity • New capabilities not available in T-SQL • User Defined Types • User Defined Aggregates www.NovickSoftware.com

  50. Why Use SQLCLR in S2K5 • Encapsulation • Functions and Aggregates allow the encapsulation of business rulesin reusable form. www.NovickSoftware.com

More Related