SQL Server Inline Table Valued Functions better than a View

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 ] ] )

RETURNS TABLE

[ 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-

USE TEST;
 
CREATE FUNCTION ftStudList()
RETURNS Table
AS
RETURN(
SELECT dbo.Students1.[SID] AS Stud_Id1,dbo.Students1.Name AS Stud_Name1,dbo.Students1.City AS Stud_City1,
dbo.Students2.[SID] AS Stud_Id2,dbo.Students2.Name Stud_Name2,dbo.Students2.City AS Stud_City2 
FROM dbo.Students1
INNER JOIN dbo.Students2
ON dbo.Students1.[SID]=dbo.Students2.[SID]);

Now, Calling an Inline Table-Valued Function:

1_SQL_Server_Are_Inline_Table_Valued_Functions_better_than_a_View

  • 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:

CREATE FUNCTION ftStudListbyId(@Id INT = NULL)
RETURNS Table
AS
RETURN(
SELECT dbo.Students1.[SID] AS Stud_Id1,dbo.Students1.Name AS Stud_Name1,dbo.Students1.City AS Stud_City1,
dbo.Students2.[SID] AS Stud_Id2,dbo.Students2.Name Stud_Name2,dbo.Students2.City AS Stud_City2 
FROM dbo.Students1
INNER JOIN dbo.Students2
ON dbo.Students1.[SID]=dbo.Students2.[SID]
WHERE dbo.Students1.[SID] = @Id 
OR @Id IS NULL);

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

2_SQL_Server_Are_Inline_Table_Valued_Functions_better_than_a_View

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

3_SQL_Server_Are_Inline_Table_Valued_Functions_better_than_a_View

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!!

 

Regards

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

   

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

4 Comments on “SQL Server Inline Table Valued Functions better than a View”

Leave a Reply

Your email address will not be published.