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 |
|
Kenny Blankenship
Starting Member
25 Posts |
 Posted - 2004-07-27 : 15:37:13
|
| I have something of a philosophical question. At what point is it better to use a view versus a table variable? I have a query that will return about 1,000 records. I put it in a function to make it more dynamic and so I could run a loop against it, but I am considering converting it to a view. The only thing is, if I convert it to a view, I will need 10 different views. The current SQL function returns 10 different tables with about 1,000 records each. What's a good rule of thumb, if there is one? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-07-28 : 01:36:52
|
| Probably best to leave this as a table variable. The view will access the underlying tables for every call which in your loop could impact other processes. Make sure the table variable holds the minimum amount of table so that it uses the min amount of memory.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Kenny Blankenship
Starting Member
25 Posts |
Posted - 2004-07-28 : 07:01:11
|
| Thanks for the response. I was not sure if a view would be less resource intensive than a table variable or not. The function that I have right now is executed quite often, so I thought a view might be better use of resources. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-28 : 08:49:33
|
| Just a thought, if it runs drastically different, you might want to have multiple functions for each "type" of call each using a table variable. This would mean the function never has to recompile and is much more efficient. Using a combination of this and a table variable, which will utilize memory space better than a view for small recordsets many times, might be your best overall tradeoff. Test it out.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kenny Blankenship
Starting Member
25 Posts |
Posted - 2004-07-28 : 14:07:32
|
| Thanks for the idea. I will try them. The SQL stays the same for each call. I just pass different values to the WHERE clause.While I'm here, must you create a Rank column to return a particular record from recordset if you only know its position in the recordset? Or, can you select the, for example, the 11th record from a recordset? If so, how? |
|
|
|
Pat Phelan
Posting Yak Master
187 Posts |
Posted - 2004-07-28 : 14:46:18
|
You can get creative and finagle it, something voodoo like:EXECUTE ('SELECT TOP 1 * FROM (SELECT TOP 11 * FROM master.dbo.sysprocesses AS p ORDER BY spid) AS a ORDER BY spid DESC')-PatP |
|
|
|
Kenny Blankenship
Starting Member
25 Posts |
Posted - 2004-07-28 : 15:06:07
|
| Thanks, just what I needed. |
|
|
|
|
|
|
|
|
Views versus table vars