SQL Server Performance

SQL Coding standards

Discussion in 'General Developer Questions' started by bmbele, Jul 24, 2008.

  1. bmbele New Member

    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
  2. SQL2000DBA New Member

    Hi,Welcome to forum.Many times I came across this type of question as I had developed many database coding standards.
    In opinion, your approach should be scenario based and Avoid temp table and use table variable Because of following reason1). Table behaves as Local variable2). Table variables require less locking and logging resources3). Table variables are automatically cleaned up at the end4). Table variables used in stored procedures cause fewer recompilations of the stored procedures than when temporary tables are usedWhere as
    1).Temporary tables behaves as Local as well global2).Temporary tables require more locking and logging resource3). Temporary variable needs explicit DROP else remain in Tempdb till user disconnects
    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
  3. bmbele New Member

    Thank you very much. Appreciated.
    Regards
    b
  4. satya Moderator

  5. bmbele New Member

    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

Share This Page