Category: SQL Server internals
SQL Server versions: 7, 2000, 2005
Level: Advanced

Well, this week I spent some time playing with defaults, and here is just a small taster of what SQL Compare version 4 does for you.

Defaults are a nice thing in SQL. One can assign defaults to table columns, and when one does not assign a column value for a column with a default the default is used automatically. A simple example is the following table:

create table table1 ( a1 int identity, a2 int not null default 1)

One can insert values in the traditional way:

create table table1 ( a1 int identity, a2 int not null default 1)

or by:

insert into table1 values(DEFAULT)

or, since all the columns can be assigned a default value:

insert into table1 default values

The text that we specify after the default in the table definition is stored in a system table (syscomments or on Yukon sys.default_constraints). Whenever a row with missing values is inserted the system table is consulted, and if needed the default value expression is evaluated.

But what exactly goes into the system table? On SQL Server 2000 in the above example “(1)” will be inserted as the default value. Note the parentheses.One can of course specify “(1)” when one creates the default, but in that case the converted value for the default remains “(1)”. If one inserts “(convert(int,1))” it will, as expected remain “(convert(int,1))”.

Well, we look at the above from SQL Compare’s point of view, and it is all nice, we compare two defaults, and if they are different we just synchronise them for you :-).

Ok, this is the moment when the above idyllic picture shatters.

Try to use “cast(1 as int)” as a default. Cast is basically the same as convert, but it is the standard SQL-92 command. Who prefers what is a question of taste. But SQL Server clearly prefers converts, and it replaces casts with converts, thus if one inserts the above default, SQL Server stores “(convert(int,1))” in the system tables. This is not too bad, since we can only have converts in the system tables, thus SQL Compare does not need to think about intelligent comparison.

However, until now we were only inspecting SQL Server 2000. Let us have a look at SQL Server 2005. Unlike SQL Server 2000, SQL Server 2005, or Yukon, puts double braces around constants. So the “1” would be converted to “((1))”. This would not really be a problem if one does not want to compare 2000 and 2005 databases. As a first solution first and last parentheses could be ignored if the compared strings differ in size by exactly two.

But if one inserts “CONVERT(xml,'Hi',0)” it gets converted to “CONVERT(xml,'Hi',(0))”. That is not nice. So the next step could be a parentheses ignoring stack machine that matches expressions based on their scope. This would work perfectly, until one inserts “a+b+c”. A seemingly innocent default value is converted to “(1 + 2 + 3)” on SQL Server 2000, but on Yukon it is converted to “((1)+(2))+(3))”. Note that the first addition is encapsulated in an additional parentheses pair. So let’s move to expression trees, and it clearly solves the problem.

Well, SQL Server still has surprises. The syntax of convert has changed, and in Yukon it can accept an optional third parameter. Thus a SQL Server 2000 default “'SO' + convert(nvarchar(23),[SalesOrderID]))” would be changed to “('SO'+CONVERT([nvarchar](23),[SalesOrderID],0))”. Note the third parameter. Note also, that this default cannot be inserted in exactly the same form, i.e. with the “0” parameter, since it would be changed to “(0)”. Well, in this case we need to use something much more than simple expression tree comparison.

I believe this is a moment when many who compare databases manually give up J. The good news is that SQL Compare handles all the above cases, and detects differences properly for all the above cases, and for a number of cases like strange commeting, case sensitivity, and other cases I did not include in this blog.

András