Hello Folks,

You would be very much aware of the View in SQL Server. But do you really know about SQL Server Inline Table Valued Functions?

Well you don’t have to worry further more on this, because I will let explain you in a brief.

With the introduction of SQL Server 2000, Microsoft has offered two types of table-valued functions, i.e., Inline and Multi-Statement Table-Valued Functions. But now I am going to deal with the Inline table-valued functions.

As you would had seen in my previous blog-post, that how a scalar function returns a single value. If you want to see it again, then here is the link;

Here are some keynotes about Inline Table-Valued Functions:

  • It has no BEGIN/END body, and the SELECT statement is being used to return a virtual table.
  • You can see the syntax for this:

CREATE FUNCTION [owner_name.]function_name

( [ { @parameter_name data_type

[ = default ] } [ ,…n ] ] )


[ WITH < function_option > [ ,…n ] ]

[ AS ]

RETURN [ ( ] select_statement [ ) ]

Now, the concept if Inline Table-Valued Functions will become clearer if you see the following example. It is a very simple one, watch it carefully-

Now, Calling an Inline Table-Valued Function:


  • One thing I discovered from the above example is that, while you are creating a function you are not at all being allowed to show the table in which two columns have the same name, i.e., every column name should be unique.

Now, the question arises are Inline Table-Valued Functions are really better than a View…If Yes, then Why?

Let say, if you want a subset of rows form a view, then you need to apply a search criteria in a WHERE clause. But an Inline Table-Valued Functions can accept search criteria as function parameters within the pre-compiled SELECT statement, which gives more ease as well as flexibility to the users and developers of SQL.

It will become clearer to you, if you see an example below:

Now, if you want to see the Student whose Id is 1, than you just have to write the query statement like:


If you want to display all the Students irrespective of their Id’s, then write a query like:


Well, this was a bit about Inline Table-Valued Functions.

In the next post I would like to deal with Multi-Statement Inline Table Valued Functions.

So be tuned and also comments on this!!



Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook