STRING_SPLIT() vs User Defined Table Types - Conclusion

I don't think I have tested for every scenario possible, but it is better than only a single test. With that being said, I'm a little surprised at the result. I thought that User Defined Table Type would win in every scenario but it is clear that in some cases STRING_SPLIT() function is actually faster. Just like most other things in SQL Server the answer is: It Depends!

In the case of STRING_SPLIT() function Engineers at Microsoft did amazing job optimizing it over other custom solutions that people had to resort to in the past. Of course there are limitations with it, that need to be acknowledged and taken into consideration when designing a solution. In my opinion this could be quick go to solution that is great for initial testing but needs to be heavily tested when it comes to final solution for production environment.

Just like I mentioned in previous post, estimated row count always shows 50 rows and value returned from the function is a string and needs to be converted to target type. This did not prove to be large issue in my testing but in complex execution plans it could throw query optimizer into wrong plan when getting data from other tables which could cause major slow downs.

In my test scenarios outlined below, User Defined Table Type Won 4 out of 6 times. I'm sure I could have setup other scenarios where it would lose every time but in this case it won. Leave comments if you want to see full source code or want me to run any other scenarios. Thanks for reading!

---

 
SELECT   a.*
        , CASE WHEN ( MAX(a.AvgDuration) OVER ( PARTITION BY RowCnt ) ) = AvgDuration 
  THEN 'LOOSER' ELSE 'WINNER' END AS RoundDecision
  , ROW_NUMBER() OVER ( ORDER BY RowCnt, SPName ) AS RowNumber
FROM     (
            SELECT    SPName
                    , RowCnt
                    , COUNT(*) AS NumberOfExecutions
                    , MIN(Duration) AS MinDuration
                    , AVG(Duration) AS AvgDuration
                    , MAX(duration) AS MaxDuration
            FROM      dbo.SPExecutionTime
            GROUP BY  SPName
                    , RowCnt
        ) a
ORDER BY RowNumber