20

Anyone have a good decisioning tree for deciding when to use a view and when to use a table-valued function in SQL Server?

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
Striker
  • 567
  • 1
  • 4
  • 12
  • 2
    Maybe, I could re-phrase the question. I can easily accomplish what I need to do by using either a view or a table valued function. Is the rule of thumb to use a view then? – Striker May 13 '11 at 14:36
  • 1
    Yes, if its a real ordinary view and you aren't using fancy tricks like OPENROWSET or CONTEXT_INFO. – Cade Roux May 13 '11 at 14:42
  • 5
    See also [table-valued function vs view](http://stackoverflow.com/questions/4960137/table-valued-function-vs-view/4960468#4960468) – Martin Smith May 13 '11 at 19:10
  • 9
    JonH, if people didn't compare things which appear to do the same thing, then they wouldnt know where to use one over another. A pig headed comment If ever I saw one. His question is perfectly valid – brumScouse Jul 28 '13 at 20:48

3 Answers3

13

Although any view can almost trivially be converted to an inline table-valued function, the converse is not true.

If the construct needs to be parametrized, then use an inline table-valued function. Inline table-value functions are basically parametrized views in terms of the optimizer being able to combine them with views and push things around. Multi-statement table-valued functions are not at all like inline table-valued functions.

If you cannot do it with an inline table-valued function, use a multi-statement table-valued function.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
2

There's certain things you can't do in a view (such as table variables, intermediate results before you return your result-set, etc.) ... if you don't need those, view, if you do, sproc/udf :-)

Joel Martinez
  • 46,929
  • 26
  • 130
  • 185
-1

Ok, I'll give the use we do. We have the tables, but we never access the tables, but the views about the tables. It's just a security issue.

elvenbyte
  • 776
  • 1
  • 17
  • 34