Types of Functions in SQL Server 2008

Today, I’m going to talk about types of functions in the SQL Server 2008 over here. At first, I would like to mention that we may categorize functions into various different types of categories based upon parameters, which we are considering for categorization. However, in general we can categorize SQL Server functions into two broad categories:

1. Built-In Functions: As it says, these functions are ready-made. SQL Server has offerred us set of functions, which we can just call and make use of it. However, we can further sub-categorize these functions into four sub-categories:

• Aggregate Functions: I’m sure everyone is aware of this category. These fucntions accept group of values and return a single, summarized value. We usually use these functions either into SELECT or GROUP BY clause. Example, Avg, Min, Max, Count, etc.
• Ranking Functions: This category is unique one as it gives a row number to a row in a group or partition of rows. These functions are very useful and handy when we are doing some sort of statistical analysis with data. Example, RANK, DENSE_RANK, NTILE and ROW_NUMBER.
• Rowset Functions: Return an object that we can use in place of a table reference i.e WHERE clause or in a subquery. Example, FREETEXTTABLE, OPENDATASOURCE, OPENQUERY, OPENXML, etc.
• Scalar Functions:The word ‘Scalar’ says it all. It return a single value and, of course, it operates on a single value only. Here, we can again sub-categorize the Scalar functions into ten subcategories:
  • String Functions: Operate on char or varchar input types and return a single value of either string or numetic data type.
  • Mathematical Functions: Operate on numetic data type input values and return a numeric value.
  • Data & Time Functions: Operate on date and time data type and return either string or numetic or date and time data type value.
  • System Functions: Though, these are built-in function but we may keep them over here based upon their return type. Return values, objects or setting in an instance of SQL Server.
  • Cursor Functions: Return information about a cursor.
  • Security Functions: Return information about roles or users in SQL Server.
  • System Statistical Functions: Return statistical information about the system. Example, @@CPU_BUSY, @@IDLE, etc.
  • Text & Image Functions: These functions would be dropped off in coming version of SQL Server. Operate on text and image data type and return information about value.
  • Metadata Functions: Return information about database and its objects.
  • Configuration Functions: Returns information about the current configuration of an instance of SQL Server.

2. User-Defined Functions: SQL Server offers a feature where users can create their own functions and make use of it inside T-SQL statements. There are three types of user-defined functions:

• Scalar Functions: Here again, we’ve got scalar category but user-defined scalar functions, and not built-in ones. It returns a single value and, of course, it operates upon a single input value.
• Inline Table-Valued Functions: Return a set of rows as TABLE data type. It can have only single SELECT statement. A user doesn’t have liberty to define the table structure. Whatever SELECT clause returns, returned table takes that structure.
• Multistatement Table-Valued Functions: Returns a set of rows as TABLE data type. Unlike Inline Table-Valued function, it can have multiple SELECT statements and user can define the structure of the table being returned.

We may categorize functions as Deterministic and Non-Deterministic as well but, as I mention in the beginning, it requires different set of parameters for categorization. I’ll definitely discuss about Deterministic and Non-Deterministic types but in a separate thread, in another blog. So, follow my blogs to catch it…