Table Variables in User Defined Table Functions
Today I want to drill briefly into one of the uses described yesterday for using Table Variables…user defined table functions.
There are two kinds of user defined table functions. The best performing version is a table value function that returns the results of a single SQL Statement. That statement may be a compound statement using inline views or common table expressions; but, it must be a single statement. It differs from a view in that it can take advantage of input parameters much like a stored procedure.
The other kind of user defined table function uses a table variable as the final output. This kind of function may be used when the logic of the function cannot be resolved with a single SQL statement. It performs best with a minimum amount of work. There are no limits to the number of SQL statements included in the body to fulfill the necessary logic.
I have found very few uses for this kind of user defined function. Performance is much slower than an inline table value function. If the necessary data can’t be defined in a single inline query, there are few instances where a table function is the best resource.
As always, your experience is useful to many of our readers. Feel free to leave comments below, or send an Email to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Narrowing Your Career Choices to Get Ahead
As with most professionals, the list of what we can do is often very large and diverse. When we start to make a list of everything that we ‘could’ do, we often get overwhelmed. Instead — focus on what you really want to do, and then align you skills to support the more specific ‘focus’. This tends to help narrow the field and eliminates the risk of moving into a position that you ‘could do’ but actu
Featured White Paper(s)
Harness Your Data for Better, Faster Decision-Making
read more)