Mickey's T-SQL Ponderings

Sharing my tidbits of knowledge on T-SQL, SSRS, and whatever catches my fancy.

T-SQL Tuesday #51- Don’t Crap Out While Betting On Table Functions

| 4 Comments

My good friend Jason Brimhall (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009. As a compliment to the upcoming debut of the Las Vegas SQL Saturday, Jason has taken up a betting theme. He wants to know our stories of when we bet it all on a risky solution and won or lost.

Instead of telling you about the past, I want to help you win big at the table today. I really don’t want you to crap out while betting on the wrong table functions.

Snake Eyes

There are two types of table functions Multi-line Table Functions and In-Line Table Functions. There is a huge difference between the two of them.

Multi-line table functions sound great. You write as much code as you need in them and they will return all the data in a table variable. This is where the weighted dice rolls snake eyes every single time. You see, the statistics for a table variable always, always says there is only one row in the table being returned. It doesn’t matter if there are a hundred, a thousand, or a million rows. The statistics will say one. Which means the optimizer has a good chance of loosing when it picks the execution plan for that query.

Let’s Take a Look at the Bets

For my example, I have a simple query that returns 43 rows out of a Tally table. Notice that the index estimates 43 rows will be returned, which is great, because that is exactly on the money!

TSQLTuesday51_1

 

If we put that same query inside of a multi-line table function, we get an estimated number of rows of 1 (snake eyes!).

 

TSQLTuesday51_2

 

TSQLTuesday51_3

 

Double Down

An in-line table function will return the same result set, but there are some limitations on its construction. The entire query within the in-line table function needs to be done in only one statement.

Note: You can get very creative with Common Table Expressions (CTE) if need be.

There are two benefits to using an in-line Table Function. One, is that the Estimated Number of Rows will be accurate (or as accurate as the statistics on the table), and two, the “inside” of the in-line table function is not masked in the Execution Plan. It is plopped right into the middle of the calling query. (Yes, “plopped” is a technical term. )

 

TSQLTuesday51_4

 

TSQLTuesday51_5 

Last Call

SoRemember to double down on in-line table functions and don’t crap out on the snake eyes of the multi-line table function.

Thanks for all the fish

Thanks go out to Jason Brimhall for hosting this month’s T-SQL Tuesday blog party. Please visit his website at http://jasonbrimhall.info/, or better yet come to Las Vegas for their SQL Saturday and thank him in person.

Author: Mickey Stuewe

I love all things T-SQL. I'm a database architect, BI developer, and a craftswoman. I love watching action movies and reading fantasy books. I'm a wife and a mother of two beautiful daughters. I love laughing and I laugh often. Just ask my family.

4 Comments

  1. Pingback: T-SQL Tuesday #051: Bets and Results | SQL RNNR

  2. Pingback: T-SQL Tuesday #051: Bets and Results - SQL Server - SQL Server - Toad World

  3. Mickey, thanks so much for this great tip. I’ve been writing SQL functions for over a decade and I never knew this. And thanks also for writing a proactive “bet”. The other disaster stories have been fun to read, but this is the only one so far that will actually impact my daily life!

    • You’re very welcome VG! I love it when I help people. :)

      That is one of the fun things about the T-SQL Tuesday Challenge. You get to interpret it however you want.

      –Mickey

Leave a Reply