Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-04-02 : 08:53:36
|
| Rich writes "using windows xp, sql 2000 + sp3ain our software there are several situations where we have a view with all possible records (all clients). we then, using c#.net and ado.net, select from the view the records we need. my question is. how does SQL deal with this: For instance does sql have to execute the view to get all them records and then select from all them records the one's we need. that is how i would see it working and if so would it not be best to put a straight select into a stored procedure and use parameters to get the records. if not then how does it handle it??forgive me if this sounds simple.Cheers,Rich" |
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2004-04-02 : 09:46:24
|
| I think when you use the VIEW, (as in attempt to select it or join it with another table), SQL Server runs the VIEW code (on the fly). If you query the Information_Schema.VIEWS you will see that it has a VIEW_DEFINITION column. (Incidentally the Information_Schema.VIEWS is not a table!!) No actual data is stored froma VIWE, it only acts as a filter on the targeted tables (in the VIEW definition)Whether it's a good idea to have a VIEW in your particular case is hard to say. First of all, how complex is your VIEW? Who is going to see the result of your VIEW? Do you have sensitive data you want to hide (from your various client information?).Sorry, but it's hard to say or no. You have many variable situations and that is something you need to consider.Hope this helps.________________Make love not war! |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-04-02 : 10:54:58
|
quote:
Originally posted by AskSQLTeam
so would it not be best to put a straight select into a stored procedure and use parameters to get the records.
Yes. Going against the base table is the most prefered, along with usinfg the sproc...Even in a partitioned view, it's still better to go against the base if you can...MOOBrett8-) |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-02 : 19:56:31
|
| A possible exception is an indexed view where you all the data you need for your FROM and WHERE clauses are stored in the indexes on the view. This will be more efficient if the base table has no such indexes. Indexed views can also be more efficient if you can only join to the view instead of joining to many tables underneath.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
Selecting from a view