Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

View vs sp vs functions Expand / Collapse
Author
Message
Posted Saturday, January 25, 2014 11:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 26, 2014 1:17 AM
Points: 90, Visits: 239
Dear Experts

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

Thanks lot
Post #1534736
Posted Saturday, January 25, 2014 1:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 5,594, Visits: 24,980
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
Post #1534741
Posted Sunday, January 26, 2014 8:38 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:53 AM
Points: 4,350, Visits: 6,162
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 at GMail
Post #1534789
Posted Sunday, January 26, 2014 12:01 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:13 PM
Points: 36,995, Visits: 31,514
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1534802
Posted Sunday, January 26, 2014 12:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:13 PM
Points: 36,995, Visits: 31,514
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1534804
Posted Sunday, January 26, 2014 12:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:13 PM
Points: 36,995, Visits: 31,514
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1534807
Posted Sunday, January 26, 2014 6:54 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 23,294, Visits: 32,029
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

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)
Post #1534837
Posted Monday, January 27, 2014 6:49 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:13 PM
Points: 36,995, Visits: 31,514
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1534989
Posted Monday, January 27, 2014 7:04 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 23,294, Visits: 32,029
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.



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)
Post #1534993
Posted Monday, January 27, 2014 7:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:13 PM
Points: 36,995, Visits: 31,514
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1534998
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse