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."

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."

I want to know differences between views , stored procedures and functionsAnd 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."

I want to know differences between views , stored procedures and functionsAnd 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"?

I want to know differences between views , stored procedures and functionsAnd 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."

I want to know differences between views , stored procedures and functionsAnd 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."