Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Inline Table Valued Function Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, June 6, 2013 12:45 PM
 SSCarpal Tunnel Group: General Forum Members Last Login: Friday, December 19, 2014 12:28 PM Points: 4,611, Visits: 4,067
 Excellent - thanks very much for the tip. I'm going to have to try this one.
Post #1460847
 Posted Thursday, June 6, 2013 2:27 PM
 Grasshopper Group: General Forum Members Last Login: Thursday, June 6, 2013 2:25 PM 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
Post #1460879
 Posted Friday, June 7, 2013 1:05 AM
 SSCertifiable Group: General Forum Members Last Login: Thursday, December 18, 2014 1:43 AM Points: 6,890, Visits: 14,254
 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
Post #1460980
 Posted Friday, June 7, 2013 7:40 AM
 SSChampion Group: General Forum Members Last Login: Friday, December 19, 2014 2:05 PM Points: 13,327, Visits: 12,820
 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)
Post #1461079

 Permissions