Kurt W. Zimmerman (11/4/2013) Jeff Moden (11/4/2013)
mick burden (11/4/2013)
thanks Jeff, it's not something I've used before. What is the advantage? and can the table created be read using VB.net?
The biggest advantage is that you can use SELECT/INTO instead of having to precreate a Temp Table. The advantages there are that you don't have to select all columns and SELECT/INTO is incredibly fast because it uses minimal logging. Like a stored procedure, it also allows you to pass in parameters and you can actually think of the function as a parameterized view.
The only drawback that I see is there isn't any index which would have to be created separately. I've used this method as long as I wasn't joining it with other tables.... It makes for a very inefficient processing....
For me, those problems normally don't exist and they probably shouldn't exist for most. Stop and think about what should be in a Temp Table even if you do have to join against it. It should ONLY contain data that will actually be used meaning that a table scan on a Temp Table is frequently as fast or faster than the use of indexes. If your temp table is so large that you need indexes on it to gain performance, then you may have simply put the wrong data into the Temp Table.
To wit, indexing a 50 row table like what the OP is producing is highly likely to be counter productive.
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Helpful Links:
How to post code problemsHow to post performance problemsForum FAQs