| Author |
Topic |
|
andrthad
Starting Member
19 Posts |
 Posted - 2005-12-01 : 18:45:34
|
| Hi All,Novice question. Would someone explain tell me what a view is used for? Also I am confused about the difference between a function and a stored procedure. They both seem like functions to me. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-12-01 : 19:02:04
|
| view -- a stored SELECT statement. stored proced -- a stored T-SQL script that accepts parameters, "does" something, and also "returns" something. a stored proc may update, add, or remove data or database objects; it can pretty much "do" anything.function -- a stored t-SQL script that accepts parameters and "returns" something only; it cannot change the state of any data in the database. |
 |
|
|
andrthad
Starting Member
19 Posts |
Posted - 2005-12-02 : 09:59:03
|
| Wow. Thank you so much. Very good definitions. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-12-02 : 10:03:57
|
| Oh, by the way, Views are useful for:1) Abstracting your table structure. You can create different views that do common joins and hide your underlying table structure as needed, and give users only access to those Views (i.e., if they need to write reports). This way you can change table structures, or hide internal column values, from regular users and also change these items w/o having to re-write all of the reports or SQL statements that access your data (assuming you keep the views the same).2) Making your stored procs much shorter and clearer; common joins between tables can be done using Views which can make your stored procedures much shorter and more readable and easier to maintain.3) Implementing row-level security; you can write views that filter the data returned based on who is logged. (Though this requires a bit of planning and can be a little tricky). i.e., user "John" might only be able to see data for company XYZ, while user "Amin" can see data for all the companies. Using views can make this possible.Views are a *very* under-utilized feature of SQL Server ... |
|
|
|
andrthad
Starting Member
19 Posts |
Posted - 2005-12-02 : 11:01:52
|
| Thanks for the followup. I actually thought about having you explain the use of views a little bit more, but didn't want to bother you too much ;-)Would you say people typically use stored procedures more than functions since functions can't update/add/remove? |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-12-03 : 13:41:18
|
| Stored procedures are used quite a bit more. They are generally considered the "core" of database programming in SQL Server. The functions can help to modularize certain code or formulas; however, you should be careful with this and watch the performance impacts carefully.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2005-12-03 : 14:34:27
|
| Hope this helps in additon to the aboveSP are very good, they are a useful way of building a 3tier system, they can accept input from the calling application (ie a web page, vb app) and pass then to the db, or do some useful work and then return a return value to the web page.Taht way, users dont interact directly with your db.It allows for security, speed and consistent programs. hence u dont have a write the same app for multiple pages, just call it in your page and it does your work and updates, selects, inserts or deletes basically does WORKhope this helpsAfrika |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-12-03 : 15:43:21
|
| Just building on Jeff's note.4) Views can be used to provide complex constraints (business rules), that cannot be easily achieved using tables alone.As for table valued functions.. These are good for queries that require a certain amount of control over the query processor. By that I mean, in a complex query, the table valued functions are processed "first" then the result set from this function is integrated into the rest of the query.Stored procs provide an excellent API/security layer for calling code.DavidMIntelligent Design is NOT science.A front-end is something that tries to violate a back-end. |
|
|
|
|
Views / Stored Procedures / Functions