SP or View!! Pls Help

[Forri]

Hi,

I'm new to SQL Server and i would like to ask what is normally used to create a select all statement...a Stored Procedure or a View? What exactly is a View and how would one use it?

Also how can i execute a view in Query Analyzer?


Tahnks
Nick

 

[mrdenny]

If you need to select all the columns from a table you don't need a view or a stored procedure. Simple run your select statement within Query analyser.

A view is nothing more than a virtual table that actually have one or more tables behind it.

To view the data within a view you call it just like a table.

A stored procedure is a series of sql commands that are processed. Including select, insert, update, delete and conditional logic.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[Forri]

Ok fair enough....

so if i would like to view a list within my VB.net project would i use an SP, a view or a simple query within a command?

Does a view support joins and stuff or?


thanks again
Nick

 

[hmckillop]

A stored procedure would be the best way as it implements any security you wish to have in place for the database, also they are compiled after first use and are generally the most efficient way of working.
I would recommend using the Stored Procedure choice.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[SQLSister]

Views are less efficent that sps unless they are indexed views (only available in Enterprise Manager). I do not allow any access to my databases except through sp's for the security reason and because they are generally (not always) the most efficient way to go.

Questions about posting. See faq183-874

 

[mrdenny]

All 3 options would be available to you. The preferred option from a security and efficienty standpoint combined would be the stored procedure method.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[SQLSister]

You talk about selecting all the records. Remember don't use Select * if you can help it. Never use Select * if you have an inner join as it sends more data over the network than you need (by definition in an inner join at least two columns contaion the same information). Only ask for the minimum columns you need.

Also always try to have a where clause unless you really need all the records. You never want to return more records across the network that you will actually be using at the other end.

Don't get stuck in the trap of many nondatabase programmers of figuring you can reuse code if you select all the columns and records. Code reuse is a bad idea if it makes the database less efficient. In accessing databases, anything that improves even slightly the performance of a query is the way to go, no matter how much extra code you might have to write. Small differnces in how you write code can savce tremendous amounts of database and network resources making the overall system work faster for all theusers. Inthe web arean this is even more critical as you can;t even calculate how many users might be logged on simultaneously.

Sorry for the rant, but it seemed from your question that writing code for databases is new to you and I'm trying to save you from some common traps.

Questions about posting. See faq183-874

 

[Forri]

I thank you all for your views (opinons!)

Well, i'm not new to SQL and i know what i have and have nto to do! I was asking about the views etc because i was in discussion with a SQL SERVER "EXPERT" and he telling me that putting everything as stored procedures was a bad idea. I persoanlly never used Views but he kept on telling me that reports and Grids shoudl be populated through a View!

But i think i stck to my SPs!

Tahnks once again
Nick