Well-known issue with Default constraints?

Ken from MERA

Hello,

Our company is developing software which operates with Microsoft SQL Server 2000.
Currently third-party tool is used in our build environment in order to compare two live databases and produce synchronization SQL script. This tool is executed from command prompt with appropriate options and works fine with Microsoft SQL Server 2000 (execution from command prompt is used in order to automate this process by means of task scheduler).

In next release of our product we are going to migrate to Microsoft SQL Server 2005 and unfortunately that third-party tool is unable to operate with it (company which is responsible for that third-party tool is not going to develop new version for Microsoft SQL Server 2005).

That's why we have to search for replacement.

red-gate SQL Compare looks like good one, but there is one issue with Default constraints which was in our "old" third-party tool as well:

When two databases are compared and difference in Default constraints is found, the following piece of code is produced:

...
ALTER TABLE [dbo].[SomeTable] DROP CONSTRAINT [OldConstraintName]
...
ALTER TABLE [dbo].[SomeTable] ALTER COLUMN [SomeColumn] [tinyint] NULL
...
ALTER TABLE [dbo].[SomeTable] ADD CONSTRAINT [NewConstraintName] DEFAULT ((160)) FOR [SomeColumn]

It is OK when you are going to execute generated SQL-script on SQL-server which was used during schema comparing.

But there can be some problems if you are going to pack this script into patch installer for several different SQL-servers. It is because Default constraints can have different names on different servers, while their names are fixed in generated synchronization script.

There are two possible solutions to fix this issue:


1) Always use named constraints.
You should either create default constraints directly in your DB source as

ALTER TABLE SomeTable ADD CONSTRAINT NewConstraintName DEFAULT ((160)) FOR SomeColumn

or specify Default constraint name in table definition as

create table SomeTable
(
    DefaultSomethingColumn tinyint CONSTRAINT SomeConstraintName Default 170 null
)

2) You can alter synchronization script and retrieve Default constraint name using the following piece of code:

declare @constraint_name sysname

select
  @constraint_name = c_obj.name
from
  sysobjects c_obj
  inner join syscomments com on c_obj.id = com.id
  inner join sysobjects t_obj on c_obj.parent_obj = t_obj.id
  inner join sysconstraints con on c_obj.id = con.constid
  inner join syscolumns col on t_obj.id = col.id and con.colid = col.colid
where
  c_obj.uid = user_id()
  and c_obj.xtype = 'D'
  and t_obj.name = 'SomeTable'
  and col.name = 'DefaultSomethingColumn'

After that you can drop this constraint using dynamic SQL:

exec ('if exists (select 1 from sysobjects where name=''' + @constraint_name + ''') alter table SomeTable drop constraint ' + @constraint_name)

Unfortunately, second way is not implemented in red-gate SQL Compare and it can be serious barrier for our migration on it.
Can it be implemented as an option?

JonathanWatts

Hi Ken,

Whilst I understand your problem I am not entirely clear how the first option you suggest will solve the issue, as this is currently how SQL Compare operates. SQL Compare should always use named default constraints, even if they were orginally SQL Server generated ones (e.g. DF__Different_De__Id__03317E3D). If the databases you are running the script on have renamed default constraints in them this will still cause a problem when the constraints are dropped.

Unfortunately, at the moment, I don't think that we would consider the dynamic SQL approach within SQL Compare itself. We currently feel that it would overly complicate scripts, and the number of possible naming conventions users would want would be very difficult to support successfully.

Kind Regards,

Jonathan

Ken from MERA

Jonathan Watts wrote:

Hi Ken,
Whilst I understand your problem I am not entirely clear how the first option you suggest will solve the issue, as this is currently how SQL Compare operates.

Hi,

Actually, first option is not for SQL Compare, but for user's source code
It was an example how the issue can be avoided.


As for second option: am I able to apply necessary modifications for synchronization script using SQL Compare API?

JonathanWatts

Ken,

Ah, now I am with you, sorry about that.

Okay the API, well unless you want to programmatically search / replace all the default constraints from the script then the API isn't really going solve your problem, as the only real way for you to interact with the synchronisation script as a string.

We might be carrying out some upgrades to our SQL Packager product that will improve it patching abilities later in the year, this may help you. However, at this stage I could not say when this product would be released.

Sorry about this,

Jonathan