Views vs. UDFs vs. Stored Procedures for Data Warehouse Reports

  • Grant Fritchey

    SSC Guru

    Points: 395580

    imani_technology wrote:

    But could the same thing be done with a UDF?

    I'd say no. Not the same way as stored procedures. An inline UDF (heck, even a multi-statement UDF) are treated as tables (similar to views, hence the nickname, parameterized views) within T-SQL code. So not only is what is possible within a procedure vs. a UDF (or view) different, but the methods of calling them, maintaining them, and all the rest, is different.

    One is not objectively superior than the other. They are all tools in the toolbox that do different things in different ways for different reasons. Defining exactly what you need and why helps you define which of the combination of tools is best in a given scenario.

    TLDR: It depends.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing post 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply