Parentheses in Defaults of bit columns don't match b/w DBs

digitalage

I am comparing 2 SQL Server 2005 Databases. One DB is on my local machine while another is on a remote Windows Server 2003. Both DBs are running under SQL Server 2005 Standard. Anyway, for some reason, when I compare the 2 databases, SQL Compare seems to think that many tables have differences even when they don't. I narrowed down the problem and it seems like there is some weird thing going on with the DEFAULTS on columns, specifically the ones on bit columns. In my local DB, SQL Compare shows the defaults as being ((0)) while in the remote DB it shows it as (0) with only 1 set of parentheses.

Example:

Local DB:
[Absent] [bit] NULL CONSTRAINT [DF_Employees_Base_Absent] DEFAULT ((0))

Remote DB:
[Absent] [bit] NULL CONSTRAINT [DF_Employees_Base_Absent] DEFAULT (0)

This, I guess, is enough of a difference to make the 2 tables look different to SQL Compare. When I look at the tables through the management console, I do indeed see that there are 2 parentheses in the remote DB's Default's for all bit columns, but the local DB has only one set of parentheses.

I was wondering if there was a setting that I need to change, in either SQL Server or Red-Gate that can help me avoid this issue...because now, almost all my tables come up as different which makes it a real pain to see whet the real differences are.

(By the way, when I try to remove the parentheses or add the parentheses to make the 2 databases match, the parenthese revert back as soon as I save...so I can't even fix it that way)


Thanks in advance!!!

Brian Donahue

Hi,

Yes, we're aware this is an issue with SQL Server 2005 because it adds these extra delimiters on its' own. We can't fix it in the short term because it would be too complicated, but it's something we are looking at in the future.

Andras B

digitalage wrote:

I am comparing 2 SQL Server 2005 Databases. One DB is on my local machine while another is on a remote Windows Server 2003. Both DBs are running under SQL Server 2005 Standard. Anyway, for some reason, when I compare the 2 databases, SQL Compare seems to think that many tables have differences even when they don't. I narrowed down the problem and it seems like there is some weird thing going on with the DEFAULTS on columns, specifically the ones on bit columns. In my local DB, SQL Compare shows the defaults as being ((0)) while in the remote DB it shows it as (0) with only 1 set of parentheses.

Example:

Local DB:
[Absent] [bit] NULL CONSTRAINT [DF_Employees_Base_Absent] DEFAULT ((0))

Remote DB:
[Absent] [bit] NULL CONSTRAINT [DF_Employees_Base_Absent] DEFAULT (0)

This, I guess, is enough of a difference to make the 2 tables look different to SQL Compare. When I look at the tables through the management console, I do indeed see that there are 2 parentheses in the remote DB's Default's for all bit columns, but the local DB has only one set of parentheses.

I was wondering if there was a setting that I need to change, in either SQL Server or Red-Gate that can help me avoid this issue...because now, almost all my tables come up as different which makes it a real pain to see whet the real differences are.

(By the way, when I try to remove the parentheses or add the parentheses to make the 2 databases match, the parenthese revert back as soon as I save...so I can't even fix it that way)


Thanks in advance!!!

Hi, there is no way of fixing this. SQL Server 2005 parses the definition of defaults, and it modifies it. So it is no longer possible to put exactly the text we wish into a default. SQL Server even changes function names in addition to introducing parantheses all over the place. For this reason we now look at the semantics of the defaults, and compare them at semantic level. So while textual differences will exist, we still can detect correctly whether the content is different or is the same. For more information please see my blog http://blogs.red-gate.com/blogs/andras

Regards,
Andras