I see more and more people asking how to check if a temporary table exists. How do you check if a temp table exists?

You can use IF OBJECT_ID(’tempdb..#temp’) IS NOT NULL Let’s see how it works

now open a new window from Query Analyzer (CTRL + N) and run this code again

It doesn’t exist and that is correct since it’s a local temp table not a global temp table

Well let’s test that statement

It exists, right?
Now run the same code in a new Query Analyzer window (CTRL + N)

And yes this time it does exist since it’s a global table

I have also added this to our wiki, read it here: Check If Temporary Table Exists

Straight from the man himself comes this statement posted in the microsoft.public.sqlserver.programming forum: “You need to read ISO-11179 so you use proper data element names. You
actually had “tbl-"on the table names! Sometimes “id” id a
prefix and sometimes it is a postfix.“

Of course you know who I am talking about? No? Joe Celko of course. So what is ISO-11179?

The 11179 standard is a multipart standard that includes the following parts:

11179-1
Part 1: Framework, introduces and discusses fundamental ideas of data elements, value domains, data element concepts, conceptual domains, and classification schemes essential to the understanding of this set of standards and provides the context for associating the individual parts of ISO/IEC 11179.

11179-2
Part 2: Classification, provides a conceptual model for managing classification schemes. There are many structures used to organize classification schemes and there are many subject matter areas that classification schemes describe. So, this Part also provides a two-faceted classification for classification schemes themselves.

11179-3
Part 3: Registry Metamodel and Basic Attributes, specifies a conceptual model for a metadata registry. It is limited to a set of basic attributes for data elements, data element concepts, value domains, conceptual domains, classification schemes, and other related classes, called administered items. The basic attributes specified for data elements in ISO/IEC 11179-3:1994 are provided in this revision.

11179-4
Part 4: Formulation of Data Definitions, provides guidance on how to develop unambiguous data definitions. A number of specific rules and guidelines are presented in ISO/IEC 11179-4 that specify exactly how a data definition should be formed. A precise, well-formed definition is one of the most critical requirements for shared understanding of an administered item; well-formed definitions are imperative for the exchange of information. Only if every user has a common and exact understanding of the data item can it be exchanged trouble-free.

11179-5
Part 5: Naming and Identification Principles, provides guidance for the identification of administered items. Identification is a broad term for designating, or identifying, a particular data item. Identification can be accomplished in various ways, depending upon the use of the identifier. Identification includes the assignment of numerical identifiers that have no inherent meanings to humans; icons (graphic symbols to which meaning has been assigned); and names with embedded meaning, usually for human understanding, that are associated with the data item’s definition and value domain.

11179-6
Part 6: Registration, provides instruction on how a registration applicant may register a data item with a central Registration Authority and the allocation of unique identifiers for each data item. Maintenance of administered items already registered is also specified in this document.

The one that deals with naming conventions is 11179-5 The link will point to a zip file which has a pdf file in it. The TOC of this pdf file is below

Contents
Foreword
1 Scope
2 Normative references
3 Terms and definitions
4 Data Identifiers within a registry
5 Identification
6 Names
6.1 Names in a registry
6.2 Naming conventions
7 Development of naming conventions
7.1 Introduction
7.2 Scope principle
7.3 Authority principle
7.4 Semantic principle
7.5 Syntactic principle
7.6 Lexical principle
7.7 Uniqueness principle
Annex A (informative) Example naming conventions for names within an MDR registry
Annex B (informative) Example naming conventions for Asian languages

So check it out and hopefully you and your team can adapt a common naming conventions instead of having things named like employee_address, EmployeeAddress, employeeAddress and tblEmployeeAddress.

Which books should you read/buy when you are a programmer? I have listed 5 books that have helped me a lot. The books that I have chosen are not specific to any language although some of the books have examples in one language only. Design Patterns has examples in smalltalk and C++ but since the code is not very complicated you should have no problem converting it to your language of choice. I have included links to sample chapters for the books where I could find them. For some of the books I have also provided links to the author’s site; some of them have additional material so that you can look at that. I have also provided Amazon links so that you can read reviews. All of these books are rated 4 stars or higher. I have also provided alternate books if I felt that there were more choices for the same subject

Design Patterns
This book is one of the seminal books on patterns in software development. If you are a professional software developer, you must read this. If you are learning to write good software, this is a book that you will need to take on at some point

Design Patterns Site

Code Complete
Code complete provides the reader with an insight into how
to write good and easy to understand code. You will come away from this book with an appreciation of the thought process that should go into writing every class, routine, comment etc…

Software development steps are outlined clearly. Pitfalls to avoid are discussed and rewards obtained from good code is explained. The author tells you what you need to know and most importantly why you need this information. If one applies the ideas in this book, I think you will be a better programmer.

Sample Chapter: Chapter 1: Welcome (pdf)
Sample Chapter: Chapter 5: Design in Construction (pdf)

Code Complete Author’s Site

The Pragmatic Programmer
The pragmatic programmer provides invaluable advice to those who are just starting to program, and those who have been programing for years. By following the authors’ simple rules you should have gained some programming wisdom that a programmer would realize in a decade.

Extracts from the book
The Preface
Software Entropy
Programming by Coincidence
Evil Wizards
Balance Resources
Summary of the book’s tips
Contents

Refactoring
This book will change the way you think about and working with exisiting code. It’ll teach you that changing/modifying software is a fact of life. Martin Fowler does a awesome job of describing how to improve the design of existing code by performing various refactorings. Various design patterns are mentioned throughout the text, that is another reason why the design patterns book is so important

Sample Chapter: Refactoring, a First Example

UML 2 and the Unified Process: Practical Object-Oriented Analysis and Design, 2nd Edition
UML has grown. A few years ago, when UML was just getting accepted, a book on how to use it would have been much thinner. But the successful broad uptake of UML led to its semantic notation being expanded. What the authors give us here is a thorough exposition of UML 2.0 and how to use it. It also goes into the Unified Process for running a project, and how this can be documented in UML

Sample Chapter: Relationships

And here are a couple of more choices instead of the books above
Agile Software Development, Principles, Patterns, and Practices
AntiPatterns
Prefactoring
Ajax in Action
Head First Design Patterns

Microsoft Source Analysis for C# has been announced. Here is what I found on the Microsoft site:

This tool is known internally within Microsoft as StyleCop, and has been used for many years now to help teams enforce a common set of best practices for layout, readability, maintainability, and documentation of C# source code.

Source Analysis is similar in many ways to Microsoft Code Analysis (specifically FxCop), but there are some important distinctions. FxCop performs its analysis on compiled binaries, while Source Analysis analyzes the source code directly. For this reason, Code Analysis focuses more on the design of the code, while Source Analysis focuses on layout, readability and documentation. Most of that information is stripped away during the compilation process, and thus cannot be analyzed by FxCop.

The ultimate goal of Source Analysis is to allow you to produce elegant, consistent code that your team members and others who view your code will find highly readable.

More info can be found here: http://blogs.msdn.com/sourceanalysis/
Source Analysis for C# can be downloaded here: https://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=sourceanalysis.

SQL performance tuning is probably one of those things you can do to really make a HUGE difference in performance. Let’s put this in perspective: take a typical application, if you can improve the performance by 100% then you really made a huge improvement. You can improve a SQL query by 1000% with 2 lines of code (sometimes all you have to do is take away a % sign). If you can make a query sargable so that the optimizer can do an index seek instead of an index scan your query might go from 12 seconds to 200 milliseconds. Now try doing that in an application, even if you change all the string concatenation to use a stringbuilder instead of creating new strings all the time you will not get such a drastic performance improvement. I am sure you get the point by now, let’s talk about the book.

Inside Microsoft SQL Server 2005: Query Tuning and Optimization is part 4 of the Inside Microsoft SQL Server 2005 series, it is written by Kalen Delaney and five other authors. There are 6 chapters in this book

1 A Performance Troubleshooting Methodology
This chapter explains some typical things that affect performance and also gives a troubleshooting overview

2 Tracing and Profiling
This chapter explains how to use the profiler and how to analyze traces. SQL Server’s built-in traces are also covered

3 Query Execution
This chapter gives a query processing and execution overview. It explains how to read plans and goes into a lot of detail about analyzing plans

4 Troubleshooting Query Performance
This chapter explains how to detect problems in plans, how to improve queries and some best practices

5 Plan Caching and Recompilation
This chapter goes into detail about plan caching and recompilation and how to troubleshoot plan cache issues

6 Concurrency Problems
The final chapter deals with concurrency (locking, blocking and deadlocking)

This is an excellent book for an intermediate/advanced developer. There is so much new stuff in SQL Server 2005 compared to 2000 to help you with tuning queries that you probably want to read each chapter several times. The Dynamic Management Views are a big help and this book shows you how to use them. Some other cool stuff in this book is the discussion of internal tables, undocumented DBCC commands and undocumented trace flags to discover information which could help you determine much faster what the cause of a performance problem might be. Some pages are packed with so much information that you need to pause for a second and process all that info (I have read some pages two to three times in a row). You will also find out that there are more joins besides left, full and outer. Page 137 for example has a nice table with the three Physical Join Operators: Nested Loop Join, Hash Join and Merge Join. This table lists the characteristics for each of these joins.

If you are an intermediate to advanced developer then I highly recommend this book.

I have interviewed Kalen a while back about this book and you can find that interview here: Interview With Kalen Delaney About Inside Microsoft SQL Server 2005 Query Tuning and Optimization