Functions

1. Functions Lesson 10

2. Skills Matrix

3. Function • A function is a piece of code or routine that accepts parameters and stored as an object in SQL Server. The function always returns a result or result set from invocation. • A function can be called within a SELECT statement or even a WHERE clause, whereas a stored procedure must be called using an EXEC[UTE] procedure statement.

4. Function • SQL Server supports several types of functions: • Built-in functions • Scalar functions • Inline table-valued functions • Multistatement table-valued functions • CLR functions

5. Built-in Functions • You need to become familiar with a large number of functions provided to you by Microsoft. • Aggregate functions perform operations that combine multiple values into one value by grouping, summarizing, or averaging the values.

6. Built-in Functions

7. Built-in Functions • Configuration scalar functions return information about system settings. • Cryptographic functions support encryption, decryption, digital signing, and the validation of digital signatures. • EncryptByKey( ) • DecryptByKey( )

8. Built-in Functions • Configuration functions include server_name( ) and db_name( ), which gives you information about server and database configurations, respectively. • Cursor functions return information about the status of a cursor. • Date and time functions provide you with the capability to manipulate and calculate with dates and time values.

9. Mathematical Functions

10. Ranking Functions • Ranking functions are nondeterministic functions that return a ranking value for each row in a partition. • Ranking functions are new with SQL Server 2005 and allow you to use a rank or a row number within a result set.

11. Row Set, Security and String Functions • Rowset functions return the rowsets that can be used in place of a table referenced in a Transact-SQL statement • Security functions return information about users and roles. • String functions manipulate character text. Once again, examine each function in turn.

12. Execution Context • Execution context establishes the identity against which permissions are checked. • Without specifying the execution context, the user or login calling the module, such as a stored procedure or function, usually determines the permissions invoked.

13. Three Function Types • A scalar function passes and/or returns a single value. • A multistatement table-valued function proves to be a combination of a view and a stored procedure. • Inline table-valued functions return a table and are referenced in the FROM clause, just like a view.

14. CLR Functions • In the same way you can write managed code procedures, you now can also write a user-defined function in any .NET programming language. • Also, as with the scalar functions or a table-valued Transact-SQL function, a managed code (CLR) function can be scalar or table-valued. • Before you can use a managed function, you first need to enable CLR support on the server.

15. Deterministic and Nondeterministric Functions • SQL Server marks a function as: • A deterministic functionalways returns the same result, given a specific input value. • A nondeterministric function always returns a different value each time invoked.

16. Deterministic Function • You can create an index on a computed column if a function is deterministic. • This means whenever you update the row, the index also updates, and you could gain a lot of query performance when using the function in a query expression. • User-defined functions are deterministic when they are: • Schema-bound. • Defined with only deterministic user-defined or built-in functions.

17. CLR Functions • As with managed procedures, you use CLR functions to perform complex calculations or conversions that are outside the scope of a data-centric environment, or to create functionality that scopes outside of SQL Server and cannot be resolved within a Transact-SQL function. • All functions are deterministic or nondeterministic.

18. Nondeterministic Built-in Functions • SQL Server permits the use of nondeterministic built-in functions within user-defined functions, with the exception of NEWID( ), RAND(, NEWSEQUENTIALID( ), and TEXTPTR( ).

19. Schema Binding • Schema binding connects the function to the object that it references. • All attempts to drop the object referenced by a schema-bound function fails. • To create a function with the WITH SCHEMABINDING option, the following must be true: • All views and user-defined functions referenced by the function must be schema-bound as well. • All objects referenced by the function must be in the same database.

20. Summary • You learned that functions have three forms: scalar, multistatement table-valued, and inline table-valued. • Scalar types return a single value; for example, GETDATE( ) returns the current day and time. Both forms of table-valued functions return a dataset; for example, rows and columns.

21. Summary • Built-in functions perform common tasks. They have been developed over the years, and their number keeps increasing as new situations warrant the inclusion of new solutions in SQL Server. • You can create your own functions.

22. Summary for Certification Examination • Know the use of functions. • Know when to apply any of the three forms to specific scenarios. • Know, in general, the built-in functions. • Know, in particular, Substring( ), Datediff( ) and Dateadd( ). • Know the consequences of including nondeterministic functions and functions that reference alias data types.