Is it OK to just TRY and add a column to an SQL Server database and catch the exception?

Question

Using SQL Server in C# is it worth saying:

IF COL_LENGTH('MyTable','MyColumn') IS NULL
 BEGIN
 ALTER TABLE MyTable ADD MyColumn INT
 END

because I could more easily put a catch around the call:

try
{
Db.ExecuteNonQuery("ALTER TABLE MyTable ADD MyColumn INT");
}
catch(Exception)
{
}

and just let if fail all the time (except when running on an old database) ... or is that naughty/slow/etc?

Answer 1

Exceptions should be "exceptional", as in the exception to the rule. Do you plan on running this code, and 90% of the time, the column exists?

Then that is not "exceptional".

Do not use exception catching as normal logic flow.

Here are some more pointers by someone smarter than me:

http://blogs.msdn.com/b/kcwalina/archive/2005/03/16/396787.aspx

"Do not use exceptions for normal flow of control. "

---

Here is my typical idempotent add column tsql.

IF EXISTS (    SELECT TABLE_SCHEMA , TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Categories' and TABLE_SCHEMA = 'dbo'    )
    BEGIN

        IF NOT EXISTS 
        (
            SELECT * 
                FROM [INFORMATION_SCHEMA].[COLUMNS] 
            WHERE   
                TABLE_NAME = 'Categories' 
                AND TABLE_SCHEMA = 'dbo'
                AND COLUMN_NAME = 'CategoryName'
        )
            BEGIN
                print 'Adding the column dbo.Categories.*CategoryName*'

                ALTER TABLE [dbo].[Categories] ADD [CategoryName] nvarchar(15) NOT NULL

            END
        ELSE
            BEGIN
                print 'The column dbo.Categories.*CategoryName* already exists.'
            END

    END

Answer 2

Just don't catch the Exception exception this way: you'll never know if it is thrown:

• because the table doesn't exists
• because the column already exists
• because the connection string is wrong
• because of a network error
• any other reason

If you want to create a column only if it doesn't already exists, do write the SQL that checks the presence of the column before adding it.

Answer 3

Does speed really matter in this case? How often do you update the database?

However, are you really sure that you want to silently eat ALL exceptions? The problem can be that you do not have DDL (modify db) permissions or that the column exists but with a different column type.

Answer 4

It's pretty standard to perform this type of check in your script because the script may need to be run more than once. For example, other table changes may get added to it. You don't want your script to cause errors if someone has to re-run it.

As others have noted, exceptions should be reserved for truly exceptional conditions.

But there's an additional problem in doing this in your C# code: you're mixing database maintenance stuff into what is (presumably) your application code. Your code is going to be much more readable and maintainable if you follow the principle of separation of concerns.

Answer 5

Your C# code flow is just a shallow shim around the back end, where the real work happens. As such, obvious questions arise:

WHY was an exception raised?

Reasons include, but are not limited to:

• you do not have permission to alter the table
• you timed out trying to obtain an SCH-M lock on the table
• the new column triggers a size-of-data operation that runs out of log space
• a custom DDL trigger prevents execution
• a policy-based declarative management rule prevents execution
• the IO subsystem just collapsed and database is offline
• the column already exists (which seems to be the only case you thought of)

You are willing to silently swallow and silence the exception because you can only think at one possible reason to fail. When dealing with databases you are always in need of serious logging, otherwise you're just shooting yourself in the foot.

Is the exception free?

There is a long debate whether SEH is cheap or not, but this is completely beyond the point when talking about DDL in a database. You are requesting a SCH-M lock on an object and thus will block everybody until you get it. Even if your DDL throws and you handle the exception, you have already dipped the throughput to 0 (zero) while you waited for the uber lock to be granted. And you may had been waiting hours, you know...