Summary

Functions do the heavy lifting of your business logic and can be used to apply programming functionality to queries. Several useful and powerful functions are standard features of Transact-SQL. You learned that SQL functions, like functions in procedural and object-oriented programming languages, encapsulate programming features into a simple and reusable package. This takes a lot of the work out of the query designer's hands. You know that Transact-SQL is a task-oriented language rather than a procedural language. Although functions give you the option to tread the procedural line, building fairly complex logic into queries, the strength of the language is in allowing the designer to state his intentions rather than the exact steps and methods that must be used to perform a task. Used correctly, functions allow you to do just that.

In Transact-SQL, arguments are used to pass values into a function and most functions return a scalar, or single-value, result. Functions are categorized as either deterministic or nondeterministic. A deterministic function will always return the same value when called with the same argument values. Nondeterministic functions depend on other resources to determine the return value; therefore SQL Server must execute the function explicitly. For this reason, there are some restrictions on the use of nondeterministic functions in custom SQL programming objects.

SQL functions perform a wide variety of important tasks including mathematical operations, comparisons, date parsing and manipulation, and advanced string manipulation. Several categories of specialized functions are introduced along with their related topics in following chapters. A complete function syntax reference is also provided in Appendix B.