View vs sp vs functions

  • Dear Experts

    I want to know differences between views , stored procedures and functions

    And when to choose the appropriate one

    Thanks lot

  • Sounds like you are asking for answers to a homework question.

    Use GOOGLE to search for your answers. If you do, the following are typical of the answers you will find.

    1). A view is a virtual table that consists of columns from one or more tables. Though it is similar to a table, it is not stored in the database.

    2). Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e. no INSERT or UPDATE statements allowed).

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Read Books Online. They are a tremendous resource and are free with the product.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • To add to what the others have already stated...

    If you write a function as an iTVF or INLINE Table Valued Function, it will work just like a View except for one important factor... the iTVF will take parameters where a View cannot. That can work to a huge advantage if the result set is used as part of the join and the columns being joined on are calculated. A CTE would work in a similar fashion.

    The result sets of stored procedures can't be used in a FROM clause directly. You have to do a trick with OPENROWSET which has it's own privs and parameterization problems.

    That's just scratching the surface, though. As the others have suggested, you really need to hit the books on this question. One could write an entire book on the differences and when to use each for what.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • bitbucket-25253 (1/25/2014)


    1). A view is a virtual table that consists of columns from one or more tables. Though it is similar to a table, it is not stored in the database.

    Unless it's an indexed view, which is fully materialized.

    2). Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e. no INSERT or UPDATE statements allowed).

    At least not directly or using anything conventional or proper. There is a hack where you can use OPENROWSET to run a proc from a function and that proc can make changes to tables. Since you can't make it take parameters (no dynamic SQL or global temp tables allowed in functions), I can't see much use in doing such a thing unless it was to preload some other variables (scalar or table valued) based on conditions outside of the function.

    I could maybe see using it to filter the return of something like sp_Who2 or sp_Lock but I'm also thinking there would be better ways to do that since OPENROWSET requires some pretty hefty privs.

    Of course, since that's also undocumented functionality, it could change but I think MS is too busy coming up with new features and probably wouldn't touch this "feature".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • zi (1/25/2014)


    Dear Experts

    I want to know differences between views , stored procedures and functions

    And when to choose the appropriate one

    Thanks lot

    Since it was appropriately brought up, I have to ask the following question and I do so without any sarcasm or irony because the last 20 people that I interviewed for SQL Dev or DBA positions didn't actually know.

    Do you specifically know what "Books Online" is and how to "get there"?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (1/26/2014)


    zi (1/25/2014)


    Dear Experts

    I want to know differences between views , stored procedures and functions

    And when to choose the appropriate one

    Thanks lot

    Since it was appropriately brought up, I have to ask the following question and I do so without any sarcasm or irony because the last 20 people that I interviewed for SQL Dev or DBA positions didn't actually know.

    Do you specifically know what "Books Online" is and how to "get there"?

    Are you serious?

  • Lynn Pettis (1/26/2014)


    Jeff Moden (1/26/2014)


    zi (1/25/2014)


    Dear Experts

    I want to know differences between views , stored procedures and functions

    And when to choose the appropriate one

    Thanks lot

    Since it was appropriately brought up, I have to ask the following question and I do so without any sarcasm or irony because the last 20 people that I interviewed for SQL Dev or DBA positions didn't actually know.

    Do you specifically know what "Books Online" is and how to "get there"?

    Are you serious?

    Absolutely. It's been a ridiculous set of interviews. Considering the lack of knowledge even in the most simple areas of SQL Server, I don't know how some of these people actually kept any job for longer than 5 minutes. For example on the Dev interviews, about 80% of the candidates didn't even know how to get the current date and time using T-SQL.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (1/27/2014)


    Lynn Pettis (1/26/2014)


    Jeff Moden (1/26/2014)


    zi (1/25/2014)


    Dear Experts

    I want to know differences between views , stored procedures and functions

    And when to choose the appropriate one

    Thanks lot

    Since it was appropriately brought up, I have to ask the following question and I do so without any sarcasm or irony because the last 20 people that I interviewed for SQL Dev or DBA positions didn't actually know.

    Do you specifically know what "Books Online" is and how to "get there"?

    Are you serious?

    Absolutely. It's been a ridiculous set of interviews. Considering the lack of knowledge even in the most simple areas of SQL Server, I don't know how some of these people actually kept any job for longer than 5 minutes. For example on the Dev interviews, about 80% of the candidates didn't even know how to get the current date and time using T-SQL.

    Too bad the companies you are doing the interviews for insist on butts in seats. Of course I am not looking for a new position at the moment since I still have my job with my current company (since the beginning of December) and will be coming back to Afghanistan for another six months after my R & R in February.

  • The reason why I'm the one doing the interviews is because these particular companies don't just want "butts in seats". They actually want people that know what they're doing and are willing to wait to find the right people.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Since it was appropriately brought up, I have to ask the following question and I do so without any sarcasm or irony because the last 20 people that I interviewed for SQL Dev or DBA positions didn't actually know.

    Do you specifically know what "Books Online" is and how to "get there"?

    Um, wow, just wow!

  • Jeff Moden (1/27/2014)


    The reason why I'm the one doing the interviews is because these particular companies don't just want "butts in seats". They actually want people that know what they're doing and are willing to wait to find the right people.

    I understand why you are doing the interviews, that part makes sense. If they were willing to work with someone remotely and I was still actively looking for work I would be interested in such work for reasons I think I told you about previously.

    Getting to come back to Afghanistan for another 6 months (and hopefully I will be able to extend until the middle of December as another 3.5 months Afghan pay would be awesome) and it looking that I will still have a position with my company State side (the company has 3.5 DBAs company wide).

Viewing 12 posts - 1 through 11 (of 11 total)

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