What is the difference between views and stored procedures?
Can we have input parameters for views?
Answers were Sorted based on User's Feedback
Answer / ashutosh jha
both can never be compares, as both have totally different
approached,
Well a view is a output of a query ,and makes a virtual
image of the table,and the input parameters are not accepted.
sp is a group of sql statements that are pre-executed and it
accepts the parameters.it reduces network traffic, gives
faster performance, etc.
Is This Answer Correct ? | 42 Yes | 2 No |
Answer / chandana sriram
Well both of them aren't even compatible to compare and
tell the difference. They both belong to different paradigm
of database world. Anyways, here are the definitions I
would give you. A stored procedure is a set of sql
statements which can be called a subroutine to access
relational database systems. Its actually stored in a
daatabase. They are mainly used for faster access(Pre-
compilation of SQL statements) ,Simplification of data
management and for security.
A view as my friends have elucidated above is a virtual or
logical table that holds result set of a pre-defined query.
The data for these views are not stored in the database.
There are two types of view read only and updatable. You
can modify a read only view with an INSTEAD OF trigger.
Hope that makes sense :-)
Is This Answer Correct ? | 20 Yes | 8 No |
Answer / grish varshney
views & stored procedure are basically different appraches.
both have their different purposes.
view is a virtual table. which have no data atall.which
difination stored in oracle system catalogue . whenever a
call is made every time the defination is scanned & a new
virtual table is made over the base table.
while stored procedure are the precompiled statements
which can accept arguments..
Is This Answer Correct ? | 6 Yes | 3 No |
Answer / sandeep
At times, the database administrator might want to restric access of data to different users. They
might want some user to be able to access to all columns of a table wheras other users to be able to access only selected columns.The sql server allows you to create views to restric user access to the data.Views also help in simplifying query execute when the query involves retrieving data from maltiple tables by applying joins.
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / indhu karur
View:
V can define a select-query that retrieves
specific information from one or more tables
Sp:
A stored procedure can do much more: it can run
multiple sql-commands, has control-of-flow statements and
accepts
parameters
Is This Answer Correct ? | 5 Yes | 4 No |
Answer / mukesh
All above answers are exactly correct.
In a different approach my answer is,
View is for Static approach. We cannot create a view with dynamic values.
SP is for Dynamic/Run-time values.
Is This Answer Correct ? | 0 Yes | 0 No |
how would you write a sql query to compute a frequency table of a certain attribute involving two joins? What changes would you need to make if you want to order by or group by some attribute? What would you do to account for nulls?
Consider the table T1 it consits A,2,3,4 consider another table t2 it contain 1,2a,3a,4a now i need o get the upadted answer
Difference between group by clause and having clause in SQL?
What is the default Port No on which SQL Server listens?
What is the use of DBCC commands?
What are the different ways you can create databases in sql server?
What is Stored Procedure? What is Views in sql server? Difference between a User Defined Function and a Stored Procedure Difference between a primary key and a unique key? What is a join and explain different types of joins. Difference between temp table and table variable Difference between Triggers and Stored Procedures Difference between UNION ALL Statement and UNION What is COALESCE / Why do we use COALESCE? Why we use SET ROWCOUNT in Sql How many clustered index can have a table How many types of local tables in SQL SERVER Difference between DELETE and TRUNCATE What is Aggregate Functions? What is Row_Number()? What are Ranking Functions? What is NOLOCK? What is CTE? What are the Advantages of using CTE? What is the STUFF function and how does it differ from the REPLACE function? What are the difference between clustered and a non-clustered index? What are the different index configurations a table can have? Difference between a HAVING CLAUSE and a WHERE CLAUSE? Difference between SET and SELECT Provide all the built in string function of SQL SERVER Difference between char and varchar data types Define candidate key, alternate key, composite key. What are constraints? Explain different types of constraints. What is a self join? Explain it with an example. How will you convert table row to a column comma separated value
How to create a stored procedure with a statement block in ms sql server?
Explain what is lock escalation and what is its purpose?
What are the 2 types of classifications of constraints in the sql server?
CLR Integration ? what is Notification services ?
What is the minimum and maximum number of partitions required for a measure group? : sql server analysis services, ssas