Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 305
SQL Server 304
Administration 252
DBA 241
T-SQL 234
#TSQL 232
Development 226
Tips 216
Guidance 148
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

#0337 - SQL Server - Inline TVFs - Using multiple SELECTs with UNION

May 19 2014 12:00AM by Nakul Vachhrajani   

The topic of today’s post might be obvious, but is backed by a question that came to me while making a change to one of our in-line table-valued functions (TVF).

We had an in-line TVF to which we had to add another SELECT statement in order to achieve a business requirement. Normally, the first thing that most developers would do is to convert it into a multi-line TVF. As a DBA, this would raise a red flag due to the much-talked about performance issues associated with multi-line TVFs.

Per Books-on-line (refer “Further Reading” section below):

“The SELECT statement used in an inline function is subject to the same restrictions as SELECT statements used in views.”

Now, a view can use multiple SELECT statements separated by UNION or UNION ALL. Hence, if a UNION can be used be used such that the SELECTs in a TVF can be combined into a single statement, then SQL Server will continue to treat the function as an in-line TVF. Here’s the simple example I used to cross-check that a function with only multiple SELECTs combined via the use of UNION will make it an in-line TVF:

USE tempdb;
GO

--Safety Check
IF OBJECT_ID('dbo.tmpfunc_MultiLineTest','IF') IS NOT NULL
    DROP FUNCTION dbo.tmpfunc_MultiLineTest;
GO

--Create the function
--Disclaimer: This function has been created for demo purposes only!
CREATE FUNCTION dbo.tmpfunc_MultiLineTest
    (@id1 INT,
     @id2 INT
    )
RETURNS TABLE
AS
RETURN
    SELECT 1 AS Col1, 2 AS Col2
    UNION
    SELECT 2 AS Col1, 3 AS Col2
    UNION
    SELECT @id1 AS Col1, @id2 AS Col2
GO

--Is the function an in-line table valued function?
SELECT so.name, 
       so.type, 
       so.type_desc 
FROM sys.objects AS so 
WHERE so.name = 'tmpfunc_MultiLineTest';
GO
-------------------------
--       RESULTS
-------------------------
--name                     type type_desc
-------------------------- ---- ---------------------------------
--tmpfunc_MultiLineTest    IF   SQL_INLINE_TABLE_VALUED_FUNCTION

As you can see, I have a function that uses the syntax for in-line TVFs and has 3 SELECT statements combined via the use of a UNION operator. I then query the sys.objects catalog view which clearly tells me that this created an in-line TVF.

The reason? It’s quite simple – it’s still a single query!

Further Reading

  • Inline User Defined Table Valued Functions [TechNet Link]
  • Table valued parameters to a table valued function – Underappreciated features of Microsoft SQL Server [Blog Link]

Until we meet next time,

Be courteous. Drive responsibly.

Tags: #SQLServer, SQL Server, Administration, DBA, T-SQL, #TSQL, Guidance, Tips


Nakul Vachhrajani
4 · 36% · 11648
2
 
0
Lifesaver
 
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"#0337 - SQL Server - Inline TVFs - Using multiple SELECTs with UNION" rated 5 out of 5 by 2 readers
#0337 - SQL Server - Inline TVFs - Using multiple SELECTs with UNION , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]