March 30, 2010 at 1:33 pm
Hello Everyone
Today one of the guys that I work with asked me the difference between a View and a Sproc. I could not easily answer. I just could not put it into words that I was truly confident with.
Can someone point me in a direction, other than the SQL BOL, or explain it to me.
Thanks
Andrew SQLDBA
March 30, 2010 at 2:09 pm
A view is a derived table defined in declarative code. Think of it as being a variable with a name and a value (the value is the data).
A stored procedure is a program. Procedural code that is executed statement by statement.
They are quite different things really. About the only thing they have in common is that SELECT statements are also used in procedures.
March 30, 2010 at 2:58 pm
David Portas (3/30/2010)
A view is a derived table defined in declarative code. Think of it as being a variable with a name and a value (the value is the data).A stored procedure is a program. Procedural code that is executed statement by statement.
They are quite different things really. About the only thing they have in common is that SELECT statements are also used in procedures.
Clarification: SELECT statements can also be used in stored procedures. Stored procedures could just be an UPDATE and/or DELETE and/or INSERT and/or SELECT...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 30, 2010 at 3:01 pm
AndrewSQLDBA (3/30/2010)
Today one of the guys that I work with asked me the difference between a View and a Sproc. I could not easily answer. I just could not put it into words that I was truly confident with.
:w00t:
How about saying the first one is a cataloged query while the second one is a procedural stored block?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 30, 2010 at 3:21 pm
My attempt:
A view is the definition of a single (restricted) SELECT statement. The definition of the view is expanded into any query it is referenced by before optimization.
A stored procedure is a single batch of statements, for which an overall optimized query plan may be cached.
March 30, 2010 at 7:15 pm
Paul White NZ (3/30/2010)
My attempt:A view is the definition of a single (restricted) SELECT statement. The definition of the view is expanded into any query it is referenced by before optimization.
A stored procedure is a single batch of statements, for which an overall optimized query plan may be cached.
I like this - clear and concise 😉
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply