Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
SQL 3
COLUMNPROPERTY 1
OBJECT_NAME 1
OBJECT_ID 1
SysColumns 1
IF EXISTS 1
decimal 1
float 1
conversion error 1
implicit convertion 1

Archive · View All
March 2012 3
April 2012 2

Adam Tokarski's Blog

Looking for column

Apr 25 2012 12:00AM by Adam Tokarski   

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'

 CheckColumns1

(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.

  Plans of IF EXISTS...

(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
with time of 453ms
vs
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
with time 1093 ms.

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.



Thank you for your attention.
-- Adam Tokarski --

Tags: sql, IF EXISTS, SysColumns, OBJECT_ID, OBJECT_NAME, COLUMNPROPERTY


Adam Tokarski
58 · 3% · 1021
1 Readers said this post helped them to refresh memory
Nakul Vachhrajani said this post helped them to refresh memory on 4/25/2012 12:40:00 PM
Profile · Blog · Facebook · Twitter
5
 
0
Lifesaver
 
1
Refreshed
 
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Looking for column" rated 5 out of 5 by 5 readers
Looking for column , 5.0 out of 5 based on 5 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]