1

Today I was talking to colleagues about the use of inline table valued functions in SQL Server, why I recommend using them and in what scenarios they are useful.

It occurred to me that they do not have to have any input parameters but I couldn't think of any scenarios of when you would want to do this and it be of any benefit over a view, are there any?

mheptinstall
  • 2,109
  • 3
  • 24
  • 44
  • One important difference is that you can always add a default parameter to a table valued functions. Also, there may be a subtle difference in how the plans are cached. If I am recalling correctly, views are compiled on their first execution but table-valued functions are compiled when the query calling them is compiled (and the plan may be cached along with the query). However, that might be off a bit. – Gordon Linoff Apr 08 '16 at 20:01
  • I can't think of any, and I would strongly argue in favor of views in this situation, as inline table-valued functions are more complex and require more resources – quest4truth Apr 08 '16 at 20:02
  • @user3481891 can you explain why an itvf is more complex? And why do you say they require more resources? What resources do they require? I am curious of the facts behind either of these statements. – Sean Lange Apr 08 '16 at 20:10
  • Sure. An itvf entails more structure to accommodate parameters. More structure is more complexity. More complexity means more memory, CPU and disk space. It may be that defining an itvf without parameters simply signals SQL Server to compile it to a view, in which case the additional resources would only be used at compile time. The additional complexity would then be only a matter of an additional pointer, but there are always more resources and complexity involved in an itvf than a view. Where it applies depends on where MS implements it. If you only need a view, only define a view. – quest4truth Apr 12 '16 at 14:53

1 Answers1

0

There really should be no reason why you wouldn't want to have your data sitting in a table or view somewhere ... unless you were to want to have some magic values or something which would only be available to your application, and you were perhaps encrypting the function?

Check out Encrypting and Decrypting SQL Server Stored Procedures, Views and User-Defined Functions for just such an example*. It's an example, and it's actually a pretty good one, when it comes down to it.

  • (although please, do not encrypt your passwords using SHA2_256 - use something without known vulnerabilities)
Community
  • 1
  • 1
David T. Macknet
  • 3,112
  • 3
  • 27
  • 36
  • You can encrypt the view as well as the function – quest4truth Apr 12 '16 at 16:42
  • True - but a function can perform operations - inputs / outputs - where a view can't, necessarily. I'm not saying this is a good idea, mind you, but I can see where the inclination would be to hard-code variables within functions and then encrypt those functions so as to obfuscate the inner workings of the scripts - plus, as functions, they don't have to operate upon data within some table somwhere. – David T. Macknet Apr 12 '16 at 18:58
  • But the OP was asking about using the function *without* the inputs, and neither views nor functions take output parameters. The hard-coded values work in views as well. In fact if you don't use parameters, you would define them in the same place. – quest4truth Apr 12 '16 at 21:51
  • I *guess* I can see where you're going with that. OTOH: hard-coding values seems more something to end up in a stored procedure or a function, to me, than it would to end up in a view. Maybe this is a matter of preference? I don't know - I found the only halfway-decent example out there & it seemed to make a wee bit of sense - not that I could see myself doing it, in any event. – David T. Macknet Apr 12 '16 at 21:58