1

I work for a public school system and have to create behavior reports for various things. I have about a dozen reports where the data is generated via stored procedures and pretty much retrieve the same base data. I'd like to create a Table-Valued Function so that each report is pulling the same base dataset to work with.

I have a student table that has columns such as schoolYear, Grade, schoolID, etc. The key is enrollmentID. I'm trying to determine which is more efficient, to return all the fields I'll be working with from the student table (schoolYear, grade, etc) or just return the key (enrollmentID) and then join it back with the student table in the query that is calling the function.

On one hand, it seems redundant to join the student table in both the function and the stored procedure that is calling the function. On the other hand, thousands of records are potentially returning and it seems like in the past I've noticed significant lag if I'm trying to return a lot of data from a function.

Is there a best-practice on how to best use table-valued functions? As always your help is greatly appreciated!

Jimmy Genslinger
  • 557
  • 3
  • 21
  • 1
    You could explore setting up an **indexed view** to provide your student data. It can be a bit more performant than a standard view or an ad hoc query as it leverages indexing. Here's a post where I described one in brief: https://stackoverflow.com/a/11250937/119246 – Darth Continent May 29 '19 at 14:26
  • 2
    An inline TVF should have a very small performance hit. The optimizer treats it like a derived table and therefor it's pretty fast to work with. Alas, you didn't supply enough details to determine a preferable solution (and I think if you did, it would too broad anyway for an SO post). If a TVF is required, your best bet is an inline TVF. – Zohar Peled May 29 '19 at 14:31
  • Thanks @ZoharPeled, I think I've been using multi-statement table functions instead of inlines...but now that I've read up on inlines...it can only be a single select statement correct? For example, I have TVFs that selects a user-selected value from a table, and if the user has not selected a value then I do a 'If @@RowCount = 0' to return a default value. I would need to do something creative like a union to turn this into a single select and make it inline, correct? – Jimmy Genslinger May 29 '19 at 15:34
  • yes, only a single select statement (but using a cte is allowed) – Zohar Peled May 29 '19 at 17:49
  • [Here's](https://stackoverflow.com/questions/30326880/case-statement-to-determine-if-i-should-union/30327171#30327171) something you can do to either select from a table or a default value inside a a single select statement. – Zohar Peled May 30 '19 at 05:31

0 Answers0