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.

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