Now that you understand how to formulate SQL queries and return result sets, you need to do something useful with this data. Once you have successfully retrieved values from tables, it's very common to further manipulate values to provide useful and meaningful results. This may involve the following:
Performing calculations and mathematical operations
Conversion
Parsing
Combining values
Aggregation
The purpose of this chapter is to help you learn the mechanics of using functions of all kinds. It introduces you to some of the more common value manipulation functions and some less-common functions to give a sample of these powerful capabilities. You'll also take a look at some new functionality offered in SQL Server 2005.
At the end of the book, you'll find a reference for all of the system-supplied functions and the syntax needed to use them. Additionally, subsequent chapters contain more detailed information about specific groups of functions. For example, Chapter 7 discusses specific uses for aggregate functions in more advanced SQL queries, and Chapter 11 shows you how to use functions to support full-text index searches.
Transact-SQL functions are grouped into the categories described in the following table.
Function Category | Purpose |
---|---|
Aggregation | Aggregate functions return a scalar value representing an aggregation over a range of values, applying a specific aggregate selection or summary. |
Configuration Variables | Return information about the SQL Server execution environment that may be useful in programming objects. |
Conversion | Used to convert values of one data type to another. Also used to apply formatting characteristics to dates, times, and numeric values. |
Cursor | Used to loop through the rows in a result set in a procedural manner when iterating through a cursor. |
Date | A set of functions used for parsing the date and time portions of a date value, and for comparing and manipulating date/time values. |
Image/Text | Includes both specialized functionality, specific to the Text, nText, and Image types; and functions equivalent to those for standard character types. |
Mathematical | Used to perform a variety of common and specialized mathematical operations. Useful in performing algebraic, trigonometric, statistical, approximating, and financial operations. |
Metadata | Utility functions that return information about the SQL Server configuration details and details about the server and database settings. |
Ranking | New function in SQL Server 2005 used to enumerate sorted and top-valued result sets. |
Security | Returns role membership and privilege information for SQL Server users. Also includes a set of functions to manage events and traces. |
String Manipulation | Used to parse, replace, and manipulate character values. |
System | Utility functions used to perform a variety of tasks. These include value comparisons and value type testing. This category is also a catch-all for other functionality. |
System Statistical | Administrative utilities used to discover database system usage and environment information. |