Hi All,

First would like to thank all of you for the knowledge sharing in this website. I have taken over a responsibility of being an Software Architect in my company and been on it for a year. Personally I have 10 years experience in programming in different languages. When I took over this responsibility one of my task was to write coding standards and I remember I got the standards from this site and they helped us a lot.

From that coding standards I had to create a checklist that we use to allow work to be taken to production servers. One of the standards is that we should avoid temp tables and try a use more of derived tables and table variables instead. Obviously there is always new people coming in and this new guy is challenging this point because he likes or prefer using temp tables more than derived tables or table variables. He is arguing that temp tables are actually better.

Can someone help me here. I am open for good suggestions and if really temp tables are better then maybe I will have to change the standards document to say that but I would like here what you guys think.

 Thanks

B

Hi,

In opinion, your approach should be scenario based and Avoid temp table and use table variable

1).Temporary tables behaves as Local as well global

If you see performance wise, table variable perform well with small set of data, if data size is huge then it is advised to use temporary table with support of indexes and statistics

Thank you very much. Appreciated.

Regards

b

You will also get an initial information on this one by referring to http://www.sql-server-performance.com/articles/dev/sql_best_practices_p1.aspx & http://www.sql-server-performance.com/articles/per/SQL_Server_Performance_Coding_Standards_p1.aspx

This is very good. The first link is the actual standard I implemented and thanks for the second link.

I am reading through it and it clearly explained which sometimes some developers need that kind of explanation.

Thank you

B