1

I have been asked this question in one of the interview and thought it worth sharing here.

I an using Sql Server 2008 with ASP.Net, My requirement is to select tables data without any condition, the option are below,

  1. Stored Procedure

  2. View

I have checked this question also, but unable to get the exact answer i am looking for.

Which one should i choose to perform only select query without any condition and why in terms of Security, Performance etc.?

Community
  • 1
  • 1
Sujit
  • 3,677
  • 9
  • 41
  • 50

4 Answers4

1

This link has most of the things you would need:

What is the difference between a stored procedure and a view?

For above scenario i guess you should go ahead with View, cause view would be just a snapshot of the table and does not expose the entire table, hence faster and secure. View is just a representation and source of data where as stored procedures are much more power full when it come to transactions on tables and view...

Community
  • 1
  • 1
Sheetal
  • 1,368
  • 1
  • 9
  • 15
1

Stored procedures offer the best performance gains over either a view or a regular SELECT statement.

Why?

1) When a regular SELECT statement is passed to the database it must be compiled and then executed, which adds overhead.

2) The compiled and executable plans are same for both SELECT statements and Views.

3) Stored Procedures are precompiled for faster execution

RAJESH KUMAR
  • 497
  • 4
  • 13
  • There were performance differences between stored procedures and ad-hoc SQL back in, roughly, the SQL Server 6.5 era. SQL Server has moved on a fair bit since then and both ad-hoc SQL and stored procedures benefit from cached plans. – Damien_The_Unbeliever Jul 19 '13 at 06:38
1

I would use a view. You've also missed out an option - a table-valued function.

There are 3 possibilities here - a view, a table-valued function, or a stored procedure.

A view is basic - it cannot be parameterised. But it can easily be composed as part of a larger query and the optimizer can (usually) be expected to produce as optimal a plan as if you had included the body of the view in your query (indeed, it's often liked to a macro which "expands out" into your query).

A table-valued function may be parameterised, but cannot produce any side effects in the database. However, it can still be composed into queries, and (if it's an inline TVF, and you have a tail wind) the optimizer can still, hopefully, produce an optimal plan.

A stored procedure is all powerful - it can do anything, it can make changes to the database, it can compute results however it needs to. However, your only option is to execute it. It's execution takes place within a separate scope, and it cannot be composed into a larger query.

So I'd recommend using the least powerful of the above that meets your needs - because it provides the most scope for being able to reuse it as part of other queries later.

Re: Performance - if you're just doing a plain SELECT * against a view or are just executing a stored procedure, there shouldn't be any noticeable difference.

Re: Security - again, not much difference - you can as easily GRANT/DENY SELECT on a view as you can EXEC on a stored procedure.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
0

If your query is to select all the data in the table, there is no optimization that can be gained by using a store procedure because in all probability it does a complete Table Scan as it wont use any indexes. You could make it use a NC index by putting in a dummy where clause on the NC index column like "Where INDEX_WhatEverNameIs > 0"

A view would be used if i have some kind of restrictions in terms of who should see it. In this case you could answer as View because you could choose an Indexed View which would store this in memory and serve out same every time.

lloydom
  • 377
  • 2
  • 11