Inline or Table Function differences

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Inline or Table Function differences

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-28 : 16:16:12

What is the difference in using an Inline Function which returns a table and a Table function (which also returns a table)? Is this just a matter of coding preference?

CREATE FUNCTION <inline_function_name, sysname, test_function> 
	(<@param1, sysname, @p1> <data_type_for_param1, , int>, 
	 <@param2, sysname, @p2> <data_type_for_param2, , char>)
RETURNS TABLE 
AS
	RETURN SELECT   @p1 AS c1, 
			@p2 AS c2
GO

-- =============================================
-- Example to execute function
-- =============================================
SELECT * 
FROM <owner, , dbo>.<inline_function_name, sysname, test_function> 
	(<value_for_@param1, , 1>, 
	 <value_for_@param2, , 'a'>)
GO
CREATE FUNCTION <table_function_name, sysname, test_function> 
	(<@param1, sysname, @p1> <data_type_for_param1, , int>, 
	 <@param2, sysname, @p2> <data_type_for_param2, , char>)
RETURNS <@table_variable_name, sysname, @table_var> TABLE 
	(<column_1, sysname, c1> <data_type_for_column1, , int>, 
	 <column_2, sysname, c2> <data_type_for_column2, , int>)
AS
BEGIN
	INSERT <@table_variable_name, sysname, @table_var>
	SELECT 1, 2
	RETURN 
END
GO

-- =============================================
-- Example to execute function
-- =============================================
SELECT * FROM <owner, , dbo>.<table_function_name, sysname, test_function>
	(<value_for_@param1, , 1>, 
	 <value_for_@param2, , 2>)
GO

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-28 : 16:34:34

Inline functions are single statement. Table functions can be multi-statement.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-28 : 16:35:43

The first is an Inline Table-valued Function and the second is a Multi-statement Table-valued Function. If you go to CREATE FUNCTION topic in BOL, it shows an example of each.

Tara

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-28 : 16:44:57

Are Inline functions somehow integrated into stored procedures better than calling external stored procs and Multi-statement Table-valued functions?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-28 : 16:48:11

No, the integration is the same. It just depends on what the UDF needs to do in order to decide which type of function to use. If your returned table can be summarized into one SELECT statement, then you use an inline table-valued function. If you can't do it in one SELECT statement, then you use multi-statement table-valued function. This allows you to build the result set via multiple statements.

Tara

Kristen
Test

22859 Posts

Posted - 2005-03-01 : 00:29:19

I think the second one allows you to put a PK on the table, and the first doesn't. For me that would be the clincher - termpoary tables perform WAY better with a PK on them ... YMMV of course

Kristen

SamC
White Water Yakist

3467 Posts

Posted - 2005-03-01 : 08:44:43

I remember some languages compiled inline functions as if macros were expanded inline as opposed to passing parameters to an external routine.

Thanks for the comments. I guess the last word has been spoken on a small but obscure SQL subject >> * <<

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-01 : 10:21:23

quote:
Originally posted by Kristen

I think the second one allows you to put a PK on the table, and the first doesn't. For me that would be the clincher - termpoary tables perform WAY better with a PK on them ... YMMV of course

Kristen

Really?

Most every temp table I build is meant to be a driver...and as such is going to be scanned anyway

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2005-03-01 : 10:45:45

I guess it depends on the temp table size and use. I've got some temp tables that hold 4 rows that I don't bother to index. Then I've got a couple of "honkin" temp tables that I build with clustered indexes. Sometimes indexing a temp is a real benefit.

SamC
White Water Yakist

3467 Posts

Posted - 2005-03-01 : 10:46:50

quote:
Originally posted by X002548

Most every temp table I build is meant to be a driver...

What's a driver?

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-01 : 11:14:22

quote:
Originally posted by SamC

I guess it depends on the temp table size and use. I've got some temp tables that hold 4 rows that I don't bother to index. Then I've got a couple of "honkin" temp tables that I build with clustered indexes. Sometimes indexing a temp is a real benefit.

Anything that substantial should be a permanent table in my own opinion (MOO)

A driver is a set of resultant rows that you want to do work against. After your population is identified, it is stored in a temp table. Since you have work to do against them, every single row will be processed.

The temp table and it's rows, "Drive" the entire process. Hence, driver.

Since every row will be processed, the optimizer will always scan that table. Always. One might say, that an index could be used, in which case it would scan the index.

But why keep information in the driver that you don't need, especially if it exists somewhere else.

In either case, it's a scan. If it's not, then I'd say you need to build a smaller temp table that will effeciently drive whatever work you need to do.

MOO

Brett

8-)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-01 : 18:06:29

When the function is an inline table function, it may perform better with a large result set, because it does not have to go to the trouble of storing the result in a temp table first. It can return the result directly into your query.

On the other hand, the optimizer "steps into" an inline table function to optimize the whole query. Depending on the complexity of the query in the function, this can cause it to generate an unexpected query plan, or take a long time to generate the query plan. I usually use a TOP 100 PERCENT and ORDER BY on the final result set to prevent the optimizer from doing this. It can also run you into limits like too many tables in the query.

Generally speaking, the inline table function acts more like a view, while a regular table function acts more like a table.

Codo Ergo Sum

Kristen
Test

22859 Posts

Posted - 2005-03-02 : 14:59:29

"Really?"

Well, I could be talking out of the wrong end, and I haven't got time for the obligatory TestTable99 today, sorry, but I'm pretty PRETTY sure that we have had DIRE performacne from TempTable with no index which went away when we put an index on them.

Kristen

SamC
White Water Yakist

3467 Posts

Posted - 2005-03-02 : 15:09:57

quote:
Originally posted by X002548

Anything that substantial should be a permanent table in my own opinion (MOO)

I suppose it's possible to code around the concurrency problems of several users needing the same permanent table by using a unique key index for each user, but isn't this one of the problems that temporary tables were created to solve? It's about localization of scope. And if not scope, listerine.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2005-03-02 : 16:28:15

SamC:

Sure, temp tables may assist in reducing the complexities of implementation associated with solutions requiring localization, but nothing comes without a cost. You will need to weigh the performance benefits of implementing a permanent table vs. the ease of coding made possible by using a temp table.

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-03 : 09:44:03

OK

I'm confused.

Why would a temp table have that volume of data?

And there's alos the overhead of rebuilding the thing everytime, for every spid?

I'm curious as to the process that builds this.

Anyway, it's as easy as using the spid on the row....

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2005-03-04 : 12:14:41

Ack...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources