C#
C#
Microsoft SQL Server
Microsoft SQL Server
Microsoft SQL Server 2005
Microsoft SQL Server 2005

view vs table in sql server

i am using query from C# to sql server. my question is, i can get data from the views as well as tables. which one is better for performence for query? which one is better to use for select query?
please help

Amitava_MukherjeeAsked: 2011-03-08

Who is Participating?

Solutions

LVL 31

Olaf DoschkeConnect With a Mentor Software DeveloperCommented: 2011-03-08

A view by general definition is a virtual table as it selects with more or less complex SQL from one or more real tables. From that perspective you can do both.

A view has the advantage to keep the knowledge about the queries at the database level. You can argue, that the business logic level of the c# application is also a good place for that, but the view can of course be used by many applications connecting to the same database.

Doing stored procedures instead of views could even be a better choice for keeping cahed meta data like the execution plan.

What you can't do is update a view, so you'd need to write changes to the underlying tables.

Pros and cons mainly are about abstraction layers of the application business logic and database/persitence layer. See here for further discussion: http://stackoverflow.com/questions/1004974/use-sql-view-or-sql-query

Bye, Olaf.

LVL 52

Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented: 2011-03-08

It depends what it is a View of. If it is just a subset of a single table then the View will be as quick as the table. Views can be indexed so Views that draw data from multiple tables can be quicker than the same query run inline because the view can be indexed independently of the underlying tables.

LVL 5

mayankagarwalConnect With a Mentor Commented: 2011-03-08

if your view is based on multiple tables, and if you have to put loops in your code to access the data from the multiple tables and for sure view will give you the better performance, becuse the number of hits to the database will be descreased.