Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Potentially delusional co-worker, inline vs. multi statement TVF Expand / Collapse
Author
Message
Posted Monday, September 3, 2012 7:34 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 6:54 PM
Points: 61, Visits: 677
Greetings --

I am having a disagreement with a co-worker about what constitutes a multi-statement Table Valued Function.

A sample call to the function would be something like:

SELECT table1.col1, table1.id, othercol1, othercol2
FROM table1 OUTER APPLY (SELECT * FROM func1(id))

I've stripped out the bulk of the function, and included it below.

Co-worker contends that since there only a single SELECT statement that ultimately returns a type of table, and also because he doesn't specifically define the columns/data types that this must be considered "inline".

Certainly doesn't seem that way to me, and so I thought I'd ask the experts at SqlServerCentral.

I have other questions about this example, but I'll include them in another post.

Thanks for any feedback.

SQLNYC

------------------------------

CREATE FUNCTION [dbo].[func1]
(
@param_x INT
)
RETURNS TABLE
AS
RETURN
(
WITH ReminderEntity ( param1, param2 ) AS
(
SELECT ...
),
ReminderParent ( param_y ) AS
(
SELECT TOP 1 some_col FROM
(
SELECT ...
WHERE

UNION ALL

SELECT ...
OUTER APPLY ( SELECT * FROM func2(param1, param2, param3)) AS some_alias
WHERE some_condition
) AS Temp
),
ReminderParentEntity ( param1, param2 ) AS
(
SELECT ...
),
ReminderParentGuidEntity ( param1, param2 ) AS
(
SELECT ...
)
SELECT TOP 1 EmpID FROM
(
SELECT TOP 1 EmpID
FROM
(
SELECT ...
INNER JOIN

UNION ALL

SELECT ...
INNER JOIN

UNION ALL

SELECT ...
INNER JOIN

UNION ALL

SELECT ...
INNER JOIN

UNION ALL

SELECT ...
INNER JOIN

UNION ALL

SELECT ...
INNER JOIN

UNION ALL

SELECT ...
INNER JOIN

UNION ALL

SELECT ...
INNER JOIN

UNION ALL

SELECT ...
INNER JOIN

UNION ALL

SELECT ...
INNER JOIN
) AS RemindersEmployee

UNION ALL

SELECT TOP 1 ...
FROM some_table
INNER JOIN
INNER JOIN
LEFT JOIN
LEFT JOIN
CROSS JOIN
WHERE some_condition
) AS Temp
)
Post #1353669
Posted Monday, September 3, 2012 8:09 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 20,799, Visits: 32,717
The skeleton you show us is an inline table valued function.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1353675
Posted Monday, September 3, 2012 8:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:00 AM
Points: 35,547, Visits: 32,137
Lynn Pettis (9/3/2012)
The skeleton you show us is an inline table valued function.


Agreed. The table is not explicitly defined and there is no BEGIN/END pair (just to name a couple of the more obvious giveaways on this one).


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1353679
Posted Monday, September 3, 2012 9:37 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 6:54 PM
Points: 61, Visits: 677
Thanks very much Jeff and Lynn.

Best,

SQLNYC
Post #1353692
Posted Tuesday, September 4, 2012 2:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 40,385, Visits: 36,827
A multi-statement table valued user-defined function looks like this: (example from Books Online)

CREATE FUNCTION dbo.FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE
(
EmployeeID int primary key NOT NULL,
FirstName nvarchar(255) NOT NULL,
LastName nvarchar(255) NOT NULL,
JobTitle nvarchar(50) NOT NULL,
RecursionLevel int NOT NULL
)
AS
BEGIN
INSERT INTO @retFindReports
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM ....


RETURN
END;

The key things being the definition of the table variable and one or more inserts into that table variable



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1353768
Posted Tuesday, September 4, 2012 10:24 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 12:29 PM
Points: 1,945, Visits: 3,121
The short answer is it has one SELECT statement, so her is right.

The better answer is why aren't you using a VIEW and not writing proprietary SQL. The next question is why this mess needs sooo many inner joins, outer joins, unions, applies etc that it screams "This schema was blown up by a passing terrorist! We are re-assembling the splattered remains back to what it should have been!"

It just smells like really awful DDL.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1354310
Posted Wednesday, September 5, 2012 5:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 6:54 PM
Points: 61, Visits: 677
Hi Gail and Joe - thanks so much for your replies.

Just wanted to be clear that this is not my code --

Joe - I completely agree that this database is a disaster. An inherited structure that can not easily be changed, unfortunately.

SQLNYC
Post #1354450
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse