Try to avoid sql generated names if you want to use change scripts to do the database upgrade.

Sometime, if you don't explicitly name an object when you create an object in sql server, the sql server will create a default name for you. It is not too bad if you have some sophisticated tool to migrate your database. But if you want to run your change scripts manually , it's best that you name every object you created, not relying on the system generated name.

One way to create an object in the sql server is normally dropping it firstly, that's where I got hit when relying on the system generated name.

I have an sql script like this:

IF dbo.ColumnAlreadyExists('BusinessCategory','IsActive')=1
BEGIN
ALTER TABLE BusinessCategory DROP COLUMN IsActive
END

ALTER TABLE BusinessCategory ADD IsActive Bit DEFAULT 1

It's fine when I ran it the first time, it generated the new column for me, but when I ran it again, it complains there is a default constraint object on the IsActive column which needed to be dropped firstly. The issue is the system generated a long name for my default constraint. The name is long, hard to remember, and could be changing, so it makes the changes scripts hard to write.

So, the better way is to use a named object instead like this:


ALTER TABLE BusinessCategory ADD IsActive Bit
GO
ALTER TABLE BusinessCategory ADD CONSTRAINT BusinessCategory_Default DEFAULT 1 FOR IsActive
GO

That way, you can always drop the named constraint if you want to drop it like this:

IF dbo.ColumnAlreadyExists('BusinessCategory','IsActive')=1
BEGIN
ALTER TABLE BusinessCategory DROP CONSTRAINT BusinessCategory_Default
ALTER TABLE BusinessCategory DROP COLUMN IsActive
END