i previously blogged about using the object_id function in a drop statement instead of the usual way. i wondered how it did performance wise. it may be prettier, but is it faster? slower? the short answer is it appears to be faster to use the function than the default drops.

for reference, we are talking about comparing this:

if EXISTS (SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N'[dbo].[MSreplication_options]')
  AND type in (N'U'))
  drop proc dbo.msreplication_options

with this:

if (object_id('dbo.msreplication_options') is not null)
  drop proc dbo.msreplication_options


as you can see, the auto-generated drop script makes the same call anyway.

you can see some detailed results if you execute this:

print '1'
set statistics profile on
set statistics time on
go
select 'yes' where object_id('dbo.msreplication_options') is not null
go
set statistics time off
set statistics profile off
go
print '2'
set statistics profile on
set statistics time on
go
select 'yes' where EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MSreplication_options]') AND type in (N'U'))
go
set statistics time off
set statistics profile off
go

you can pick another proc or table or whatever object you want for analysis. the results were the same for me. enjoy!