View vs sp vs functions

Question

Post reply

View vs sp vs functions

zi

SSCrazy

Points: 2387
More actions
January 25, 2014 at 11:57 am

#288814

Dear Experts
I want to know differences between views , stored procedures and functions
And when to choose the appropriate one
Thanks lot

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

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]

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 3

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)

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 4

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)

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 5

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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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?

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 7

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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

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.

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 9

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)

David.Lester Mr or Mrs. 500 Points: 551 More actions · Answer 10

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!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

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