Differentiate Multi statement and Inline table valued function in SQl server

Question

Differentiate Multi statement and Inline table valued function in SQl server ?

Vinitha T · Answer

Table Valued Function falls under User Defined Functions, it returns output as set/row/table. It has two types of Functions: Inline Table Valued Function. Multi Statement Table Valued Function. In Inline Table Valued Function, the function body have only one select statement with Return statement as type Table. Syntax: CREATE FUNCTION InlineFunction ( @parameter1 dataType, @parameter2 dataType ) RETURNS TABLE AS RETURN ( SELECT statement ) In Multi Statement Table Valued Function, the function body can have one or more than one statement. Syntax: CREATE FUNCTION MultiStatementFunction ( @parameter1 dataType, @parameter2 dataType ) RETURNS @TableVariable TABLE (column1 dataType, column2 dataType,..) AS BEGIN function body RETURN END Main Differences: Inline functions cannot have Begin & End block where Multi statement have Begin & End block. Inline functions has only single SELECT statement whereas Multi-statement functions has more than one statement. Inline functions output table structure is defined by the SELECT statement whereas Multi-statement output table structure is defined in function by user.

Vinitha T · Answer

Table Valued Function falls under User Defined Functions, it returns output as set/row/table. It has two types of Functions: Inline Table Valued Function. Multi Statement Table Valued Function. In Inline Table Valued Function, the function body have only one select statement with Return statement as type Table. Syntax: CREATE FUNCTION InlineFunction ( @parameter1 dataType, @parameter2 dataType ) RETURNS TABLE AS RETURN ( SELECT statement ) In Multi Statement Table Valued Function, the function body can have one or more than one statement. Syntax: CREATE FUNCTION MultiStatementFunction ( @parameter1 dataType, @parameter2 dataType ) RETURNS @TableVariable TABLE (column1 dataType, column2 dataType,..) AS BEGIN function body RETURN END Main Differences: Inline functions cannot have Begin & End block where Multi statement have Begin & End block. Inline functions has only single SELECT statement whereas Multi-statement functions has more than one statement. Inline functions output table structure is defined by the SELECT statement whereas Multi-statement output table structure is defined in function by user.

Rahul Bhatt · Answer

Normal 0 false false false EN-IN X-NONE X-NONE MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable{mso-style-name:"Table Normal";mso-tstyle-rowband-size:0;mso-tstyle-colband-size:0;mso-style-noshow:yes;mso-style-priority:99;mso-style-qformat:yes;mso-style-parent:"";mso-padding-alt:0cm 5.4pt 0cm 5.4pt;mso-para-margin-top:0cm;mso-para-margin-right:0cm;mso-para-margin-bottom:10.0pt;mso-para-margin-left:0cm;line-height:115%;mso-pagination:widow-orphan;font-size:11.0pt;font-family:"Calibri","sans-serif";mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;mso-bidi-font-family:"Times New Roman";mso-bidi-theme-font:minor-bidi;mso-fareast-language:EN-US;} UDF Come into two format 1. Inline 2. Multistatement Multistatement :: Include multiple statement Inline :: include only one select statement Multistatement :: can provide more flexibility Inline :: more efficient then Multistatement Check following link for more detail http://www.sqlmag.com/article/tsql3/inline-vs-multistatement-table-valued-udfs

Senthilkumar · Answer

Inline table can returns the result set from the single query. But the multi statement valued function will allow you to insert the result set into the same table where you can do the DML operation before returns to the calling program.

Jignesh Trivedi · Answer

hi shubh, Please refer, it is good discussion on Multi statement and Inline table valued function in SQl server. http://stackoverflow.com/questions/2554333/multi-statement-table-valued-function-vs-inline-table-valued-function hope this will help you.

Satyapriya Nayak · Answer

Hi Shubham, Please refer the below link http://www.sqlservercentral.com/blogs/discussionofsqlserver/2012/02/15/comparing-inline-and-multistatement-table-valued-functions/ Thanks

Differentiate Multi statement and Inline table valued function in SQl server

Answers (6)