-4

I know this question is asked so many times but some issue is still not clear to me. Please don't mark it as duplicate. I am little bit confused with View. Is View create a table internally or execute sql query internally when I select data from view? I know view treated as virtual Table and using view I can also do DML operation. But if View execute sql query internally every time when I select data from View, then what is the requirement of adding extra additional layer. In View I can do complex join query and in stored procedure(SP) also I can do.

So I mean, in place of View, I can easily use direct SP without doing extra effort by creating View and again SP for that View and calling from c# back end.

I mean, using View it will be faster or not, I dont know. It may be as it is treated as virtual table, please clarify those things. 

Example:

CREATE TABLE EMPLOYEE(Emp_ID INT PRIMARY KEY CLUSTERED,Address VARCHAR(50) NOT NULL)

INSERT INTO EMPLOYEE VALUES(1,'West BEngal');
INSERT INTO EMPLOYEE VALUES(2,'Bihar');
INSERT INTO EMPLOYEE VALUES(3,'MP');

VIEW:
---
CREATE VIEW vw_all_Employee
AS
  SELECT Emp_ID, Address FROM EMPLOYEE;

SP1:
---
CREATE PROCEDURE sp_select_All_Employee
AS
BEGIN
    SELECT Emp_ID, Address FROM EMPLOYEE;
END

SP2(On VIEW):
----------
CREATE PROCEDURE sp_select_All_Employee1
AS
BEGIN
    SELECT Emp_ID, Address FROM vw_all_Employee;
END

My question is: I will use the SP from c# back end, so which SP will be faster? SP1 or SP2?

Han
  • 3,052
  • 2
  • 22
  • 31
Sopan Maiti
  • 189
  • 1
  • 11
  • 2
    a sproc is code. it can do queries, or it can do anything else. a view is basically a stored QUERY, and can be used as a "virtual" table. you can't compare them, because they're totally different things. – Marc B Aug 05 '15 at 14:26
  • 2
    Side note: [`CREATE PROCEDURE`](https://msdn.microsoft.com/en-us/library/ms187926.aspx): "Avoid the use of the **sp_** prefix when naming procedures. This prefix is used by SQL Server to designate system procedures" – Damien_The_Unbeliever Aug 05 '15 at 14:28
  • Depending on your version of SQL Server you can also have a indexed view which stores the results of the query so it is faster to query but does impact updating the table(s) it is made up of. – Mant101 Aug 05 '15 at 14:38

2 Answers2

0

A View is more specific to handle some portion of data. SPs are more flexible. Both of them are using query optimization so in your case I think there will be no performance issues. You can read similar post about differences between Views and SPs.

Michael
  • 1,027
  • 10
  • 22
0

I think of a view as a filter, or a window, for the data. It can be used to present a restricted, simplified, or otherwise customised "view" of what's in the database.

You can probably do what you're doing either way but, basically, a view is a reusable way of looking at a set of data, and a stored procedure is a reusable set of actions.

pinginrua
  • 21
  • 4