SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


View vs sp vs functions


View vs sp vs functions

Author
Message
zi
zi
SSC-Addicted
SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)SSC-Addicted (455 reputation)

Group: General Forum Members
Points: 455 Visits: 338
Dear Experts

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

Thanks lot
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15225 Visits: 25280
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

Before posting a performance problem please read
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31138 Visits: 8670
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
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207680 Visits: 41965
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207680 Visits: 41965
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207680 Visits: 41965
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91708 Visits: 38953
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?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207680 Visits: 41965
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91708 Visits: 38953
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207680 Visits: 41965
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search