{"id":1235,"date":"2011-11-10T00:00:00","date_gmt":"2011-11-10T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-server-functions-the-basics\/"},"modified":"2021-09-29T16:21:49","modified_gmt":"2021-09-29T16:21:49","slug":"sql-server-functions-the-basics","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/sql-server-functions-the-basics\/","title":{"rendered":"SQL Server Functions: The Basics"},"content":{"rendered":"<div id=\"pretty\">\n<p class=\"Start&gt;&lt;span lang=\">A function, in any programming environment, lets you encapsulate reusable logic and build software that is &#8220;composable&#8221;, <span class=\"STItalic\">i.e.<\/span> built of pieces that can be reused and put together in a number of different ways to meet the needs of the users. Functions hide the steps and the complexity from other code. <\/p>\n<p>However, in certain respects, SQL Server&#8217;s functions are fundamentally different from functions in other programming environments. In procedural programming, the piece of functionality that most programmers call a <b>function<\/b> should really be called a subroutine, which is more like a miniature program. These subroutines can go about changing data, introducing side effects, and generally misbehaving as much as they like.<\/p>\n<p>In SQL Server, functions adhere much more closely to their mathematic definition of mapping a set of inputs to a set of outputs. SQL Server&#8217;s functions accept parameters, perform some sort of action, and return a result. They do all of this with no side effects. Nevertheless, in the same way as subroutines, SQL Server functions can hide complexity from users and turn a complex piece of code into a re-usable commodity. Functions make it possible, for example, to create very complex search conditions that would be difficult and tedious to express in inline T-SQL.<\/p>\n<p>This article describes:<\/p>\n<ul>\n<li>The types of user-defined functions (UDFs) that SQL Server supports, both scalar (which return a single value) and table-valued (which return a table), and how to use them.  <\/li>\n<li>&#160;Some of the more interesting built-in functions  <\/li>\n<li>How and why functions can get you into trouble, and cause terrible performance, if you&#8217;re not careful about how you use them. <\/li>\n<\/ul>\n<h2>Fun Facts about Functions<\/h2>\n<p>This section describes, briefly, some of the basic characteristics of the various types of SQL Server function, which we&#8217;ll explore in more detail as we progress through the later examples.<\/p>\n<p>As noted in the introduction, <span class=\"STItalic\">all<\/span> SQL Server functions adhere closely to the mathematic definition of a function <span class=\"STItalic\">i.e.<\/span> a mapping of inputs to outputs, <span class=\"STItalic\">without<\/span> have side effects. A function with inputs <i>x<\/i> and <i>y<\/i> cannot both return <i>x<\/i> + <i>y<\/i> and modify the original value of <i>y<\/i>. As a matter of fact, that function couldn&#8217;t even modify <i>y<\/i>: &#160;it is only able to return a new value. <\/p>\n<h3>Where Can I Use A Function?<\/h3>\n<p>Anywhere!<\/p>\n<p>Well, we can use a function almost anywhere that we would use a table or column. We can use a function anywhere that we can use a scalar value or a table. Functions can be used in constraints, computed columns, joins, <code>WHERE<\/code> clauses, or even in other functions. Functions are an incredibly powerful part of SQL Server. <\/p>\n<h3>Functions can be Scalar or Table-valued<\/h3>\n<p>Scalar functions return a single value. It doesn&#8217;t matter what type it is, as long as it&#8217;s only a single, value rather than a table value. You can use a scalar function &#8220;<span class=\"STItalic\">anywhere that a scalar expression of the same data type is allowed in T-SQL statements<\/span>&#8221; (quote from Books Online). All data types in SQL Server are scalar data types, with the exception of <code>TEXT<\/code>, <code>NTEXT<\/code>, <code>ROWVERSION<\/code><a id=\"OLE_LINK14\"><\/a><a id=\"OLE_LINK13\">, and <\/a><code>IMAGE<\/code>. Unless you are working with SQL Server 2000, you should be avoiding the <code>TEXT<\/code>, <code>NTEXT<\/code>, and <code>IMAGE<\/code> data types; they are deprecated and will be removed in a future version of SQL Server.<\/p>\n<p>In addition to user-defined scalar functions, SQL Server provides numerous types of <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms174318.aspx\">built-in scalar functions<\/a>, some of which we&#8217;ll cover in more detail later. For example, there are several built-in date functions, such as <code>GETDATE<\/code>, string functions, such as <code>SUBSTRING<\/code>, and so on, all of which act on a single value and return a single value. There are also <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms173454.aspx\">aggregate functions<\/a> that perform a calculation on a set of values and return a single value, as well as a few <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms189798.aspx\">ranking functions<\/a> that produce one row for each input row.<\/p>\n<p>Table-valued functions (TVFs) return a table instead of a single value. A table valued function can be used anywhere a table can be used &#8211; typically in the <code>FROM<\/code> clause of a query. TVFs make it possible to encapsulate complex logic in a query. For example, security permissions, calculations, and business logic can be embedded in a TVF. Careful use of TVFs makes it easy to create re-usable code frameworks in the database.<\/p>\n<p>One of the important differences between scalar functions and TVFs is the way in which they can be handled internally, by the SQL Server query optimizer. <\/p>\n<p>Most developers will be used to working with compilers that will &#8220;inline&#8221; trivial function calls. In other words, in any place where the function is called, the compiler will automatically incorporate the whole body of the function into the surrounding code. The alternative is that a function is treated as interpreted code, and invoking it from the main body of code requires a jump to a different code block to execute the function. <\/p>\n<p>The biggest drawback of SQL Server functions is that they may not be automatically inlined. For a scalar function that operates on multiple rows, SQL Server will execute the function once for every row in the result set. This can have a huge performance impact, as will be demonstrated later in the article. Fortunately, with TVFs, SQL Server will call them only once, regardless of the number of rows in the result set and it&#8217;s often possible, with a bit of ingenuity, to rewrite scalar functions into TVFs, and so avoid the row-by-row processing that is inherent with scalar functions.<\/p>\n<p>In some cases, it might be necessary to dispense with the TVF altogether, and simply &#8220;manually inline&#8221; the function logic into the main code. Of course this defeats the purpose of creating a function to encapsulate re-usable logic.<\/p>\n<h3>Functions can be Deterministic or Nondeterministic<\/h3>\n<p>A deterministic function will return the same result when it is called with the same set of input parameters. Adding two numbers together is an example of a deterministic function.<\/p>\n<p>A nondeterministic function, on the other hand, may return different results every time they are called with the same set of input values. Even if the state of the data in the database is the same, the results of the function might be different. The <code>GETDATE<\/code> function, for example, &#160;is nondeterministic. One caveat of almost all nondeterministic functions is that they are executed once per statement, not once per row. If you query 90,000 rows of data and use the <code>RAND<\/code> function to attempt to produce a random value for each row you will be disappointed; SQL Server will only generate a single random number for the entire statement. The only exception to this rule is <code>NEWID<\/code>, which will generate a new <code>GUID<\/code> for every row in the statement.<\/p>\n<p>When we create a function, SQL Server will analyze the code we&#8217;ve created and evaluate whether &#160;the function is deterministic. If our function makes calls to &#160;any nondeterministic functions, it will, itself, be marked as nondeterministic. SQL Server relies on the author of a SQL CLR function to declare the function as deterministic using an attribute.<\/p>\n<p>Deterministic functions can be used in indexed views and computed columns whereas nondeterministic functions cannot.<\/p>\n<h3>Keeping things safe: functions can be schema-bound<\/h3>\n<p>Functions, just like views, can be schema bound. Attempts to alter objects that are referenced by a schema bound function will fail. What does this buy us, though? Well, just as when schema binding a view, schema binding a function makes it more difficult to make changes to the underlying data structures that would break our functions. To create a schema-bound function we simply specify schema binding as an option during function creation, as shown in Listing 1.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE FUNCTION Sales.CalculateSalesOrderTotal (@SalesOrderID INT)\nRETURNS MONEY\nWITH SCHEMABINDING AS\nBEGIN\n&#160; DECLARE @SalesOrderTotal AS MONEY ;\n&#160; SELECT &#160;@SalesOrderTotal = \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; SUM(sod.LineTotal) \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; + soh.TaxAmt \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; + soh.Freight\n&#160; FROM &#160;&#160;&#160;Sales.SalesOrderHeader AS soh\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; INNER JOIN Sales.SalesOrderDetail AS sod\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ON soh.SalesOrderID = sod.SalesOrderID\n&#160; WHERE &#160;&#160;soh.SalesOrderID = @SalesOrderId\n&#160; GROUP BY soh.TaxAmt, soh.Freight ;\n&#160; RETURN @SalesOrderTotal ;\nEND;\nGO\n<\/pre>\n<p class=\"Caption\">Listing 1: Creating a Schema Bound Function<\/p>\n<h3>Behavior around NULL<\/h3>\n<p>We can bind a function to our database schema, thereby preventing database changes &#160;breaking our function, but what do we do when our function receives <code>NULL<\/code> input values? By default, SQL Server will go ahead and run the code in the function and evaluate all of the parameters passed in, even if one of those parameters is a <code>NULL<\/code> value, and so the output of the function is <code>NULL<\/code>.<\/p>\n<p>This is a waste of processor cycles and we need to avoid this unnecessary work. We <i>could <\/i>check every parameter that is passed into a function, but that is a lot of code to maintain. If you&#8217;re thinking, &#8220;there has to be a better way&#8221; then you&#8217;re absolutely right. When we create the function we can use the <code>RETURNS NULL ON NULL INPUT<\/code> option, which will cause SQL Server to immediately return <code>NULL<\/code> if any parameters in the function are <code>NULL<\/code>-valued. Users of SQL Server 2000 and earlier are out of luck, though, as this feature was introduced in SQL Server 2005.<\/p>\n<h2>Scalar User-defined Functions <\/h2>\n<p>It&#8217;s time to take a look at some interesting uses for scalar UDFs, and along the way elucidate the rules that govern how we create and call them.<\/p>\n<h3>Calling Scalar UDFs<\/h3>\n<p>There are a few rules that must be followed when creating a function:<\/p>\n<ul>\n<li>The body of the function must be enclosed in a <code>BEGIN<\/code>\/<code>END<\/code> block.  <\/li>\n<li>Statements with side effects (insert\/update\/delete) and temporary tables may not be used. You can, however, use table variables. Table variables are allowed in UDFs because they are created as variables, not through DDL. DDL is viewed as producing a side effect and is not allowed.  <\/li>\n<li><code>TRY<\/code>\/<code>CATCH<\/code> statements are not allowed since <code>CATCH<\/code> can have the side effect of masking the error state of a given function.<\/li>\n<\/ul>\n<p>Let&#8217;s dive straight in and take a look at Listing 2, which shows the code to create, in the <code>AdventureWorks2008<\/code> database, a scalar UDF called <code>ProductCostDifference<\/code>, which will compute the cost difference for a single product, over a time range.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">IF OBJECT_ID(N'Production.ProductCostDifference', N'FN') IS NOT NULL \n&#160;&#160;&#160; DROP FUNCTION Production.ProductCostDifference ;\nGO\n&#160;\nCREATE FUNCTION Production.ProductCostDifference\n&#160;&#160;&#160; (\n&#160;&#160;&#160;&#160;&#160; @ProductId INT ,\n&#160;&#160;&#160;&#160;&#160; @StartDate DATETIME ,\n&#160;&#160;&#160;&#160;&#160; @EndDate DATETIME \n&#160;&#160;&#160; )\nRETURNS MONEY\nAS \n&#160;&#160;&#160; BEGIN\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; DECLARE @StartingCost AS MONEY ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; DECLARE @CostDifference AS MONEY ;\n&#160;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SELECT TOP 1\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; @StartingCost = pch.StandardCost\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160; Production.ProductCostHistory AS pch\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160;&#160; pch.ProductID = @ProductId\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; AND EndDate BETWEEN @StartDate\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; AND @EndDate\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ORDER BY StartDate ASC ;\n&#160;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SELECT TOP 1\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; @CostDifference = StandardCost - @StartingCost\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160; Production.ProductCostHistory AS pch\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160;&#160; pch.ProductID = @ProductId\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; AND EndDate BETWEEN @StartDate\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; AND @EndDate\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ORDER BY StartDate DESC ; \n&#160;\n&#160;&#160;&#160; &#160;&#160;&#160;&#160;RETURN&#160; @CostDifference ;\n&#160;&#160;&#160; END\n<\/pre>\n<p class=\"Caption\">Listing 2: Creating a scalar function<\/p>\n<p>Likewise, when we call a UDF, we must follow a few rules:<\/p>\n<ul>\n<li>&#160;quality the function name (e.g. <code>Production.ProductCostDifference<\/code>) when using a function in a query.  <\/li>\n<li>Optional parameters cannot be omitted, but we can use the <code>DEFAULT<\/code> keyword to supply the default value. <\/li>\n<\/ul>\n<p>Listing 3 shows a simple call to our <code>ProductCostDifference<\/code> function.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT Production.ProductCostDifference(707, '1999-01-01', GETDATE()) ;\n&#160;\n\/*\n&#160;column1&#160;&#160;&#160; \n&#160;---------- \n&#160;1.8504&#160; \n*\/\n<\/pre>\n<p class=\"Caption\">Listing 3: Executing a user-defined function<\/p>\n<p>Scalar UDFs are a fairly straightforward feature but there are some drawbacks to them, the biggest one being that, as discussed earlier, SQL Server has no optimization whereby it can compile this function as inline code. Therefore, it will simply call it once for every row to be returned in the result set. Another drawback of scalar UDFs is that we won&#8217;t see the true cost of the function when we&#8217;re looking at execution plans. This makes it difficult to gauge just how much a UDF is hurting query performance.<\/p>\n<h4>Scalar functions in the SELECT Clause<\/h4>\n<p>Listing 4 demonstrates calling our function in the <code>SELECT<\/code> statement of two simple queries, the only difference being that in the second query we filter out <code>NULL<\/code> results from our scalar function.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">--QUERY 1\nSELECT&#160; ProductID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; Name AS ProductName ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; Production.ProductCostDifference\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; (ProductID, '2000-01-01', GETDATE())\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; AS CostVariance\nFROM&#160;&#160;&#160; Production.Product ;\n&#160;\n--QUERY 2\nSELECT&#160; ProductID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; Name AS ProductName ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; Production.ProductCostDifference\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; (ProductID, '2000-01-01', GETDATE())\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;AS CostVariance\nFROM&#160;&#160;&#160; Production.Product\nWHERE&#160;&#160; Production.ProductCostDifference\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; (ProductID, '2000-01-01', GETDATE())\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;IS NOT NULL ;\n<\/pre>\n<p class=\"Caption\">Listing 4: Running a query with a scalar function<\/p>\n<p>Unfortunately, SQL Server is not terribly intelligent in the way that it works with scalar functions. In the first query, our <code>ProductCostDifference<\/code> function will be executed once for each of the 504 rows in the <code>Production.Product<\/code> table. This leads to an increase in disk access, CPU utilization, and memory utilization. The execution plan for this query is shown in Figure 1.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1388-clip_image001-630x97.jpg\" width=\"630\" height=\"97\" alt=\"1388-clip_image001-630x97.jpg\" \/><\/p>\n<p class=\"Caption\">Figure 1: Execution Plan for Query 1 in Listing 4<\/p>\n<p>We can see that all data is read from disk in the <span class=\"STBold\">Index Scan<\/span> operator before being sent to the <span class=\"STBold\">Compute Scalar<\/span> operator, where our function is applied to the data. If we open up the Properties page for the Compute Scalar node (pressing F4 will do this if you haven&#8217;t changed the default SQL Server Management Studio settings) and examine the <span class=\"STBold\">Define Values<\/span> property list. If this references the function name (rather than the column name), as it will in this case, then the function is being called once per row.<\/p>\n<p>The situation is even worse for the second query in Listing 4 in that the function needs to be evaluated twice: once for every 504 rows in the <code>Production.Product<\/code> table and once again for the 157 rows that produce a non-<code>NULL<\/code> result from our scalar function. The execution plan for this query is shown in Figure 2.<\/p>\n<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1388-clip_image002-630x92.jpg\" width=\"630\" height=\"92\" alt=\"1388-clip_image002-630x92.jpg\" \/><\/p>\n<p class=\"Caption\">Figure 2: Execution Plan for Query 2 in Listing 4<\/p>\n<p>Again, we can establish whether or not a function is being executed once per row by examining the details of this plan; in this case, the properties of either the <span class=\"STBold\">Compute Scalar<\/span> or the <span class=\"STBold\">Filter<\/span> node. The <span class=\"STBold\">Predicate<\/span> property of the <span class=\"STBold\">Filter<\/span> node shows that that the filter operation is filtering on:<\/p>\n<p class=\"Mono\"><code>[AdventureWorksCS].[Production].[ProductCostVariance]([AdventureWorksCS].[Production].[Product].[ProductID],'2000-01-01 00:00:00.000',getdate()) IS NOT NULL. <\/code><\/p>\n<p>In other words, SQL Server is evaluating the function once for every row in the product table. No function &#8216;inlining&#8217; has been performed; we would be able to see the &#8216;inlined&#8217; source code if it had been.<\/p>\n<p>This may seem like a trivial point to labor over, but it can have far reaching performance implications. Imagine that you have a plot of land. On one side of your plot of land is a box of nails. How long would it take you to do anything if you only used one nail at a time and kept returning to the box of nails every time you needed to use another one? This sort of thing might not be bad for small tasks like hanging a picture on the wall, but it would become incredibly time consuming if you were trying to build an addition for your house. The same thing is happening within your T-SQL. During query evaluation, SQL Server must evaluate the output of the scalar function once per row. This could require additional disk access and potentially slow down the query.<\/p>\n<p>Scalar functions, when used appropriately, can be incredibly effective. Just be careful to evaluate their use on datasets similar to the ones you will see in production before you make the decision to use them; they have some characteristics that may cause undesirable side effects. If your scalar UDF needs to work on many rows, one solution is to rewrite it as a table-valued function, as will be demonstrated a little later.<\/p>\n<h4>Scalar functions in the WHERE Clause<\/h4>\n<p>Using a scalar function in the <code>WHERE<\/code> clause can also have disastrous effects on performance. Although the symptoms are the same (row-by-row execution), the cause is different. Consider the call to the built-in scalar function, <code>DATEADD<\/code>, in Listing 5.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT&#160; *\nFROM&#160;&#160;&#160; Sales.SalesOrderHeader AS soh\nWHERE&#160;&#160; DATEADD(mm, 12, soh.OrderDate) &lt; GETDATE()\n<\/pre>\n<p class=\"Caption\"><a id=\"OLE_LINK11\">Listing 5: Poor use of a function in the <\/a><code>WHERE<\/code> clause<\/p>\n<p>This code will result in a full scan of the <code>Sales.SalesOrderHeader<\/code> table because SQL Server can&#8217;t use any index on the <code>OrderDate<\/code> column. Instead, SQL Server has to scan every row in the table and apply the function to each row. A better, more efficient way to write this particular query would be to move the function, as shown in Listing 6.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT&#160; *\nFROM&#160;&#160;&#160; Sales.SalesOrderHeader AS soh\nWHERE&#160;&#160; soh.OrderDate &lt; DATEADD(mm, -12, GETDATE())\n<\/pre>\n<p class=\"Caption\">Listing 6: Better use of a function in the <code>WHERE<\/code> clause<\/p>\n<p>Optimizing the use of a function in the <code>WHERE<\/code> clause isn&#8217;t always that easy, but in many occasions this problem can be alleviated through the use of careful design, a computed column, or a view.<\/p>\n<h3>Constraints and Scalar Functions<\/h3>\n<p>Functions can be used for more than just simplifying math; they are also a useful means by which to encapsulate and enforce rules within the data.<\/p>\n<h4>Default Values<\/h4>\n<p>Functions can be used to supply the default value for a column in a table. There is one requirement: no column from the table can be used as an input parameter to the default constraint.<\/p>\n<p>In Listing 7, we create two tables, <code>Bins<\/code> and <code>Products<\/code>, and a user defined function, <code>FirstUnusedProductBin<\/code>, which will find the first unused bin with the fewest products. We then use the output of the <code>FirstUnusedProductBin<\/code> function as a default value for the <code>BinID<\/code> in the <code>Products<\/code> table. Creating this default value makes it possible to have a default storage location for products, which can be overridden by application code, if necessary.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">IF OBJECT_ID(N'dbo.Products', N'U') IS NOT NULL \n&#160;&#160;&#160; DROP TABLE dbo.Products ;\nGO\n&#160;\nIF OBJECT_ID(N'dbo.FirstUnusedProductBin', N'FN') IS NOT NULL \n&#160;&#160;&#160; DROP FUNCTION dbo.FirstUnusedProductBin ;\nGO\n&#160;\nIF OBJECT_ID(N'dbo.Bins', N'U') IS NOT NULL \n&#160;&#160;&#160; DROP TABLE dbo.Bins ;\nGO\n&#160;\nCREATE TABLE dbo.Bins\n&#160;&#160;&#160; (\n&#160;&#160;&#160;&#160;&#160; BinID INT IDENTITY(1, 1)\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; PRIMARY KEY ,\n&#160;&#160;&#160;&#160;&#160; Shelf VARCHAR(2) NOT NULL ,\n&#160;&#160;&#160;&#160;&#160; Bin TINYINT NOT NULL\n&#160;&#160; &#160;) ;\n&#160;\nCREATE TABLE dbo.Products\n&#160;&#160;&#160; (\n&#160;&#160;&#160;&#160;&#160; ProductID INT IDENTITY(1, 1)\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; PRIMARY KEY ,\n&#160;&#160;&#160;&#160;&#160; ProductName VARCHAR(50) NOT NULL ,\n&#160;&#160;&#160;&#160;&#160; BinID INT REFERENCES dbo.Bins ( BinID )\n&#160;&#160;&#160; ) ;\nGO\nCREATE FUNCTION dbo.FirstUnusedProductBin ( )\nRETURNS INT\nAS \n&#160;&#160;&#160; BEGIN\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; RETURN (SELECT&#160; x.BinID\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160; (SELECT b.BinID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ROW_NUMBER() OVER \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; (ORDER BY COUNT(p.ProductID)) \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; AS rn\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160; dbo.Bins AS b\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; LEFT OUTER JOIN dbo.Products AS p \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ON b.BinID = p.BinID\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; GROUP BY b.BinID\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ) AS x\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160;&#160; rn = 1\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ) ;\n&#160;\n&#160;&#160;&#160; END\nGO\n&#160;\nALTER TABLE dbo.Products ADD CONSTRAINT DF_Products_BinID\nDEFAULT dbo.FirstUnusedProductBin() FOR BinID ;\n<\/pre>\n<p class=\"Caption\">Listing 7: Creating a table with a UDF default<\/p>\n<p>Our UDF will only work for single row <code>INSERT<\/code>s. We&#8217;ll explore what happens with a set-based <code>INSERT<\/code> after we look at the function working correctly with single row <code>INSERT<\/code>s.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">INSERT INTO dbo.Bins (Shelf, Bin)\nVALUES ('A', 1),\n&#160;&#160;&#160;&#160;&#160;&#160; ('A', 2),\n&#160;&#160;&#160;&#160;&#160;&#160; ('B', 1),\n&#160;&#160;&#160;&#160;&#160;&#160; ('B', 2),\n&#160;&#160;&#160;&#160;&#160;&#160; ('B', 3);\n&#160;\nINSERT&#160; INTO dbo.Products\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( ProductName, BinID )\nVALUES&#160; ( 'widget', DEFAULT ),\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( 'sprocket', DEFAULT ),\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( 'hammer', DEFAULT ),\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( 'flange', DEFAULT ),\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( 'gasket', DEFAULT ) ;\nGO\n&#160;\n-- every bin is full\nSELECT&#160; *\nFROM&#160;&#160;&#160; dbo.Products ;\n&#160;\nDELETE&#160; FROM dbo.Products\nWHERE&#160;&#160; ProductName = 'hammer' ;\n&#160;\n-- bin 3 is empty\nSELECT&#160; *\nFROM&#160;&#160;&#160; dbo.Products ;\n&#160;\nINSERT&#160; INTO dbo.Products\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( ProductName, BinID )\nVALUES&#160; ( 'plunger', DEFAULT );\n&#160;\n-- every bin is full again\nSELECT&#160; *\nFROM&#160;&#160;&#160; dbo.Products ;\n<\/pre>\n<p class=\"Caption\">Listing 8: Modifying data in the <code>Products<\/code> table<\/p>\n<p>When we insert data into the <code>Products<\/code> table in the first statement it&#8217;s very easy to see that every bin is filled. If we remove one product and add a different product, the empty bin will be re-used. <\/p>\n<p>Although this example demonstrates nicely the way in which we can use functions to set default values, the implementation of this function is na&#195;&#175;ve; once all bins are full it will circle around and begin adding products to the least full bin. An ideal function would use a bin-packing algorithm. If you need to use a bin-packing algorithm in T-SQL, I recommend looking at Chapter 4: Set-based iteration in <i>SQL Server MVP Deep Dives<\/i>&#160;(Kornelis 2009).<\/p>\n<p>What happens if we try to <code>INSERT<\/code> more than one row at a time?<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">INSERT&#160; INTO dbo.Products\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( ProductName\n&#160;&#160;&#160; &#160;&#160;&#160;&#160;)\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SELECT&#160; Name\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160; Production.Product ;\n&#160;\nSELECT&#160; *\nFROM&#160;&#160;&#160; dbo.Products ;\n<\/pre>\n<p class=\"Caption\">Listing 9: Inserting many rows at once<\/p>\n<p>Every row is inserted with the same default value. The <code>FirstUnusedProductBin<\/code> function is only called once for the entire transaction. A better way to enforce a default value that works for both single-row <code>INSERT<\/code>s and multi-row <code>INSERT<\/code>s is to use an <code>INSTEAD OF<\/code> trigger to bypass the set-based <code>INSERT<\/code>. In effect, we have to force SQL Server to use row-by-row behavior in order to insert a new value in each row.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">ALTER TABLE dbo.Bins DROP CONSTRAINT DF_Products_BinID ;\nGO \n&#160;\nTRUNCATE TABLE dbo.Products ;\nGO\n&#160;\nDROP TRIGGER TR_Products$Insert ;\nGO\n&#160;\nCREATE TRIGGER TR_Products$Insert ON dbo.Products\n&#160;&#160;&#160; INSTEAD OF INSERT\nAS\n&#160;&#160;&#160; BEGIN\n&#160;&#160; &#160;&#160;&#160;&#160;&#160;DECLARE @count INT ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; DECLARE @counter INT ;\n&#160;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SELECT&#160; @count = COUNT(*) ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; @counter = 0\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160; inserted ;\n&#160;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SELECT&#160; * ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; -- We use a trick with ROW_NUMBER to produce\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; -- an abitrary, ever increasing row number\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; -- that is not based on any characteristic of\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; -- the underlying data.\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ROW_NUMBER() OVER ( ORDER BY ( SELECT&#160;&#160; 1\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ) ) AS TriggerRowNumber\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; INTO&#160;&#160;&#160; #inserted\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160; inserted ;\n&#160;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHILE @counter &lt; @count \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; BEGIN\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; INSERT&#160; INTO dbo.Products\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( ProductName ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; BinID\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; )\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; SELECT&#160; ProductName ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; dbo.FirstUnusedProductBin()\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160; #inserted\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160;&#160; TriggerRowNumber = @counter + 1 ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET @counter = @counter + 1 ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; END\n&#160;&#160;&#160; END\n&#160;\n&#160;&#160;&#160; INSERT&#160; INTO dbo.Products\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( ProductName\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; )\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; SELECT&#160; Name\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160; Production.Product ;\n&#160;\n&#160;&#160;&#160; SELECT&#160; *\n&#160;&#160;&#160; FROM&#160;&#160;&#160; dbo.Products ;\n<\/pre>\n<p class=\"Caption\">Listing 10: A Multi-row solution<\/p>\n<p>To provide a constantly changing default value for each row we&#8217;ve removed the default constraint and replaced it with an <code>INSTEAD OF<\/code> trigger for the <code>INSERT<\/code>. Unfortunately, this trigger adds significant overhead, but it does demonstrate the difficulty of using functions to enforce complex default constraints.<\/p>\n<h4>Enforcing Constraints with Functions<\/h4>\n<p>Scalar UDFs are often very useful for data validation and restriction. Many constraints enforce simple, inline evaluations, such as the &#8220;number of federal income tax deductions must be less than ten&#8221;). For example, the <code>CHECK<\/code> constraint in Listing 11 enforces the rule that no employee&#8217;s yearly bonus is more than 25% of their salary (one could argue that this sort of salary logic belongs in the application not database, but that debate is not really relevant to our goal here).<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE TABLE dbo.Salaries\n&#160;&#160;&#160; (\n&#160;&#160;&#160;&#160;&#160; EmployeeID INT NOT NULL ,\n&#160;&#160;&#160;&#160;&#160; BaseSalary MONEY NOT NULL ,\n&#160;&#160;&#160;&#160;&#160; Bonus MONEY NULL\n&#160;&#160;&#160; ) ;\n&#160;\nALTER TABLE dbo.Salaries\nADD CONSTRAINT CheckMaxBonus \nCHECK ((COALESCE(Bonus, 0) * 4) &lt;= BaseSalary) ;\n<\/pre>\n<p class=\"Caption\">Listing 11: A simple <code>CHECK<\/code> constraint<\/p>\n<p>Say, though that we have second rule for this data says that &#8220;no employee may have a salary that is 10 times greater than the salary of the lowest paid employee&#8221;. This is a bit trickier because if we try to add a subquery to the <code>CHECK<\/code> constraint, we receive an error that &#8220;<span class=\"STItalic\">Subqueries are not allowed in this context. Only scalar expressions are allowed.<\/span>&#8221; SQL server is, wisely, preventing us from comparing the output of a query with a single value. Since we cannot put this comparison inline, we&#8217;ll have to create a scalar UDF and use the function to compare the data.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE FUNCTION dbo.SalaryWithinBounds ( @Salary MONEY )\nRETURNS BIT\nAS \n&#160;&#160;&#160; BEGIN\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; DECLARE @r_val AS BIT ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; DECLARE @MinSalary AS MONEY ;\n&#160;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SELECT&#160; @MinSalary = MIN(BaseSalary)\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160; dbo.Salaries\n&#160;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; IF ( @MinSalary * 10 ) &gt; @Salary \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET @r_val = 1\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ELSE \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET @r_val = 0\n&#160;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; RETURN @r_val ;\n&#160;&#160;&#160; END \nGO\n&#160;\nALTER TABLE dbo.Salaries\nADD CONSTRAINT CheckMaxSalary\n&#160;&#160;&#160;&#160; CHECK (dbo.SalaryWithinBounds(BaseSalary) = 1) ;\nGO\n&#160;\n\/* This insert succeeds *\/\nINSERT&#160; INTO dbo.Salaries\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( EmployeeID, BaseSalary, Bonus )\nVALUES&#160; ( 5, 1000, 0 ) ;\n\/* This insert will fail *\/\nINSERT&#160; INTO dbo.Salaries\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( EmployeeID, BaseSalary, Bonus )\nVALUES&#160; ( 6, 100000000, 50000 ) ;\n<\/pre>\n<p class=\"Caption\">Listing 12: Creating a constraint with a function<\/p>\n<p>Functions in constraints are not limited to the current table; they can reference any table in the database to enforce data constraints. In the following example we will create two tables &#8211; <code>Employees<\/code> and <code>PayGrades<\/code> &#8211; and implement a <code>CHECK<\/code> constraint that prevents an employee from having the same or higher pay grade as their manager.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">IF OBJECT_ID(N'Employees', N'U') IS NOT NULL \n&#160;&#160;&#160; DROP TABLE dbo.Employees ;\nGO\n&#160;\nIF OBJECT_ID(N'PayGrades', N'U') IS NOT NULL \n&#160;&#160;&#160; DROP TABLE dbo.PayGrades ;\nGO\n&#160;\nIF OBJECT_ID(N'dbo.VerifyPayGrade', N'FN') IS NOT NULL \n&#160;&#160;&#160; DROP FUNCTION dbo.VerifyPayGrade ;\nGO\n&#160;\nCREATE TABLE dbo.PayGrades\n&#160;&#160;&#160; (\n&#160;&#160;&#160;&#160;&#160; PayGradeCode CHAR(1) NOT NULL\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; PRIMARY KEY ,\n&#160;&#160;&#160;&#160;&#160; Position TINYINT NOT NULL\n&#160;&#160;&#160; ) ;\nGO\n&#160;\nCREATE TABLE dbo.Employees\n&#160;&#160;&#160; (\n&#160;&#160;&#160;&#160;&#160; EmployeeID INT NOT NULL\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; IDENTITY(1, 1)\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; PRIMARY KEY ,\n&#160;&#160;&#160;&#160;&#160; ManagerID INT NULL\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;&#160;&#160;&#160;&#160;&#160;&#160;REFERENCES dbo.Employees ( EmployeeID ) ,\n&#160;&#160;&#160;&#160;&#160; PayGradeCode CHAR(1) NOT NULL\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; REFERENCES dbo.PayGrades ( PayGradeCode ) ,\n&#160;&#160;&#160;&#160;&#160; FirstName VARCHAR(30) NOT NULL ,\n&#160;&#160;&#160;&#160;&#160; LastName VARCHAR(30) NOT NULL\n&#160;&#160;&#160; ) ;\nGO\n&#160;\nCREATE FUNCTION dbo.VerifyPayGrade\n&#160;&#160;&#160; (\n&#160;&#160;&#160;&#160;&#160; @ManagerID INT ,\n&#160;&#160;&#160;&#160;&#160; @PayGrade CHAR(1)\n&#160;&#160;&#160; )\nRETURNS BIT\n&#160;&#160;&#160; BEGIN\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; DECLARE @r AS BIT ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; @ManagerPayGradePosition AS TINYINT ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; @PayGradePosition AS TINYINT ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; \n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET @r = 0 ;\n&#160; &#160;&#160;&#160;&#160;&#160;&#160;SELECT&#160; @ManagerPayGradePosition = Position\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160; dbo.Employees AS e\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; INNER JOIN dbo.PayGrades AS pg\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ON e.PayGradeCode = pg.PayGradeCode\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160;&#160; e.EmployeeID = @ManagerID ;\n&#160;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SELECT&#160; @PayGradePosition = Position\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160; dbo.PayGrades AS pg\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160;&#160; pg.PayGradeCode = @PayGrade ;\n&#160;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET @r = CASE WHEN @PayGradePosition &lt; \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; COALESCE(@ManagerPayGradePosition,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;&#160;&#160;&#160;999) THEN 1\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ELSE 0\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; END ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; RETURN @r ;\n&#160;&#160;&#160; END\nGO\n&#160;\nALTER TABLE dbo.Employees\nWITH CHECK\nADD\nCONSTRAINT CK_Employees_PayGradePosition\nCHECK (dbo.VerifyPayGrade(ManagerID, PayGradeCode) = 1) ;\nGO\n&#160;\nINSERT&#160; INTO dbo.PayGrades\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( PayGradeCode, Position )\nVALUES&#160; ( 'A', 10 ),\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( 'B', 9 ),\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( 'C', 8 ),\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( 'D', 7 ),\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( 'E', 6 ),\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( 'F', 5 ),\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( 'G', 4 ),\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( 'H', 3 ),\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( 'I', 2 ),\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( 'J', 1 ) ;\n<\/pre>\n<p class=\"Caption\">Listing 13: A constraint using functions that access other tables<\/p>\n<p>Having now created our tables and a function to validate the business rule, we can set about testing that our rules actually work.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">-- These inserts will succeed\nINSERT&#160; INTO dbo.Employees\n&#160;&#160;&#160;&#160;&#160; &#160;&#160;( ManagerID, PayGradeCode, FirstName, LastName )\nVALUES&#160; ( NULL, 'A', 'Kim', 'Abercrombie' ),\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( 1, 'B', 'Theodore', 'Stevens' ) ;\nGO\n&#160;\n-- This insert will fail\nINSERT&#160; INTO dbo.Employees\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( ManagerID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; PayGradeCode ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; FirstName ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; LastName\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; )\nVALUES&#160; ( 2 ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; 'B' ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; 'James' ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; 'Nguyen'\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ) ;\n<\/pre>\n<p class=\"Caption\">Listing 14: Verifying our constraints by creating data<\/p>\n<p>The first two inserted rows create the head of the company at the top pay grade and then we create an immediate subordinate. The third <code>INSERT<\/code> fails because we&#8217;re attempting to create an employee at the same pay grade as their manager. When we try to insert a row that violates the check constraint, an error is returned to the client. We could parse this error message and return a meaningful message to the client instead of this:<\/p>\n<pre>The INSERT statement conflicted with the CHECK constraint \"CK_Employees_PayGradePosition\". The conflict occurred in database \"AdventureWorks2008\", table \"dbo.Employees\".\n<\/pre>\n<h2>Table-valued Functions<\/h2>\n<p>Table-valued Functions (TVFs) differ from scalar functions in that TVFs return an entire table whereas scalar functions only return a single value. This makes them ideal for encapsulating more complex logic or functionality for easy re-use. TVFs have the additional advantage of being executed once to return a large number of rows (as opposed to scalar functions which must be executed many times to return many values). <\/p>\n<p>The body of a TVF can either contain just a single statement or multiple statements, but the two cases are handled very differently by the optimizer. If the function body contains just a single statement (often referred to as an &#8220;inline TVF&#8221;), then the optimizer treats it in a similar fashion to a view in that it will &#8220;decompose&#8221; it and simply reference the underlying objects (there will be no reference to the function in the resulting execution plan). <\/p>\n<p>However, by contrast, multi-statement TVFs present an optimization problem for SQL Server; it doesn&#8217;t know what to do with them. It treats them rather like a table for which it has no available statistics &#8211; the optimizer assumes that it the TVF will always return one row. As a result, even a very simple multi-statement TVF can cause severe performance problems.<\/p>\n<h3>Avoiding Row-by-Row Behavior with TVFs<\/h3>\n<p>One of the problems with scalar functions is that they are executed once for every row in the result set. While this is not a problem for small result sets, it becomes a problem when our queries return a large number of rows. We can use TVFs to solve this problem.<\/p>\n<p>We&#8217;ll start with a relatively simple case of converting a single-statement scalar function into a single-statement TVF. We&#8217;ll then move on to the slightly more complex case of converting our previous <code>ProductCostDifference<\/code> scalar function (Listing 2), which contains multiple statements. As we discussed earlier, this function can only operate on a single row at a time. If we wanted to execute that function over several million rows, we would see a considerable spike in disk I\/O and a decrease in performance.<\/p>\n<h4>Single-statement Scalar to Single-statement TVF<\/h4>\n<p>Let&#8217;s take a look at a new example, looking at order data from the <code>AdventureWorks<\/code> database. We are specifically interested in the average weight of orders so we can determine if we need to look into different shipping options.<\/p>\n<p>Listing 15 creates two functions; the first is a scalar function and will compute the order weight for any single order. This is ideal for queries showing the details of a single order or a few orders, but when it comes to working with a large number of orders this could cause an incredible amount of disk I\/O. The second function is a single-statement table-valued function that performs the exact same calculation, but does so over an entire table instead of for a single row.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">IF OBJECT_ID(N'Sales.OrderWeight') IS NOT NULL \n&#160;&#160;&#160; DROP FUNCTION Sales.OrderWeight ;\nGO\n&#160;\nIF OBJECT_ID(N'Sales.tvf_OrderWeight') IS NOT NULL \n&#160;&#160;&#160; DROP FUNCTION Sales.tvf_OrderWeight ;\nGO\n&#160;\nCREATE FUNCTION Sales.OrderWeight ( @SalesOrderID INT )\nRETURNS DECIMAL(18, 2)\nAS \n&#160;&#160;&#160; BEGIN\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; DECLARE @Weight AS DECIMAL(18, 2) ;\n&#160;&#160;&#160;&#160;&#160; \n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SELECT&#160; @Weight = SUM(sod.OrderQty * p.Weight)\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160; Sales.SalesOrderDetail AS sod\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; INNER JOIN Production.Product AS p\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ON sod.ProductID = p.ProductID\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160;&#160; sod.SalesOrderID = @SalesOrderID ;\n&#160;&#160;&#160;&#160;&#160; \n&#160;&#160;&#160;&#160;&#160;&#160;&#160; RETURN @Weight ;\n&#160;&#160;&#160; END\nGO\n&#160;\nCREATE FUNCTION Sales.tvf_OrderWeight ( )\nRETURNS TABLE\nAS\nRETURN\n&#160;&#160;&#160; SELECT&#160; sod.SalesOrderID ,\n&#160;&#160;&#160;&#160;&#160;&#160; &#160;&#160;&#160;&#160;&#160;SUM(sod.OrderQty * p.Weight) AS OrderWeight\n&#160;&#160;&#160; FROM&#160;&#160;&#160; Sales.SalesOrderDetail AS sod\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; INNER JOIN Production.Product AS p\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ON sod.ProductID = p.ProductID\n&#160;&#160;&#160; GROUP BY sod.SalesOrderID ;\nGO\n<\/pre>\n<p class=\"Caption\">Listing 15: Two functions for Finding Order Weight<\/p>\n<p>Listing 16 shows the queries for calling each of these functions.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">--calling the scalar function\nSELECT&#160; c.CustomerID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; AVG(OrderWeight) AS AverageOrderWeight\nFROM&#160;&#160;&#160; Sales.Customer AS c\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; INNER JOIN ( SELECT soh.CustomerID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; Sales.OrderWeight(soh.SalesOrderID)\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;AS OrderWeight\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160; Sales.SalesOrderHeader AS soh\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160; soh.OrderDate BETWEEN '2000-01-01'\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;AND GETDATE()\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ) AS x ON c.CustomerID = x.CustomerID\nGROUP BY c.CustomerID\nORDER BY c.CustomerID ;\n&#160;\n-- calling the single-statement TVF\nSELECT&#160; c.CustomerID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; AVG(OrderWeight) AS AverageOrderWeight\nFROM&#160;&#160; &#160;Sales.Customer AS c\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; INNER JOIN Sales.SalesOrderHeader AS soh\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ON c.CustomerID = soh.CustomerID\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; INNER JOIN Sales.tvf_OrderWeight() AS y\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ON soh.SalesOrderID = y.SalesOrderID\nGROUP BY c.CustomerID\nORDER BY c.CustomerID ;\n<\/pre>\n<p class=\"Caption\">Listing 16: Using the <code>OrderWeight<\/code> Functions<\/p>\n<p>Notice the different ways in which the two functions are invoked. We use our single-statement TVF the same way that we would use a table. This makes it very easy to use TVFs in our queries; we only need to join to them and their results will be incorporated into our existing query.<\/p>\n<p>When you hit the <span class=\"STBold\">Execute<\/span> button for those queries, it should be immediately clear that one of them, at least, is pretty slow. However, by examining the execution plans alone, as shown in Figure 6, it&#8217;s hard to tell which one is the culprit. In fact, in terms of relative plan cost, you may even conclude that the first plan is less expensive.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1388-clip_image003-630x242.jpg\" width=\"630\" height=\"242\" alt=\"1388-clip_image003-630x242.jpg\" \/><\/p>\n<p class=\"Caption\">Figure 3: Execution plans for calling the scar function and the TVF<\/p>\n<p>Unfortunately, the top execution plan (for the scalar function), hides any immediately-obvious evidence of the <code>Sales.OrderWeight<\/code> function reading row-by-row through the <code>Sales.SalesOrderDetail<\/code> table. Our evidence for that comes, again, from the <span class=\"STBold\">Compute Scalar<\/span> operator, where we see direct reference to our <code>Sales.OrderWeight<\/code> function, indicating that it is being called once per row.<\/p>\n<p>In the second execution plan, for the TVF, we see the index scan against the <code>Sales.SalesOrderDetail<\/code> table. Our TVF is called just once to return the required rows, and has effectively been &#8220;inlined&#8221;; the plan references only the underlying objects with no reference to the function itself.<\/p>\n<p>Using <code>SET<\/code> <code>STATISTICS<\/code> <code>TIME<\/code> <code>ON<\/code> revealed that (one my machine) the first query executes in 28 seconds while the second query executes in 0.6 seconds. Clearly the second query is faster than the first.<\/p>\n<p>Later in the article, we&#8217;ll take this a step further and show how to dispense with the TVF altogether and manually inline the logic of this TVF; a strategy that&#8217;s sometimes advantageous from a performance perspective.<\/p>\n<h4>Multi-statement Scalar to Multi-statement TVF<\/h4>\n<p>Let&#8217;s now look at the more complex case of converting our multi-statement <code>ProductCostDifference<\/code> scalar function into a TVF. We&#8217;re going to start with a straight conversion to a multi-statement TVF. <\/p>\n<p>As noted, a multi-statement TVF is one that contains more than one statement in the function body. Listing 17 shows a nearly-direct translation of the original scalar function, in Listing 2, into a multi-statement table-valued function.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">IF OBJECT_ID(N'Production.ms_tvf_ProductCostDifference',N'TF' ) IS NOT NULL \n&#160;\n--SELECT * FROM sys.objects WHERE name LIKE 'm%'\n&#160;&#160;&#160; DROP FUNCTION Production.ms_tvf_ProductCostDifference ;\nGO\n&#160;\nCREATE FUNCTION Production.ms_tvf_ProductCostDifference\n&#160;&#160;&#160; (\n&#160;&#160;&#160;&#160;&#160; @StartDate DATETIME ,\n&#160;&#160;&#160;&#160;&#160; @EndDate DATETIME&#160; \n&#160;&#160;&#160; )\nRETURNS @retCostDifference TABLE\n&#160;&#160;&#160; (\n&#160;&#160;&#160;&#160;&#160; ProductId INT ,\n&#160;&#160;&#160;&#160;&#160; CostDifference MONEY\n&#160;&#160;&#160; )\nAS \n&#160;&#160;&#160; BEGIN\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; DECLARE @workTable TABLE\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; (\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ProductId INT ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; StartingCost MONEY ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; EndingCost MONEY\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ) ;\n&#160;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; INSERT&#160; INTO @retCostDifference\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( ProductId ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; CostDifference\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; )\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; SELECT&#160; ProductID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;StandardCost\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160; ( SELECT&#160;&#160;&#160; pch.ProductID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; pch.StandardCost ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ROW_NUMBER() OVER\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( PARTITION BY ProductID\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;ORDER BY StartDate DESC ) AS rn\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160;&#160;&#160; Production.ProductCostHistory AS pch\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160;&#160;&#160;&#160; EndDate BETWEEN\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; @StartDate AND @EndDate\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ) AS x\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160;&#160; x.rn = 1 ;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; \n&#160;&#160;&#160;&#160;&#160;&#160;&#160; UPDATE&#160; @retCostDifference\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; SET&#160;&#160;&#160;&#160; CostDifference = CostDifference - StandardCost\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160; @retCostDifference cd\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; JOIN ( SELECT&#160;&#160; ProductID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; StandardCost\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160;&#160; ( SELECT&#160;&#160;&#160; pch.ProductID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; pch.StandardCost ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ROW_NUMBER() OVER\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;( PARTITION BY ProductID\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ORDER BY StartDate ASC )\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; AS rn\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160;&#160;&#160; Production.ProductCostHistory\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;AS pch\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160;&#160;&#160;&#160; EndDate BETWEEN \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; @StartDate AND @EndDate\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ) AS x\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160;&#160;&#160; x.rn = 1\n&#160; &#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;) AS y ON cd.ProductId = y.ProductID ;\n&#160;\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; RETURN ;\n&#160;&#160;&#160; END\nGo\n&#160;\n<\/pre>\n<p class=\"Caption\">Listing 17: A multi-statement TVF<\/p>\n<p>This TVF, Instead of retrieving a single row from the database and calculating the price difference, pulls back all rows from the database and calculates the price difference for all rows at once.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT&#160; p.ProductID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; p.Name ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; p.ProductNumber ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; pcd.CostDifference\nFROM&#160;&#160;&#160; Production.Product AS p\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; INNER JOIN Production.ms_tvf_ProductCostDifference\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;('2001-01-01', GETDATE()) AS pcd\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ON p.ProductID = pcd.ProductID ;\n<\/pre>\n<p class=\"Caption\">Listing 18: Using the TVF<\/p>\n<p>The downside of this multi-statement TVF is that SQL Server makes the assumption that only one row will be returned from the TVF, as we can see from the execution plan in Figure 4. With the data volumes we see in <code>AdventureWorks<\/code>, this doesn&#8217;t pose many problems. On a larger production database, though, this would be especially problematic.<\/p>\n<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1388-clip_image004.jpg\" width=\"487\" height=\"357\" alt=\"1388-clip_image004.jpg\" \/><\/p>\n<p class=\"Caption\">Figure 4: Only one row<\/p>\n<h4>Multi-statement Scalar to Single-statement TVF<\/h4>\n<p>Let&#8217;s now rewrite our original <code>ProductCostDifference<\/code> scalar function a second time, this time turning it into a single-statement (or &#8220;inline&#8221;) TVF, as shown in Listing 19.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">IF OBJECT_ID(N'Production.tvf_ProductCostDifference') IS NOT NULL \n&#160;&#160;&#160; DROP FUNCTION Production.tvf_ProductCostDifference ;\nGO\n&#160;\nCREATE FUNCTION [Production].[tvf_ProductCostDifference]\n&#160;&#160;&#160; (\n&#160;&#160;&#160;&#160;&#160; @StartDate DATETIME ,\n&#160;&#160;&#160;&#160;&#160; @EndDate DATETIME\n&#160;&#160;&#160; )\nRETURNS TABLE\nAS\nRETURN\n&#160;&#160;&#160; WITH&#160;&#160;&#160; cte\n&#160;&#160;&#160; &#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;AS ( SELECT&#160;&#160; pch.ProductID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; pch.StandardCost AS Cost ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ROW_NUMBER() OVER ( PARTITION BY pch.ProductID ORDER BY StartDate ASC ) AS rn_1 ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ROW_NUMBER() OVER ( PARTITION BY pch.ProductID ORDER BY StartDate DESC ) AS rn_2\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160;&#160; Production.ProductCostHistory AS pch\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160;&#160;&#160; pch.EndDate BETWEEN @StartDate AND @EndDate\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; )\n&#160; -- Find the newest price for each product by using\n&#160; -- grabbing the first (x.rn_1 = 1) row.\n&#160; -- Then find the last row for each product with&#160; \n&#160; -- x.rn_1 = y.rn_2. Since rn_2 is ordered by StartDate\n&#160; -- descending, the row in y where rn_2 = 1 is the \n&#160; -- oldest order.\n&#160; SELECT&#160;&#160;&#160; x.ProductID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; y.Cost - x.Cost AS CostDifference\n&#160; FROM&#160;&#160;&#160;&#160;&#160; cte AS x\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; INNER JOIN cte AS y ON x.ProductID = y.ProductID\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; AND x.rn_1 = y.rn_2\n&#160; WHERE&#160;&#160;&#160;&#160; x.rn_1 = 1 ;\n<\/pre>\n<p class=\"Caption\">Listing 19: Moving to a single-statement table-valued function<\/p>\n<p>In addition to switching to a table valued function, we also re-wrote the code to read the table fewer times (two times, in this case; once for each use of the CTE in Listing 19) by using two different <code>ROW_NUMBER<\/code>s. Use of a common table expression also makes it easier and faster to get the oldest and newest row at the same time. <\/p>\n<p>While this initially seems like a lot of complexity to get to our original goal, it all has a purpose. By re-writing the TVF to use a common table expression, we can avoid the performance problem of a multi-statement TVF.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">--QUERY 1\nSELECT&#160; p.ProductID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; p.Name ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; p.ProductNumber ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; pcd.CostDifference\nFROM&#160;&#160;&#160; Production.Product AS p\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; INNER JOIN\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; Production.tvf_ProductCostDifference('2001-01-01',\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; GETDATE()) AS pcd\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ON p.ProductID = pcd.ProductID ;\n&#160;\n--QUERY 2\nSELECT&#160; p.ProductID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; p.Name ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; p.ProductNumber ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; pcd.CostDifference\nFROM&#160;&#160;&#160; Production.Product AS p\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; INNER JOIN\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; Production.tvf_ProductCostDifference('2001-01-01',\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; GETDATE()) AS pcd\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ON p.ProductID = pcd.ProductID\nWHERE&#160;&#160; p.Name LIKE 'A%' ;\n<\/pre>\n<p class=\"Caption\">Listing 20: Two uses of the single-statement TVF<\/p>\n<p>The execution plan for the first query in Listing 20 is shown in Figure 5.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1388-clip_image005-630x154.jpg\" width=\"630\" height=\"154\" alt=\"1388-clip_image005-630x154.jpg\" \/><\/p>\n<p class=\"Caption\">Figure 5: Execution Plan for Query 1 in Listing 20<\/p>\n<p>We can see that the execution plan of the body of <code>Production.tvf_ProductCostDifference<\/code> has been &#8220;inlined&#8221;. If the query had not been inlined, we would have just seen a single operator for executing the table valued function. Note that there are two scans on <code>ProductCostHistory<\/code>, because we call the CTE twice in the function, producing two reads of the underlying query).<\/p>\n<p>Again, we can confirm that this TVF is not executed once per row by examining, for example, the <span class=\"STBold\">Compute Scalar<\/span> operator, which contains no reference to our function, as well as the the <span class=\"STBold\">Hash Match<\/span> operator. If SQL Server had not inlined the function, we might have seen a nested loop join to the TVF operator.Looking at the properties of the <span class=\"STBold\">Hash Match<\/span> node, in Figure 6, we can see that SQL Server not only expects to perform the join to the body of the TVF just once, but it does perform that join only once. SQL Server has successfully inlined the TVF and it is called once for the entire result set, not once for every row.<\/p>\n<p class=\"illustration\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1388-img29-280x387.jpg\" width=\"280\" height=\"387\" alt=\"1388-img29-280x387.jpg\" \/><\/p>\n<p class=\"Caption\">Figure 6: Properties of the Hash Match node<\/p>\n<p>At this point, we&#8217;ve removed the problem we had with scalar functions, where they were executed row-by-row. We&#8217;ve also avoided the problems inherent with multi-statement TVFs. However, we <span class=\"STItalic\">still<\/span> may encounter performance problems with these &#8220;inline&#8221; TVFs. The reason for this is that the TVF may be evaluated in full before being joined to our query. This means that we may see several hundred rows returned from the TVF when our query only returns a few rows. In Listing 20, we have one query that returns 157 rows and a second query that returns only a single row. However, turning on <code>STATISTICS IO<\/code> reveals that both queries perform the same amount of physical and logical IO, showing that we&#8217;re doing the same amount of work whether we return 157 rows or 1 row. In both cases, we perform two scans of <code>ProductCostHistory<\/code> at a cost of 10 logical reads.<\/p>\n<pre>(157 row(s) affected)\nTable 'Product'. Scan count 0, logical reads 314, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'ProductCostHistory'. Scan count 2, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\n&#160;\n(1 row(s) affected)\n&#160;\n(1 row(s) affected)\nTable 'Product'. Scan count 0, logical reads 314, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'ProductCostHistory'. Scan count 2, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\n&#160;\n(1 row(s) affected)\n<\/pre>\n<p>When you are building your TVFs and the queries that use them, it&#8217;s important to remember that the join path may not always be what you expect. You may come to a point when you have been optimizing your queries and the performance bottleneck comes down to a single table-valued function returning a lot of data that is subsequently filtered down to a few rows.&#160; What should you do then?<\/p>\n<h3>Rewriting TVFs with CROSS APPLY<\/h3>\n<p>In cases where you have a substantial amount of data that you need to restrict through joins and where conditions, it&#8217;s usually better from a performance perspective to dispense with the TVF altogether and simply &#8220;manually inline&#8221; the function logic; in other words, put the body of our T-SQL code inline with the calling code.<\/p>\n<p>Inlining a TVF is as simple as pasting the body of the function into our query. This can have several benefits. Firstly, it makes it much easier to determine if our changes are improving performance. One of the problems of using functions is that multi-statement TVFs do not report their actual disk I\/O to <code>STATISTICS IO<\/code>, but they do when you use the SQL Server Profiler. Single statement TVFs and inlined code will correctly report the disk I\/O because it&#8217;s just another part of the query.<\/p>\n<p>Inlining TVF code also makes it easier to create one-off changes for a single query; rather than create a new function, we can just change the code. When I start inlining code in production I add a comment in the stored procedure that pointed back to the original function. This makes it easier to incorporate any improvements that I may find in the future. Finally, by moving our function to inline code it&#8217;s much more likely that SQL Server will make effective join and table scanning choices and only retrieve the rows that are needed. As discussed, with TVFs, SQL Server might execute the query in our function first and return those rows to the outer query before applying any filtering. If SQL Server returns 200,000 rows and only needs 100, that&#8217;s a considerable waste of processing time and disk I\/O. However, if SQL Server can immediately determine the number of rows that will be needed and which rows will be needed, it will make much more effective querying choices, including which indexes to use, the type of join to consider, and the whether or not to perform a parallel operation.<\/p>\n<p>Bear in mind, however, that SQL Server is in general, very efficient at inlining single-statement TVFs. Listing 21 shows how to manually inline our <code>Sales.tvf_OrderWeight<\/code> TVF (from Listing 15), by using <code>CROSS APPLY<\/code>. The <code>CROSS APPLY<\/code> operator effectively tells SQL Server to invoke a table-valued function for each row returned by an outer query.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT&#160; c.CustomerID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; AVG(OrderWeight) AS AverageOrderWeight\nFROM&#160;&#160;&#160; Sales.Customer AS c\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; INNER JOIN Sales.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID\nCROSS APPLY ( SELECT&#160; SUM(sod.OrderQty * p.Weight) AS OrderWeight\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160; Sales.SalesOrderDetail AS sod\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; INNER JOIN Production.Product AS p ON sod.ProductID = p.ProductID\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160;&#160; sod.SalesOrderID = soh.SalesOrderID\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; GROUP BY sod.SalesOrderID ) AS y\nGROUP BY c.CustomerID \nORDER BY c.CustomerID ;\n<\/pre>\n<p class=\"Caption\">Listing 21: Using <code>CROSS APPLY<\/code> to inline the <code>OrderWeight<\/code> TVF<\/p>\n<p>However, we&#8217;ll see no difference in the execution plan compared to the one we saw from calling the TVF (Figure 3). One of the benefits of using single statement TVFs is that SQL Server is frequently able to optimize the queries and inline the TVF for us. By knowing how to write optimal TVFs we can build reusable code and take advantage of SQL Server&#8217;s ability to automatically inline well-constructed TVFs.<\/p>\n<p>We can also manually inline the logic of our multi-statemnt TVFs. Listing 22 shows how to do this for our <code>tvf_ProductCostDifference<\/code> function. However, again, we don&#8217;t get additional benefit in this particular case. It turns out that attempting to inline the common table expression has similar I\/O implications to calling the function. Regardless of whether we leave the TVF as-is or inline the function logic using just the CTE, SQL Server will have to enter the CTE and evaluate the CTE query twice, once for each join to the CTE, and we&#8217;ll see the same amount of physical I\/O in each case.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SET STATISTICS IO ON ;\n&#160;\nDECLARE @StartDate AS DATETIME ;\nDECLARE @EndDate AS DATETIME ;\n&#160;\nSET @StartDate = '2001-01-01' ;\nSET @EndDate = GETDATE() ;\n&#160;\n-- calling the TVF\nSELECT&#160; p.ProductID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; p.Name ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; p.ProductNumber ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; pcd.CostDifference\nFROM&#160;&#160;&#160; Production.Product AS p\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; INNER JOIN Production.tvf_ProductCostDifference\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;(@StartDate, @EndDate)\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; AS pcd ON p.ProductID = pcd.ProductID\nWHERE&#160;&#160; p.Name LIKE 'A%' ;\n&#160;\n-- manually inlining the TVF logic\nWITH&#160;&#160;&#160; cte\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; AS ( SELECT&#160;&#160; pch.ProductID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; pch.StandardCost AS Cost ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;&#160;&#160;&#160;ROW_NUMBER() OVER \n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( PARTITION BY pch.ProductID\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ORDER BY StartDate ASC ) AS rn_1 ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ROW_NUMBER() OVER\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ( PARTITION BY pch.ProductID\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;&#160;&#160;&#160;&#160;&#160;ORDER BY StartDate DESC ) AS rn_2\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160;&#160; Production.ProductCostHistory AS pch\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHERE&#160;&#160;&#160; pch.EndDate BETWEEN @StartDate AND @EndDate\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; )\n&#160;&#160;&#160; SELECT&#160; x.ProductID ,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; y.Cost - x.Cost AS CostDifference\n&#160;&#160;&#160; FROM&#160;&#160;&#160; Production.Product AS p\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; INNER JOIN cte AS x ON p.ProductID = x.ProductID\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; INNER JOIN cte AS y ON x.ProductID = y.ProductID\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; AND x.rn_1 = y.rn_2\n&#160;&#160;&#160; WHERE&#160;&#160; x.rn_1 = 1\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; AND p.Name LIKE 'A%'\n<\/pre>\n<p class=\"Caption\">Listing 22: Re-writing a TVF with an inline function body&#8217;<\/p>\n<p>If you run Listing 22, you&#8217;ll find that the execution plan for the manually-inlined code is very similar (but not identical) to the plan for calling the TVF (Figure 5) and performs similar I\/O.<\/p>\n<h3>Best Practices for TVFs<\/h3>\n<p>Table-valued functions are best used when you will be performing operations on a large number of rows at once. Typically this will be something that could be accomplished through a complex subquery or functionality that you will re-use multiple times in your database. TVFs should be used when you can always work with the same set of parameters &#8211; dynamic SQL is not allowed within functions in SQL Server.<\/p>\n<p>It&#8217;s best to use TVFs when you only have a small dataset that could be used in the TVF. Once you start getting into larger numbers of rows, TVFs can become very slow since all the results of the TVF query are evaluated before being filtered by the outer query. When this starts to happen it is best to inline the code. If inlining the TVF code doesn&#8217;t work, you can even look into re-writing the query slightly to use a <code>JOIN<\/code> instead of a <code>CROSS APPLY<\/code>. This might complicate the query, but it can lead to dramatic performance improvements.<\/p>\n<h2>A few useful Built-in Functions<\/h2>\n<p>To finish off this article, we&#8217;ll briefly review some of the built-in scalar functions that I&#8217;ve frequently found useful.<\/p>\n<h3>COALESCE<\/h3>\n<p><code>COALESCE<\/code> takes an unlimited list of arguments and returns the first non-<code>NULL<\/code> expression. One of the advantages of <code>COALESCE<\/code> is that it can be used to replace lengthy <code>CASE<\/code> statements.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT&#160; CASE WHEN col_1 IS NOT NULL THEN col_1\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHEN col_2 IS NOT NULL THEN col_2\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; WHEN col_3 IS NOT NULL THEN col_3\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; END AS result\n<\/pre>\n<p class=\"Caption\">Listing 23: A <code>CASE<\/code> statement<\/p>\n<p>The code in Listing 23 can be simplified by using <code>COALESCE<\/code>.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT&#160; COALESCE(col_1, col_2, col_3) AS result\n<\/pre>\n<p class=\"Caption\">Listing 24: <code>COALESCE<\/code><\/p>\n<h3>DATEADD and DATEDIFF<\/h3>\n<p>The best way to modify date and time values is by using the <code>DATEADD<\/code> and <code>DATEDIFF<\/code> functions. The <code>DATEADD<\/code> function can be used to add or subtract an interval to part of a date.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT&#160; DATEADD(hh, 5, GETDATE()) ;\nSELECT&#160; DATEADD(hh, -5, GETDATE()) ;\nSELECT&#160; DATEADD(dd, 1, GETDATE()) ;\n<\/pre>\n<p class=\"Caption\">Listing 25: Modifying dates<\/p>\n<p>The <code>DATEDIFF<\/code> function can be used to calculate the difference between to dates. <code>DATEDIFF<\/code> is similar to <code>DATEADD<\/code> &#8211; <code>DATEDIFF<\/code> gets the difference between two dates using the given time interval (year, months, seconds, and so on).<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT&#160; DATEDIFF(dd, '2010-02-05', GETDATE()) ;\nSELECT&#160; DATEDIFF(hh, '2010-01-04 09:37:00', '2010-03-02 17:54:25') ;\n<\/pre>\n<p class=\"Caption\">Listing 26: Finding the difference between two points in time<\/p>\n<p>One practical use of the <code>DATEDIFF<\/code> function is to find the beginning of the current day or month.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT&#160; DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS beginning_of_day ;\nSELECT &#160;DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &#160;AS beginning_of_month ;\n<\/pre>\n<p class=\"Caption\">Listing 27: Beginning of the day or month<\/p>\n<p>This approach to getting the beginning of the day computes the number of days since the dawn of SQL Server time (January 1, 1900). We then add that number of days to the dawn of SQL Server time and we now have to beginning of the current hour, day, month, or even year.<\/p>\n<h3>SIGN<\/h3>\n<p><code>SIGN<\/code> returns +1, 0, or -1 based on the expression supplied.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT&#160; SIGN(10 - 14)\n<\/pre>\n<p class=\"Caption\">Listing 28: Using SIGN<\/p>\n<h3>STUFF<\/h3>\n<p><code>STUFF<\/code> is a powerful built-in function. It inserts one string into another. In addition, it also removes a specific number of characters from one string and adds the second string in place of the removed characters. That isn&#8217;t a very clear explanation, so let&#8217;s take a look at an example.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT&#160; STUFF('junk goes here', 1, 4, 'STUFF')\n&#160;\n\/*\n---------------\nSTUFF goes here\n*\/\n<\/pre>\n<p class=\"Caption\">Listing 29: Using STUFF<\/p>\n<p>The <code>STUFF<\/code> function can be combined with several XML functions to create a comma-separated list of values from a table.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT&#160; STUFF(( SELECT&#160; ',' + Name AS [text()]\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; FROM&#160;&#160;&#160; Production.Culture\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; FOR\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; XML PATH('')\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ), 1, 1, '') AS cultures ;\n<\/pre>\n<p class=\"Caption\">Listing 30: Creating a comma-separated list with STUFF<\/p>\n<h2>Summary<\/h2>\n<p>User-defined functions give us the ability to create reusable chunks of code that simplify the code we write. UDFs can be embedded in queries as single, scalar values or as table valued functions. Effective use of UDFs can increase the readability of your code, enhance functionality, and increase maintainability.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>SQL Server&#8217;s functions are a valuable addition to TSQL when used wisely. Jeremiah provides a complete and comprehensive guide to scalar functions and table-valued functions, and shows how and where they are best used.&hellip;<\/p>\n","protected":false},"author":221915,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4242,4150,5134,4151,4252,4190],"coauthors":[95510],"class_list":["post-1235","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-basics","tag-sql","tag-sql-prompt","tag-sql-server","tag-t-sql-programming","tag-tsql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1235","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221915"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1235"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1235\/revisions"}],"predecessor-version":[{"id":83089,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1235\/revisions\/83089"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1235"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1235"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1235"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1235"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}