The second page of the
DataDiff comparison setting dialog deals with individual
columns. For each column, you can set the role
(Key/Compared/Ignored) it plays in the comparison and various
options specifying how exactly this column will be loaded, compared
and scripted. Although the Diff usually comes up with some
reasonably good default configuration based on the information
available from the schema (primary key, column types, default
values, etc), you might need to adjust the settings to your needs,
as explained below.
column roles. In the comparison, each
column can be assigned one of the following three roles:
columns. Before the tables can
be compared, you must
choose one or
more key columns. The key fields are important, as the program
associates a row in one table with its counterpart in the other one
by comparing their key column values (or combinations of values, if
there are several columns in the key).
If there is a primary key
constraint in one of both tables, its columns will be taken as key
columns by default. However, this is not necessarily what you need
(and DataDiff does not
care if the
key columns in the comparison are actually PK in the database).
Often the PK of a table is based on some meaningless, internally
generated numeric ID, which might have totally different values in
the two databases. In such situation you might prefer to use some
other unique column of the table (e.g. person's name or email). It
is strongly advised that you choose for the key those column(s)
which don't contain duplicate data (although the Diff does handle
duplicate keys as well).
In the data comparison
windows, the key columns are always displayed as the left-most
columns of the grid and are indicated by gray background.
columns. These are the columns
which must not take part in this comparison. They are not loaded
into the buffer (although there are
exceptions), not displayed and not
scripted. By default, computed columns or columns present in only
one table are marked as ignored. BLOB columns may be marked so too,
unless you switch off the appropriate
option. But whichever columns
the program initially marks as ignored, you can always change that
before starting the comparison.
(compared) columns. These are just any
other columns left after you've chosen the key and the ignored
ones. When the program finds a pair of records with matching key
column values, it proceeds to compare each pair of "regular"
columns to see whether the row has changed or not.
Note that the column list
supports multiselection: you can choose as many items as you like
and then change their state with a single click. The program saves
the configuration of columns for each pair of recently compared
tables and automatically recalls the settings whenever you compare
the same tables again.
comparison and scripting options for specific columns.
select a specific column, one of more of the following
options will become enabled for
Digest if longer than... Here you can specify the
maximum column length, in bytes, beyond which the column value will
be digested into a 16-byte checksum before being loaded into the
buffer. This option is only enabled for strings and binary
columns. Digested columns can be reliably compared, although
obviously you can't view them. Such columns can be scripted
(regardless of their length) only if you use server-side scripting
option for them.
lone column to... It is possible that one
table has some columns which don't have their counterparts in the
other table. Normally such columns are not compared at all
(automatically marked as "Ignored", see above), but you can force
DataDiff to compare them against a specific value. Here you
can specify such value, which must be a valid SQL expression
type-compatible with the lone column. DataDiff does not try to
parse or verify the expression, but simply passes it to the server
as part of the query. Therefore it need not be a constant, but may
be an expression using column names and functions. If you use
comparison/scripting options involving the "Compare to...", but
leave this input field empty, then NULL will be used.
tolerance... is enabled only for
numeric columns (even for integer ones, although it mostly makes
sense for FLOAT/DOUBLE/DECIMAL/CURRENCY columns). If you
specify a non-zero value here, any difference in column values not
exceeding the tolerance will be interpreted as a "minor" one.
Which means the two column values will be displayed one below the
other as normal changed values, but they won't be color-marked and
won't be scripted.
insensitive option is only enabled
for string columns (CHAR/VARCHAR/NCHAR/NVARCHAR/TEXT/NTEXT).
If checked, column values differing only in character case will be
considered "minor changes".
trailing spaces is only enabled for
[N][VAR]CHAR columns. If ON, this option makes the DataDiff to
strip off any trailing blanks before comparing column values.
This is the default option for fixed-length character columns,
because the values obtained from them are always
space-padded. Otherwise you would never be able, for example,
to compare value 'TEST' in a CHAR(5) column against 'TEST' in a
CHAR(6) one. Trailing spaces in [N]VARCHAR columns may be
significant, so for such columns the option will be initially OFF
and it will be up to you to switch it on if needed.
0, '', False... This option is only
enabled for NULLable columns and indicates that differences between
NULL and various "empty" values like 0, '', 0x will be considered
as text for CLR types: as of
AdeptSQL Diff 1.97 build 100, the DataDiff can compare .NET data
types (also referred as CLR types), both predefined (GEOMETRY /
GEOGRAPHY / HIERARCHYID) and user-defined. CLR values are
stored as fixed- or variable-size binary data, and the DataDiff
would be default compare and display them as binaries. However, all
CLR types predefined in the SQL Server have methods for implicit
conversion to and from string, and any decent user-defined CLR type
would probably have such conversions as well. When you set
the "Compare as text", the DataDiff will include into the data
query an explicit data conversion of the selected column into
NVARCHAR(MAX), and then compare, display and script the converted
The following options
determine how the selected column(s) will be scripted. As you can
see on the screenshot above, these scripting modes can be set
separately for each of the two direction. The Diff analyzes a
number of factors (such as whether the column is nullable, has some
default value, is IDENTITY, is computed, etc) to enable only those
script modes that would make sense (produce valid SQL) for this
particular column/direction. For example, if you compare a
computed column against a regular one, the scripting options will
obviously be limited to "Exclude from inserts" on the
computed-column side, but can be set differently for the other
Of the enabled options,
the Diff chooses an "optimal" scripting mode for each
direction. That means you need not bother about setting
scripting modes for every column if you don't want to. If you do,
you can choose between a subset of the following scripting
- this is the
initial setting for all columns. It means that the Diff will
set the actual scripting mode according to the factors mentioned
above. The program indicates the scripting mode currently
optimal for this field by displaying one of the following options
will use data loaded into the comparison buffers, as literals, to
script the column. This was the only scripting mode available in
Diff versions prior to 1.90 and it is the default scripting mode
now. Note that for a variable-size column that can be
MD5-digested (that is, TEXT or BLOB), this is not a reliable
option. Rows where the column value is long enough to become
digested will not be scripted and DataDiff will report an error for
each of such rows.
- This is
often the best scripting mode for longish variable-size text and
binary columns, which can be successfully scripted even when only
their MD5 signature is present in the buffer. However,
server-side scripting is only possible when the two databases are
accessible to each other (or at least the source database is
accessible from the destination server). For numeric and
other short columns server-side scripting is also possible, but
usually doesn't make much sense, because using loaded values (that
provides for more compact and efficient queries.
from INSERTS - this only applies to
columns which are NULLable, are IDENTITY columns or have a default
value. It means that when an INSERT is generated, no value for this
column will be included into the VALUES list, so it will be left
NULL or initialized from its DEFAULT or the next identity
'Compare to' value - this only applies to
"lone" columns which, obviously, can be dealt with in INSERTs in
only two ways: either not included at all (see above) or included
as a constant. To make things simpler, in the latter case the
DataDiff uses that same 'Compare To' expression described