## Inline Table Valued Function

 Author Message Ed Wagner SSChampion Group: General Forum Members Points: 10333 Visits: 9602 Excellent - thanks very much for the tip. I'm going to have to try this one. Tally Tables - Performance PersonifiedString Splitting with True PerformanceBest practices on how to ask questions soulchyld21 Grasshopper Group: General Forum Members Points: 24 Visits: 50 Thanks everyone, I eventually managed to solve it and will stick the code up sometime soon, Hopefully it will be acceptable :-D ChrisM@Work SSCrazy Eights Group: General Forum Members Points: 9025 Visits: 19036 Sean Lange (6/6/2013)[quote][b]... In lieu of using a permanent table I use a view that creates the tally table on the fly. 0 reads to access it and I don't have to remember the syntax over and over....It works well as an iTVF too!`ALTER FUNCTION [dbo].[InlineTally] (@RowCount INT)RETURNS TABLE WITH SCHEMABINDING ASRETURN( WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), E2(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c), -- 10 x 10 x 10 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b) --1000 x 1000 rows max SELECT TOP (@RowCount) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4)GOSELECT * FROM dbo.InlineTally (100000)` “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps Sean Lange SSCoach Group: General Forum Members Points: 16639 Visits: 17026 ChrisM@Work (6/7/2013)Sean Lange (6/6/2013)[quote][b]... In lieu of using a permanent table I use a view that creates the tally table on the fly. 0 reads to access it and I don't have to remember the syntax over and over....It works well as an iTVF too!`ALTER FUNCTION [dbo].[InlineTally] (@RowCount INT)RETURNS TABLE WITH SCHEMABINDING ASRETURN( WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), E2(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c), -- 10 x 10 x 10 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b) --1000 x 1000 rows max SELECT TOP (@RowCount) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4)GOSELECT * FROM dbo.InlineTally (100000)`That is pretty cool too. Nice to have either option. _______________________________________________________________Need help? Help us help you. Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.Need to split a string? Try Jeff Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)