I should not use temporary tables or cursors, correct?

enrique_aeo
enrique_aeo used Ask the Experts™
on
Hi, I have understood that I should not use temporary tables or cursors, correct?
if it is correct to use?
1. CTE
or
2. Table Value Parameters
or
3. code in. net and then add the ddl to my database
which is the best option?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bhavesh ShahLead Analyst
Top Expert 2010

Commented:
Senior Developer Analyst
Commented:
Yes, cursors should be avoided.  T-SQL improvements made since SQL 2005 have really made them obsolete. (Although every once in a blue moon if find myself having to use them).  Reason being is that they are INCREDIBLY slow compared to T-SQL code that doesn't use them.

Temporary tables store  a set of data in TempDB.  You should use Table variables instead of temporary tables.  This give SQL Server the option to store the data in memory rather then TempDB if it deems that more efficient.

Table Value parameters are new to SQL 2008 and allow passing tables into stored procedures as input parameters.  I actually haven't needed to use these yet but if you need to pass an array of data to a procedure I'd guess that would be a good approach.  Definitely not an expert on those.

Code in .Net via SQL server CLR allows for incredible flexibility.  Most of the time it's not as efficient as straight up t-sql code but usually if your using .NET to make a CLR function or proc, it's because your doing something t-sql can't do.

Just my 2 cents.  Good Luck.

Author

Commented:
hi goHord
can you give me an example about it's
"because your doing something t-sql can't do"
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018
Commented:
There is nothing wrong with temporary tables or cursors for that matter...

What is often wrong is where and how they are used when there are better, more efficient choices.

Sometimes they (cursors, temp tables) cannot be avoided, other times they should be avoided at all costs. The single most common error in judgement is failing to see where a "set" based solution is required and a mistaken belief that a row by row based solution is needed.

Using a CTE (common table expression) can help a lot because it is basically naming a query which can then be used for other purposes all within the one query. Because it defines an expression - or gives a subquery a name - that name can be subsequantly used within the definition and so can provide a recursion facility as well, therefore providing a level of row level operation.

But if you are going to a procedural solution such as a stored procedure or script, then you do have the option to use a multi-step solution which sometimes can be faster.

Table valued parameters are interesting - most people think you can pass a full table as parameters and it is not quite the case. There is great use for them from an application where you can pass an array as a collection and process that collection as if it were a table (the TVP provides a definition for handling the parameters).

Code in .Net for CLR routines - well it becomes code based so in theory there should be nothing you cannot do in writing your own (unmanaged) code.

As for an example, how about writing an export function to output data to disk when you cannot use XP_CMDSHELL, you can certainly write an easy export as a CLR routine without having to expose XP_CMDSHELL  (and other things we normall use xp_cmdshell for like directory listings and other dos / batch type commands).

Christopher GordonSenior Developer Analyst
Commented:
Some examples I've used CLRs for:

1. Geocoding addresses (Lattitude & Longitude).  Not something that SQL server has a native function for.
2. Standardizing addresses against a Postal Service Database.
3. Calling Web services (T-SQL has some functions for this but .NET gives you better control)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial