What's the difference between these two methods to drop a database?

I always use the following code to drop a database in SQL Server.

IF DB_ID('mydatabase') IS NOT NULL
    DROP DATABASE mydatabase;

But in Paul S Randal's MCM video, I saw co开发者_如何学Cde like this:

IF DATABASEPROPERTYEX ('mydatabase', 'Version') > 0
    DROP DATABASE mydatabase;

What's the diffference? Is the second way faster than the first one?

Thanks.

There is no significant difference. I'll even give you another option:

IF EXISTS(
    select *
    from master.sys.databases
    where name = 'mydatabase')
DROP DATABASE mydatabase;

There are always many ways to do the same thing. But from personal experience, DB_ID is both the shortest to write out and probably fastest too.

Various ways to access information about databases

• sys.databases http://msdn.microsoft.com/en-us/library/ms178534.aspx (2005 onwards)
• sysdatabases (don't use for future work)
• DB_ID http://msdn.microsoft.com/en-us/library/ms186274.aspx (7.0+)
• databasepropertyex http://msdn.microsoft.com/en-us/library/ms186823.aspx (2000+)
• databaseproperty http://msdn.microsoft.com/en-us/library/ms176049.aspx (7.0 to 2008 R2, don't use for future work)

Fundamentally, there's not any difference. The IF statement is merely there to keep you from receiving an error when you call the DROP DATABASE command (i.e. it's a check for existence).

And with a high cost action such as a DROP DATABASE, the millisecond differences inherit in their respective existence checks is somewhat...irrelevant..

Now if you were looking for information about a database, then DATABASEPROPERTYEX is a very useful tool.

If you just want to know if a database exists so you can drop it, use DB_ID.

If you only want to drop an open database then use DATABASEPROPERTYEX. The version number of a closed database is null. Dropping a closed database or one with offline files will not remove the files from disk.

Other than that, performance is not an issue.