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
  • 85,870
  • 40
  • 177
  • 264
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
  • 85,870
  • 40
  • 177
  • 264
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
  • 45,329
  • 25
  • 128
  • 183
-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
  • 768
  • 1
  • 17
  • 32