InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 13:07:47 GMT20http://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspx[quote][b]Jeff Moden (1/10/2012)[/b][hr]Heh... agreed. That's why I very specifically stated...[quote]...and assuming that you're not actually using negative CompanyIDs...[/quote][/quote]Yes, I'm getting really erratic :blush:. I even quoted that statement and still didn't notice it. How erratic can I get?Tue, 10 Jan 2012 17:57:31 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspx[quote][b]L' Eomot Inversé (1/10/2012)[/b][hr][quote][b]Jeff Moden (1/9/2012)[/b][hr]As a side bar, you've got a huge amount of unnecessary code in your code example that you posted. Take the following snippet, for example...[code="sql"] WHERE GETDATE() BETWEEN eff_date AND term_date AND ( @CompanyID IS NULL OR ( @CompanyID IS NOT NULL AND @CompanyID > 0 AND company_id = @CompanyID ) )[/code]Because NULLs cannot be related to anything else including other NULLs and assuming that you're not actually using negative CompanyIDs, the code can be simplified quite a bit as follows...[code="sql"] WHERE GETDATE() BETWEEN eff_date AND term_date AND (@CompanyID IS NULL OR company_id = NULLIF(@CompanyID,0))[/code][/quote]I can't see any declarations anywhere in any of the code posted that prevents companyID being negative, and unless there is such a restriction this simplification is broken. It seems likely that there is such a restriction (column names with ID that get compared with 0 are often identity columns with 1 as initial seed) but there isn't certain to be, unless I've missed something.[/quote]Heh... agreed. That's why I very specifically stated...[quote]...and assuming that you're not actually using negative CompanyIDs...[/quote]Tue, 10 Jan 2012 16:31:08 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspxThanks, Jeff, for the complete treatment given to the script.Thanks, too, to Paul for uncovering the treasure that is APPLY.My only defense is "inherited code; wasn't broken so didn't fix it". I grabbed the newest split code (thanks) and looked at the code for implicit data type conversions. The usage of the split-string within the snippet posted takes a varchar and concatenates strings before comparison against a string, so there are no implicit conversions there.The get permissions UDF is unchanged in my tenure apart from reformatting it from unreadable spaghetti. It's a msTVF that could probaby be rewritten as an iTVF using some CTEs and UNION ALL statements, but time doesn't permit that change just yet. Folks no longer with the Company said the underlying design needed complete overhaul and I can wait another day until it no longer supports the business - I have bigger fish to fry. The defensive "company > 0" was already there in case the UI sent garbage (outside my control); as I said, it ain't broke and under 2-second response to get all permissions for all users is acceptable by my Director.BTW: Did you have a URL for the reference to the dates-between-harakiri, please? Wouldn't want to bump into misleading posts, as sometimes is the case. The term_date is defaulted to 9999-12-31 23:59:59 and if someone leaves a client company, we usually find out after the fact, so their term_date bereft of time is OK.Thanks again for such an in-depth, meaty post on the posted scripts. If I get into performance issues (growth is just beginning in earnest), I'll be sure to post a new thread per your directive.Tue, 10 Jan 2012 11:21:37 GMTSAinCAhttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspx[quote][b]Jeff Moden (1/9/2012)[/b][hr]As a side bar, you've got a huge amount of unnecessary code in your code example that you posted. Take the following snippet, for example...[code="sql"] WHERE GETDATE() BETWEEN eff_date AND term_date AND ( @CompanyID IS NULL OR ( @CompanyID IS NOT NULL AND @CompanyID > 0 AND company_id = @CompanyID ) )[/code]Because NULLs cannot be related to anything else including other NULLs and assuming that you're not actually using negative CompanyIDs, the code can be simplified quite a bit as follows...[code="sql"] WHERE GETDATE() BETWEEN eff_date AND term_date AND (@CompanyID IS NULL OR company_id = NULLIF(@CompanyID,0))[/code][/quote]I can't see any declarations anywhere in any of the code posted that prevents companyID being negative, and unless there is such a restriction this simplification is broken. It seems likely that there is such a restriction (column names with ID that get compared with 0 are often identity columns with 1 as initial seed) but there isn't certain to be, unless I've missed something.Tue, 10 Jan 2012 09:51:45 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspx[quote][b]SAinCA (1/9/2012)[/b][hr]As requested, Jeff:[/quote]I thought the problem may have been with the split function you had listed but, looking at the code you provided for the split functions, I don't see a particular problem with them other than they're slower (because of the delimiter concatenation) than the new split function available at the URL coming up. The attachments to the article have been updated, so be sure to get the code from the attachments and not the article itself. The code in the attachments is about 20% faster after someone was good enough to provide yet another optimization.Here's the link to the article with the new splitter functions...[url]http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]One of the "problems" (in quotes because it's a human error, not a problem with CROSS APPLY or iTVFs themselves) with iTVFs and CROSS APPLY is a problem that is very similar to a problem with VIEWs. That is, it's "hidden" code or at least "out of site" code AND the code becomes a part of the execution plan as if it had been written as a part of the code. Because of that, you can miss some of the most terrible performance problems due to a very simple oversight... mismatched datatypes which, of course, will cause table and index scans and even full "rewinds" which are worse than mere table scans. That was the same problem that Brad Shultz ran into in the link that Paul previously posted (I never did post back on that thread to show what the problem was). In that case (IIRC... it was a while ago), it was caused by CHARINDEX returning BIGINT instead of just an INT because the first operand of the CHARINDEX Brad used was MAX datatype. The datatype mismatch was a real killer there. Instead of the Numbers table selecting only the numbers it needed, it "rewound" the table 121,316 times and generated a total of 121,317,000 rows internally through a Lazy Table Spool (I have the execution plan but have misplaced the analysis that showed the precise reason, so operating as best I can from memory).Because you haven't included any information as to the datatypes you used for variables or any of the columns used in your code example, I can't even begin to guess where the datatype mismatch (or, possibly, accidental many-to-many join) may be. It may be in the splitters or it (more likely) is in the fn_GetPermissionByUser function you actually used in the CROSS APPLY. And, yeah... because of the lack of information about your code, I can only guess but anytime something like what you've described happens with a conversion to an iTVF and a CROSS APPLY, it's usually due to a datatype mismatch. You have to remember... iTVFs and VIEWs are a lot alike... they "become one" with the calling code and, as with any fully inline code, data type matches become rather critical for any decent performance.I agree with your "word of caution" but I wouldn't blame the iTVF or the CROSS APPLY. Chances are it's something like a simple datatype mismatch and that's likely what you have to be most cautious about. Why does multi-line and scalar TVFs let you get away with it (datatype mismatches and accidental many-to-many joins)? Because the execution plan doesn't incorporate the plans for such things in the main-stream execution plan.Paul is much more of an expert on execution plans than I am. If you were to follow the procedure (see the second link in my signature line at the end of this post) for posting performance problems and opened a new thread for the problem (we've diverged too much from the intent of this thread already), I'm sure that someone, or even Paul himself, would be happy to show you how to correctly incorporate the very high speed methods of combining CROSS APPLY and iTVFs for this particular problem.As a side bar, you've got a huge amount of unnecessary code in your code example that you posted. Take the following snippet, for example...[code="sql"] WHERE GETDATE() BETWEEN eff_date AND term_date AND ( @CompanyID IS NULL OR ( @CompanyID IS NOT NULL AND @CompanyID > 0 AND company_id = @CompanyID ) )[/code]Because NULLs cannot be related to anything else including other NULLs and assuming that you're not actually using negative CompanyIDs, the code can be simplified quite a bit as follows...[code="sql"] WHERE GETDATE() BETWEEN eff_date AND term_date AND (@CompanyID IS NULL OR company_id = NULLIF(@CompanyID,0))[/code]The fact that you're using BETWEEN to compare dates is a whole other chapter in performing SQL Harakiri but this thread isn't the place for that particular discussion. :-)Mon, 09 Jan 2012 20:39:48 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspxExcellent article. I liked the graphics, although I have to say I liked the code file even better. I have a few questions but I think I will wait until I have had a chance to read part 2 before expressing any :cool:KennethMon, 09 Jan 2012 15:52:30 GMTKenneth.Fisherhttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspxAs requested, Jeff:[code="other"]USE DBAGOIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_SplitN]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[udf_SplitN]GOCREATE FUNCTION [dbo].[udf_SplitN] ( @inString nvarchar(MAX) , @delim nvarchar(255) )RETURNS @arrSplit TABLE ( IDX int IDENTITY(0, 1) PRIMARY KEY , [value] nvarchar(1024) )AS /* =============================================================================** original idea by Anith Sen, posted on www.simple-talk.com at** http://www.simple-talk.com/sql/t-sql-programming/the-helper-table-workbench/** Description: Split a nvarchar string into chunks using a delimiter of up to** 255 characters.**** Notes: See the corresponding udf_Split for the varchar version.**SELECT *FROM dbo.udf_Split('one,two,three,four,five,six,seven,eight,nine,ten', ',') SELECT *FROM dbo.udf_Split('Monday--Tuesday--Wednesday--thursday--friday--saturday--sunday', '--')========================================================================== */BEGIN INSERT INTO @arrSplit ( [value] ) SELECT SUBSTRING(@inString + @delim, number, CHARINDEX(@delim, @inString + @delim, number) - number) FROM dbo.Numbers WHERE number <= LEN(REPLACE(@inString, N' ', N'`')) AND SUBSTRING(@delim + @inString, number, LEN(REPLACE(@delim, N' ', N'`'))) = @delim ORDER BY number RETURN END GO[/code]This is used in preference to the function below in cases where 4000 characters is likely to be exceeded, and sometimes by MB. The msTVF works very well at splitting XML files that contain multiple documents - a problem a third-party vendor presented that could not be changed due to their third party software. That's the primary reason for the msTVF's existence in this form.When I know the delimited varchar data are short, I'll use the much-discussed and augmented UDF found on SSC (renamed by me to distinguish functionality from my other 2 udf_Split% UDFs):[code="other"]USE [DBA]GO/****** Object: UserDefinedFunction [dbo].[udf_Split8Kv] Script Date: 01/09/2012 09:26:22 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER FUNCTION [dbo].[udf_Split8Kv]/***************************************************************************************************Purpose: Split a given string at a given delimiter and return a list of the split elements (items).Returns: iTVF containing the following:ItemNumber = Index Position of the Item as an int, begins at ZERO.Item = Element value as a VARCHAR(8000)CROSS APPLY Usage Example:-----------------------------------------------------------------------------------------------------===== Conditionally drop the test tables to make reruns easier for testing.-- (this is NOT a part of the solution)IF OBJECT_ID('tempdb..#JBMTest') IS NOT NULL DROP TABLE #JBMTest;--===== Create and populate a test table on the fly (this is NOT a part of the solution).SELECT * INTO #JBMTest FROM ( SELECT 1,'1,10,100,1000,10000,100000,1000000' UNION ALL SELECT 2,'2000000,200000,20000,2000,200,20,2' UNION ALL SELECT 3, 'This,is,a,test' UNION ALL SELECT 4, 'and so is this' UNION ALL SELECT 5, 'This, too (no pun intended)' UNION ALL SELECT 6, LEFT(REPLICATE('Thisisanunbrokenstring',400),7999) ) d (SomeID,SomeValue);GO--===== Split the CSV column for the whole table using CROSS APPLY (this is the solution)SELECT test.SomeID , split.ItemNumber , split.Item FROM #JBMTest test CROSS APPLY ( SELECT ItemNumber , Item FROM dbo.udf_Split8Kv(test.SomeValue,',') ) split;***************************************************************************************************/--===== Define I/O parameters ( @pString varchar(7999) , @pDelimiter char(1) )RETURNS TABLE WITH SCHEMABINDINGASRETURN--===== "Inline" CTE Driven "Tally Table” produces values up to -- 10,000... enough to cover VARCHAR(8000)WITH E1 ( N ) AS ( --=== Create Ten 1's SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ) , E2 ( N ) AS ( SELECT 1 FROM E1 a , E1 b ) -- 100 , E4 ( N ) AS ( SELECT 1 FROM E2 a , E2 b ) --10,000 , cteTally ( N ) AS ( SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT N ) ) FROM E4 )--===== Do the split SELECT ROW_NUMBER() OVER ( ORDER BY N ) - 1 AS IDX , SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Value FROM cteTally WHERE N < LEN(@pString) + 2 AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiterGO[/code]Mon, 09 Jan 2012 10:28:54 GMTSAinCAhttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspx[quote][b]Jeff Moden (1/8/2012)[/b][hr]Thought the discussion would lend itself to the proper application of CROSS APPLY, but sure.[/quote]Hm, that's actually a good point. As you were, discuss away...:-)Sun, 08 Jan 2012 21:08:45 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspx[quote][b]SQL Kiwi (1/7/2012)[/b][hr][quote][b]Jeff Moden (1/6/2012)[/b][hr]Would you mind posting your split function so I can take a look, please?[/quote]Can I ask that you guys start a new thread for this? Feel free to post a link here though.[/quote]Thought the discussion would lend itself to the proper application of CROSS APPLY, but sure.Sun, 08 Jan 2012 13:15:10 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspx[quote][b]Jeff Moden (1/6/2012)[/b][hr]Would you mind posting your split function so I can take a look, please?[/quote]Can I ask that you guys start a new thread for this? Feel free to post a link here though.Sat, 07 Jan 2012 00:52:08 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspx[quote][b]SAinCA (1/6/2012)[/b][hr][b][size="4"]A word of caution[/size][/b]I took the tip about SCHEMABINDING and the optimizer's expanding iTVFs and converted a Multi-statement UDF (referred to from here on as the TF) that splits up to 12MB of delimited data using a Numbers table. Perhaps this is SS2005EE-specific and later versions are smarter, but...[/quote]It could be the way the split function is using the Numbers table. I've seen it where the entire table gets scanned because of "reflection" (not an official term but that's what I think of it as) back to the table from the "outside" world cause the table to be scanned many times.Would you mind posting your split function so I can take a look, please?Thanks.Fri, 06 Jan 2012 18:50:30 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspx[quote][b]SAinCA (1/6/2012)[/b][hr][b][size="4"]A word of caution[/size][/b][/quote]It is certainly still possible to write highly dubious code using in-line table-valued functions if you really try. There are a few cases where a multi-statement function makes sense; in this case it's because the results of the split are materialized into a table variable once and reused many times that way. Of course, this is something that could easily be done without the msTVF - just split the site IDs into a temporary table first. This can be better than using an msTVF since the 'real' temporary table can have statistics and can be indexed - the hidden msTVF table variable cannot do either of those things!So my [color="#FF0000"][b]word of caution[/b][/color] would be: if you find a multi-statement TVF performing better, check that you shouldn't be materializing something yourself. Do it properly with an iTVF and a temporary table and you'll get statistics, indexes, and probably better performance.Fellow MVP Brad Shultz went into some details about why the multi-statement hidden materialization works here:[url]http://bradsruminations.blogspot.com/2010/08/integer-list-splitting-sql-fable.html[/url]Fri, 06 Jan 2012 17:02:23 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspx[b][size="4"]A word of caution[/size][/b]I took the tip about SCHEMABINDING and the optimizer's expanding iTVFs and converted a Multi-statement UDF (referred to from here on as the TF) that splits up to 12MB of delimited data using a Numbers table. Perhaps this is SS2005EE-specific and later versions are smarter, but...BAD MOVE!I have this (inherited) snippet from an SP that uses CROSS APPLY in the FROM clause and has a WHERE clause that contains DBA.dbo.udf_Split(), which is the TF I converted to an iTVF. The SP happens to be the most important SP in the system - the one that gets a User's permissions to do [b]anything[/b]. It runs in under [b][i]1 second[/i][/b] for [u][b]over 500[/b][/u] Users using the original TF. However, it takes [b][i][u]15+ minutes[/u][/i][/b] to run the [u]same[/u] SP using the iTVF for a [u][b]single[/b][/u] User, I consider this a very bad move and will pull the plug on changing this TF to an iTVF:-D[code="other"] FROM dbo.t_user_ref ur CROSS APPLY dbo.fn_GetPermissionByUser(user_id) AS p WHERE GETDATE() BETWEEN eff_date AND term_date AND ( @CompanyID IS NULL OR ( @CompanyID IS NOT NULL AND @CompanyID > 0 AND company_id = @CompanyID ) ) AND ( @SiteIDs IS NULL OR ( @SiteIDs IS NOT NULL AND EXISTS ( SELECT 1 FROM DBA.dbo.udf_Split( @SiteIDs + ', ',',') WHERE ',' + r_site_ids + ',' <> REPLACE(',' + r_site_ids + ',', ',' + [value] + ',', '') ) ) ) AND ( @UserID IS NULL OR ( @UserID IS NOT NULL AND @UserID > 0 AND user_id = @UserID ) ) AND ( @UserRole IS NULL OR ( @UserRole IS NOT NULL AND p.r_user_role LIKE '%' + @UserRole ) )[/code]The optimizer did indeed expand the iTVF form of the UDF into the main SELECT, but then goodness knows why it decides that 12789618 rows must now be scanned for each User row.Be very, very careful which TFs you convert to iTVFs... :ermm:Fri, 06 Jan 2012 16:52:20 GMTSAinCAhttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspx[quote][b]reinpost (1/6/2012)[/b][hr][quote][b]SQL Kiwi (1/6/2012)[/b][hr][quote][b]reinpost (1/6/2012)[/b][hr]Just a trivial remark: right at the start it says [quote]APPLY is named after the process of applying a set of input rows to a table-valued function.[/quote] Surely you mean to say [i]applying a table-valued function to a set of input rows[/i].[/quote]I think it makes sense either way! :cool:[/quote]So do I, but mathematicians and programmers tend to say that functions are applied to things. In fact this new operator was probably named after Lisp's 'apply' , which does the same thing.[/quote]Even though it is irrelevant... I'll agree that the proper is that the function applies to the rows. I thought about it like a car and paint. When you apply the paint to the car, the properties of the paint do not change the car does. So in this case the function does not change at all, it is applied to the rows of which the properties have changed. Now that I said that... Who cares, it still accomplishes the same thing :PFri, 06 Jan 2012 12:11:00 GMTSQLKnowItAllhttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspx[quote][b]SQL Kiwi (1/6/2012)[/b][hr][quote][b]reinpost (1/6/2012)[/b][hr]Just a trivial remark: right at the start it says [quote]APPLY is named after the process of applying a set of input rows to a table-valued function.[/quote] Surely you mean to say [i]applying a table-valued function to a set of input rows[/i].[/quote]I think it makes sense either way! :cool:[/quote]So do I, but mathematicians and programmers tend to say that functions are applied to things. In fact this new operator was probably named after Lisp's 'apply' , which does the same thing.Fri, 06 Jan 2012 11:33:02 GMTreinposthttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspx[quote][b]reinpost (1/6/2012)[/b][hr]Just a trivial remark: right at the start it says [quote]APPLY is named after the process of applying a set of input rows to a table-valued function.[/quote] Surely you mean to say [i]applying a table-valued function to a set of input rows[/i].[/quote]I think it makes sense either way! :cool:Fri, 06 Jan 2012 11:03:44 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspxReally Nice article ..looking forward for more...good luck..Fri, 06 Jan 2012 07:27:31 GMTlogicinside22http://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspxJust a trivial remark: right at the start it says [quote]APPLY is named after the process of applying a set of input rows to a table-valued function.[/quote] Surely you mean to say [i]applying a table-valued function to a set of input rows[/i].Fri, 06 Jan 2012 04:11:52 GMTreinposthttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspxThanks, Tom.Thu, 01 Dec 2011 05:01:46 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspxThis is very good Paul.Concise and Clear, not too long and with easily understandable examples.Thanks for taking the time to create this article. I appreciate your efforts. 10 minutes learning something new in the morning is a great way to start my day.Thu, 01 Dec 2011 02:02:51 GMTTom Brownhttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspx[quote][b]SQL Kiwi (10/18/2011)[/b][hr][quote][b]jared-709193 (10/18/2011)[/b][hr]A question that I have is... Is there a performance reason to use or not use a TVF (by this I mean one that uses CREATE FUNCTION versus a correlated subquery). I can see business cases for both as well as personal preference, but I was curious about performance.[/quote]Hi Jared,In-line TVFs are like (parameterized) views in that the definition of the function is expanded into the query text before optimization. A query that uses an in-line TVF will be optimized the same as if you wrote the function text out by hand as a subquery.[quote]Also, you spoke briefly about multi-statement functions, but I am still not 100% clear on this. Does this mean a function that uses CREATE FUNCTION that contains multiple statements in it?[/quote]There's quite a lot of good information about functions in Books Online, for example: [url=http://msdn.microsoft.com/en-us/library/ms177499.aspx]Types of Functions[/url] (link)A multi-statement TVF definition contains the keywords BEGIN and END (as do scalar user-defined functions) and more than one statement. In-line TVFs use the RETURNS TABLE syntax to introduce a single SELECT. In-line functions do not use the BEGIN/END combination.Multi-statement functions (and their scalar relations) are often, though not always, a performance drag for many reasons. One reason is that only in-line TVFs are expanded into the parent query for optimization. Scalar UDFs can be especially troublesome because they are executed once per row, in a new T-SQL context, and cannot use parallelism.[/quote]Great! Thanks for the quick response Paul.JaredTue, 18 Oct 2011 11:42:32 GMTSQLKnowItAllhttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspx[quote][b]jared-709193 (10/18/2011)[/b][hr]A question that I have is... Is there a performance reason to use or not use a TVF (by this I mean one that uses CREATE FUNCTION versus a correlated subquery). I can see business cases for both as well as personal preference, but I was curious about performance.[/quote]Hi Jared,In-line TVFs are like (parameterized) views in that the definition of the function is expanded into the query text before optimization. A query that uses an in-line TVF will be optimized the same as if you wrote the function text out by hand as a subquery.[quote]Also, you spoke briefly about multi-statement functions, but I am still not 100% clear on this. Does this mean a function that uses CREATE FUNCTION that contains multiple statements in it?[/quote]There's quite a lot of good information about functions in Books Online, for example: [url=http://msdn.microsoft.com/en-us/library/ms177499.aspx]Types of Functions[/url] (link)A multi-statement TVF definition contains the keywords BEGIN and END (as do scalar user-defined functions) and more than one statement. In-line TVFs use the RETURNS TABLE syntax to introduce a single SELECT. In-line functions do not use the BEGIN/END combination.Multi-statement functions (and their scalar relations) are often, though not always, a performance drag for many reasons. One reason is that only in-line TVFs are expanded into the parent query for optimization. Scalar UDFs can be especially troublesome because they are executed once per row, in a new T-SQL context, and cannot use parallelism.Tue, 18 Oct 2011 11:39:40 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspxGreat article (although I am a little late getting to read it)! A question that I have is... Is there a performance reason to use or not use a TVF (by this I mean one that uses CREATE FUNCTION versus a correlated subquery). I can see business cases for both as well as personal preference, but I was curious about performance. Also, you spoke briefly about multi-statement functions, but I am still not 100% clear on this. Does this mean a function that uses CREATE FUNCTION that contains multiple statements in it?Just trying to find out more of an answer about "functions" here versus the subquery route.Thanks,JaredTue, 18 Oct 2011 09:58:58 GMTSQLKnowItAllhttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspx[quote][b]rob mcnicol (12/21/2010)[/b][hr]choice names for the studentschur![/quote]Best comment so far! :laugh:Tue, 21 Dec 2010 00:34:18 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspxchoice names for the studentschur!Tue, 21 Dec 2010 00:02:06 GMTrob mcnicolhttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspxThanks, Doodles :-)Wed, 15 Dec 2010 08:28:20 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspxJust wanted to express my thanks for putting this article together! You got the concept of CROSS APPLY across perfectly. I'm grateful to all you DBA/DEV boddhisattvas out there sharing the knowledge! :-)DoodlesWed, 15 Dec 2010 07:05:19 GMTdoodlingdbahttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspxI haven't got around to read the 2nd part of the article completely. I did have a glance. I just want to thank you again as what i learned here helped me to optimize a query by a great margin. You Rock! :-)-arjunFri, 07 May 2010 00:22:46 GMTArjun Sivadasanhttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspx[quote][b]Carla Wilson-484785 (4/20/2010)[/b][hr]Can you share some examples of other ways to use APPLY. I would definitely like to learn some other techniques. Thanks.[/quote]Take a look in the download files for both parts, and be sure to read part 2 of the article as well.Tue, 20 Apr 2010 17:20:59 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspx[quote][b]David Walker-278941 (4/19/2010)[/b][hr]This article, like many discussions of Cross Apply and Outer Apply, says "APPLY is named after the process of applying a set of input rows to a table-valued function."It then backtracks a little and says that you don't have to use a table-valued function. Still, I think it's a disservice that many examples leave you with the impression that the only use for Apply is with functions. I often use an expression on the right side of the Apply operator. I wish there were more examples like this. The right side of the expression can be a correlated subquery, for example, and that can be very useful.[/quote]Can you share some examples of other ways to use APPLY. I would definitely like to learn some other techniques. Thanks.Tue, 20 Apr 2010 08:26:29 GMTCarla Wilson-484785http://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspxOK, I get your point (both of you!)... I jsut get a little frustrated when so many examples fixate on one kind of thing. (For example, try to find beginning "teaching" examples that show two (or more) conditions on a Join statement, or conditions that use anything other than equality for a comparison. It's not your fault, but so many examples everywhere seem to be so monolithic!)I think a tiny improvement would be to have Part 1 mention that other expressions such as correlated subqueries can also be used, and that you'll include examples in part 2. Thanks for the article; it is helpful. DavidMon, 19 Apr 2010 23:18:19 GMTDavid Walker-278941http://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspx[quote][b]David Walker-278941 (4/19/2010)[/b][hr]This article, like many discussions of Cross Apply and Outer Apply, says "APPLY is named after the process of applying a set of input rows to a table-valued function."It then backtracks a little and says that you don't have to use a table-valued function. Still, I think it's a disservice that many examples leave you with the impression that the only use for Apply is with functions. [/quote]It does say very clearly that this is article one of two, only the first half of the story. So I think it's unreasonable to complain that it doesn't have the whole story.edit: spellingMon, 19 Apr 2010 19:22:01 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspx[quote][b]David Walker-278941 (4/19/2010)[/b][hr]This article, like many discussions of Cross Apply and Outer Apply, says "APPLY is named after the process of applying a set of input rows to a table-valued function."It then backtracks a little and says that you don't have to use a table-valued function. Still, I think it's a disservice that many examples leave you with the impression that the only use for Apply is with functions. I often use an expression on the right side of the Apply operator. I wish there were more examples like this. The right side of the expression can be a correlated subquery, for example, and that can be very useful.[/quote]Yes, I walk a fine line differentiating 'function' from 'user-defined function', but I think overall the compromise on wording is a happy one. Please see part 2 (available now) for more examples of the type you seek.Mon, 19 Apr 2010 17:26:55 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspxThis article, like many discussions of Cross Apply and Outer Apply, says "APPLY is named after the process of applying a set of input rows to a table-valued function."It then backtracks a little and says that you don't have to use a table-valued function. Still, I think it's a disservice that many examples leave you with the impression that the only use for Apply is with functions. I often use an expression on the right side of the Apply operator. I wish there were more examples like this. The right side of the expression can be a correlated subquery, for example, and that can be very useful.Mon, 19 Apr 2010 09:34:10 GMTDavid Walker-278941http://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspxGreat articles, Paul. Very clear and complete explanations and examples.Mon, 19 Apr 2010 07:40:33 GMTgrc-80104http://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspxThanks for the excellent article! Clear and concise!Providing sample code with extra examples was a perfect way to provide more information, without cluttering the main article.I've never actually used APPLY before, but your article has given me a host of new ideas...Looking forward to Part II!Fri, 16 Apr 2010 12:32:47 GMTGoldie Lesserhttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspxI was writing a similar article (on some of the possibly lesser-known and/or used T-SQL features)to present to the rest of my team; I think I will scrap that and just point them at your article! ;-)Informative, concise and well-written.Many thanks,LempsterFri, 16 Apr 2010 03:41:34 GMTLempsterhttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspxI like the nice clear simple to understand approach, and the layout. Waiting eagerly for part 2.Thu, 15 Apr 2010 08:58:53 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspx[quote][b]Paul White NZ (4/14/2010)[/b][hr][quote][b]RBarryYoung (4/14/2010)[/b][hr]The problem with the articles here at SSC, is that unlike the forum posts, we have no way to see how someone's formatting tricks are done, so we cannot easily learn from each other :([/quote]No real trickery here - just screen-shots, Paint.NET, and time :satisfied:[/quote]Ah, I see now. You did all of your tables and code blocks as images too. Clever ...Thu, 15 Apr 2010 00:00:43 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic901119-2669-1.aspx[quote][b]RBarryYoung (4/14/2010)[/b][hr]The problem with the articles here at SSC, is that unlike the forum posts, we have no way to see how someone's formatting tricks are done, so we cannot easily learn from each other :([/quote]No real trickery here - just screen-shots, Paint.NET, and time :satisfied:Wed, 14 Apr 2010 23:36:17 GMTPaul White