I have become a little idiosyncratic over time, I know. And, many of my pet peeves  has to do with something that may seem pretty trivial to most. Regardless, every time I see it, I get a little irritated. It seems that even the best Developers are in too much of a hurry (or maybe just too lazy) to think about the little things…

Here’s a little thing. Existence checking for objects. If you look at SQL Books on line for an example of how to create a new object (or look at any of the books at your local book store, or search the web for examples…) the examples will either begin without any existence check or will begin with something that looks like this:

begin ...

The problem with not checking at all is that your script (or procedure) is not reliable. Even if it works the first time you use it, it will fail the second time. It will fail the first time if, for some reason, there is already an object with the same name as the one you are getting ready to create in the database. You should always insure that when your script or procedure runs, you get exactly the results you intended (and none other). And, I’m pretty sure no one will argue that you shouldn’t at least check first…

But, what about that existence check I used as an example above? It will work as advertised every time. But, does that mean it is the best you could do? I would argue that it isn’t.

Here’s why:

The good folks at Microsoft have gone to great lengths and worked long hours to provide you with functions that return useful information about the server, database, and objects contained in the database (or elsewhere on the server). One such function is OBJECT_ID().  For an entertaining exercise, compare the estimated query plan for each of the following statements.