Introduction

Sometimes it happens, that we are forced to add some column to existing table. Naturally, at first, we have to find whether that columns isn't already created.
Simple task. There is many ways to find it out, as far as I know
Till yesterday I was using simple:

IF NOT EXISTS(SELECT * FROM sysColumns c INNER JOIN sysObjects o ON o.id = c.id AND o.NAME LIKE 'OurTable' AND c.name LIKE 'OurColumn'))
-- ALTER TABLE...

But today I was working out to use - for that purpose - OBJECT_ID and OBJECT_NAME statements.

OBJECT_ID and OBJECT_NAME

Let's start our researching with:

SELECT * FROM sysColumns WHERE OBJECT_NAME(id) = 'zsbMeasureDef' AND name LIKE 'zsbMeasureAggregateTypeId'

SELECT * FROM sysColumns WHERE id = OBJECT_ID('zsbMeasureDef') AND name LIKE 'zsbMeasureAggregateTypeId'

SELECT * FROM sysColumns c INNER JOIN sysObjects o ON o.id = c.id AND o.NAME LIKE 'zsbMeasureDef' AND c.name LIKE 'zsbMeasureAggregateTypeId'

(Click on image to enlarge)

Do you see differences?
• The first one - quite well costed here - is rather server slaughter. Using function on searching column in WHERE clause is an interesting idea, though...
• The second one seems to be the best.
• The third one is something that we can call 'second option'.

But it's quite tricky trap. Remember, that we want to use it in function IF EXISTS.
As we know, IF EXISTS is not looking for every data (when SELECT *) but only testing, if data exists.

IF EXISTS

So, when try it in more appropriate form:

IF NOT EXISTS(SELECT * FROM sysColumns WHERE OBJECT_NAME(id) = 'zsbMeasureDef' AND name LIKE 'zsbMeasureAggregateTypeId') print 'a'

IF NOT EXISTS(SELECT * FROM sysColumns WHERE id = OBJECT_ID('zsbMeasureDef') AND name LIKE 'zsbMeasureAggregateTypeId') print 'a'

IF NOT EXISTS(SELECT * FROM sysColumns c INNER JOIN sysObjects o ON o.id = c.id AND o.NAME LIKE 'zsbMeasureDef' AND c.name LIKE 'zsbMeasureAggregateTypeId') print 'a'

optimizer doesn't need to use Key Lookup, so now te first and the second queries are equal (have almost the same plan), with slight advantage of that second. Both are outstanding from (my) traditional, third query.

(Click on image to enlarge)

However, differences are so small, that in everyday work it doesn't matter which way you will go. It is rather question of which is more readable (or has fewer typing).

COLUMNPROPERTY

But there is one more option, I was looking at today. Its syntax with ColumnProperty, like:

IF COLUMNPROPERTY(OBJECT_ID('zsbMeasureDef'), 'zsbMeasureAggregateTypeId','Precision') IS NULL 
-- ALTER TABLE...

I do not recommend using it, because it's not quite obvious what is used for, when you see it in code on first sight.
However, I made simple test of performance:

DECLARE @dateStamp DATETIME,
  @Count INT
SET @dateStamp = getDate()
SET @Count = 0

WHILE (@Count < 10000)
BEGIN
  IF COLUMNPROPERTY(OBJECT_ID('zsbMeasureDef'), 'zsbMeasureAggregateTypeId','Precision') IS NULL
    PRINT ''
  SET @Count = @Count + 1
END

PRINT DATEDIFF(ms, @dateStamp, getDate())
GO

DECLARE @dateStamp DATETIME,
  @Count INT
SET @dateStamp = getDate()
SET @Count = 0

WHILE (@Count < 10000)
BEGIN
  IF NOT EXISTS(SELECT * FROM sysColumns WHERE id = OBJECT_ID('zsbMeasureDef') AND name LIKE 'zsbMeasureAggregateTypeId')
    PRINT 'a'
  SET @Count = @Count + 1
END

PRINT DATEDIFF(ms, @dateStamp, getDate())
GO

Albeit its hard to see something like this in production environment, using ColumnProperty seems to be fastest option. However, as I mentioned, it's not recommended to use that.