Completing the Introduction to Transact-SQL - The World of Functions
(Page 4 of 4 )
The World of Functions
The Transact-SQL programming language has three types of built-in functions: aggregate functions, rowset functions, and scalar functions. The aggregate functions work on the summarizing principle; they work on a group of values but return one single value. Rowset functions are in general used as table references. Scalar functions, on the other hand, work on just one value and likewise return one single value.
Check out the following table of aggregate functions:
AVG | Returns the average of the values in the specified group. |
COUNT | Returns the count of the items in the specified group. |
GROUPING | Creates an additional column with values of 1 or 0 depending on whether the rows were added by either CUBE or ROLLUP operators, or not by those, respectively. |
MAX | Returns the maximum value in the specified expression. |
MIN | Returns the minimum value in the specified expression. |
SUM | Returns the sum of the numerical values in the specified expression. |
STDEV | Returns the statistical standard deviation of the values in the specified expression. |
STDEVP | Returns the statistical standard deviation for the population of the values in the specified expression. |
VAR | Returns the statistical variance of the values in the specified expression. |
VARP | Returns the statistical variance for the population of the values in the specified expression. |
As I mentioned, rowset functions return an object that can be used as a reference in a table. We have the following rowset functions: CONTAINSTABLE, FREETEXTTABLE, OPENQUERY, and OPENROWSET. But explaining these is beyond the scope of this article.
As for the scalar functions, we have dozens of them, but we categorize them in the following distinct groups:
Mathematical Functions: ABS, COS, EXP, LOG, PI, SIN, SQRT, and more.
String Functions: LEFT, LOWER, LTRIM, NCHAR, REPLACE, REVERSE, etc.
Date and Time Functions: GETDATE, DAY, MONTH, YEAR, and so forth.
System Functions: CURRENT_USER, NEW_ID, PERMISSIONS, and such.
Configuration Functions: @@LANGUAGE, @@MAX_PRECISION, etc.
Cursor Functions: @@FETCH_STATUS, CURSOR_STATUS, etc.
Metadata Functions: DB_ID, DB_NAME, FILE_ID, FILE_NAME, etc.
Security Functions: IS_MEMBER, SUSER_ID, SUSER_NAME, USER_ID, etc.
System Statistical Functions: @@CPU_BUSY, @@IDLE, @@PACK_SENT, etc.
Text and Image Functions: TEXTPTR, TEXTVALID, PATINDEX.
Now let's talk about user-defined functions (UDFs). At the beginning of this article I pointed out that there are three types of UDFs: scalar functions, inline table-valued functions, and multistatement table-valued functions. Let's see which is which.
Scalar UDFs always return a single data value, which can be of any type except text, ntext, image, cursor, table, timestamp, and of course other user-defined types. Inline table-valued functions return a table type object, which is basically the result set of a single SELECT statement. Multistatement table-valued functions also return a table type object; however, these can contain a larger number of T-SQL statements.
We declare user-defined functions using the CREATE FUNCTION statement. Moreover, we shouldn't forget about the RETURNS part where we explicitly specify the data type (scalar) that's going to be returned later on.
Check out the following prime of a scalar UDF example that returns the new sale price of a product using the next formula: new sale price = price * discount.
-- We declare the function alongside we specify the RETURNS
CREATE FUNCTION DiscPrice (@Price money, @Disc float)
RETURNS money
-- Now we specify what exactly to do in the function
AS
BEGIN
RETURN @Price * @Disc
END
As you can see from the above example, the statements that are to be executed in order to calculate the returnable value are enclosed in the "BEGIN...END" block of code. So that's where we write the function per se. Also, don't forget that we need to call user-defined functions using the owner_name.fuction_name() principle; generally, the owner is akin to the database user that has created the function.
Working with inline table-valued functions is different. The returned object is of the table data type. There's no "BEGIN...END" code block because the whole function is built up by a single SELECT instruction. However, should we need to use multiple statements, we are going to create a multistatement table-valued function. In this case we need to enclose the statements inside the "BEGIN...END" block of code.
Final Thoughts
We've come to the end of this series. I really hope that you found it informative.
Throughout this segment we covered most of the error handling concepts including the statements RETURN, RAISERROR, and the TRY...CATCH block of code. Moving on, we discussed cursors and how we should work with them. Then we drifted into the world of functions. First, we analyzed some of the built-in function categories.
Likewise, today we learned how we can create user-defined functions. UDFs should not be confused with stored procedures. Why? Stored procedures cannot be used like user-defined functions because they are not expressions. Procedures accept parameters and execute T-SQL instructions in relation to those specified parameters.
Microsoft SQL Server allows the user to create user-defined functions in the .NET programming languages, not only in T-SQL. Other DB management systems such as Oracle allow the creation of UDFs in other languages like Java and C, too.
Anyway, the time has come for me to end this article. Thanks for reading! Oh, and by the way, should you need any more help then don't hesitate to join "ASP Free Forums" and ask for help. Our community is knowledgeable, practical, and easy-going.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
