AdeptSQL Diff most recent changes

Fixed bugs

• False ROWGUDCOL flags: In Build 90, some changes were made in the way the Diff reads column attributes from the database. The changed code usually worked correctly with all versions of SQL Server, but on SQL Server 2000 some of the columns suddenly appeared with a ROWGUIDCOL attribute. This was caused by SQL Server returning undocumented bits in syscolumns.status for some columns (while not setting the bits for other columns with apparently exactly the same type and attributes). Fixed.
• Resolving constraint name conflicts: suppose there is Table_A in the source database and a different Table_B in the target, both using a constraint with the same, explicitly specified, name. When synchronizing, the Diff would script the 'CREATE Table_A' before 'DROP Table_B', therefore causing conflict between the constraint names. This is now fixed in two different ways: (a) the synchro scripts are arranged so that the DROPs go first, and (b) the Diff also specifically looks into possible constraint name conflicts and renames or removes the conflicting constraints.
• A situation very similar to the one described above is when you add a column with a named constraint to a table, whereas in the target database the same table uses a constraint with this name for another column. Such situations are also correctly resolved now.

Fixed bugs

• Recent updates had an AV on comparison, which happened in certain rare situations involving computed columns. Fixed.
• Scripted USE with the wrong database: there is an option in "Scripting/Schema Level" that forces the Diff to script "USE database_name" at the beginning of each script. It worked fine for CREATE/DROP scripts, but for synchro scripts (where it normally isn't needed anyway) it reversed the scripting direction: e.g. if we compare DB1 against DB2 and wanted to script to DB2, it scripted "USE db1" instead, and vice versa. Fixed.

Changes

• Ignoring triggers and indexes: for a long time, the Diff has had the 2 options in "Options/Scripting/Tables" to include or not to include indexes and triggers with the tables. These options, however, only affected the CREATE scripting, but not synchro scripts. In this version, when you tell the Diff not to include indexes or triggers with a table, it will also ignore them in the synchro scripts. With the "Include triggers/indexes" settings turned OFF, the Diff will behave as follows:

  • In the schema tree, below the table node any changes in triggers or indexes are highlighted as usually, but the changes don't propagate to the table level or the entire schema level.
  • The changes in triggers or indexes remain visible and scriptable from their respective "Summary collections".
  • If any triggers or indexes need to be dropped to resolve some dependencies, they will be dropped regardless of the settings
  • If a table is dropped and re-created, triggers/indexes will not be scripted and therefore will be lost (the Diff will not attempt to restore them from the source database, as it does for permissions/xprops in similar situtations)

Fixed bugs

• Fixed the "List index out of bounds" error introduced in Build 90. Starting from that version, objects to be created and those to be dropped were accumulated in two different lists. Unfortunately, in some situations the wrong list was referred, resulting in either the "out of bounds" exception or incorrect scripting. The bug only affected the "Produce SQL/CREATE from..." and "Produce SQL/DROP from..." commands, not the regular synchro scripting.

Changes

• The only changes in this version are some minor corrections in the the About box and the Registration dialog. Internally, there are also changes in how the activation keys are handled, but they shouldn't affect you in any way.

Known problems

• All recent Diff versions have been able to run in "portable" mode (without installation), keeping all their settings in ASQLDIFF.INI and taking the activation key from ASQLDIFF.LIC (in the same directory). However, the "new" setup variant for WS2008 always keeps its activation key in the registry, which makes it inconvenient to use in the portable mode. We do plan to fully implement this feature in one of the future updates.

Supported new features of SQL Server 2008

• New DATE, TIME, DATETIME2 and DATETIMEOFFSET types: these new internal types are now fully supported.
• .NET-defined types: The Diff doesn't fully support .NET-defined types, including the built-in types HIERARCHYID, GEOGRAPHY and GEOMETRY, but it is now "aware" of them. Which means that if there is a table with some columns of these types, the columns are correctly compared and scripted. However, the types themselves are neither represented in the schema tree nor compared or scripted.
• SPARSE columns are now fully supported. The SPARSE attribute is loaded from the database, compared and scripted (using "ALTER TABLE ... ALTER COLUMN ... {ADD|DROP} SPARSE" syntax).
• COLUMN_SET XML columns are now fully supported.
• DATA_COMPRESSION attribute for tables and indexes is fully supported.
• Partial indexes: SQL Server 2008 allows to specify a WHERE condition when creating an index. These are fully supported in this update.

Features of SQL Server 2005/2008 that are NOT supported

Generally, no "new" objects that appeared in SQL 2005 or 2008 are supported or going to be supported in the current Diff 1.XX release or subsequent minor updates. Most such objects have already been implemented in Diff 2.0 and will be available once that new version of Diff is released. Examples of such "new" objects are .NET-related objects (ASSEMBLIES and .NET-defined types, procedures and functions), Service Broker objects (queues, contracts, services, etc) and various encryption-related keys and certificates.

However, whereever a new feature, such as a new column type or table attribute, extends a common schema object and may interfere with the comparisons, we try to implement it as fully as possible in the "minor" Diff 1.XX updates Diff, so that customers who utilize such enhanced functionality in their databases can continue to use the Diff. Specifically:

• Partitions: currently partition information is not loaded from the database. If a table or index is partitioned, the Diff doesn't recognize the difference and wouldn't script the "ON ..." clause. It is possible that some limited support of partitions would appear in a future 1.XX update (e.g. scripting them in table/index definitions, but not treating partitions and partition functions as "first class" schema objects to be compared and scripted). Full support for all partitioning-related object is being implemented in Diff 2.0.
• FILESTREAMs: this version hasn't been tested with SQL2008 filestreams, so it is not known how the use of this feature in your databases would affect your comparisons. We plan to fully support FILESTREAMs in the next 1.XX update.
• SPATIAL INDEXes: this version hasn't been tested with SQL2008 spatial indexes, so it is not known how the use of this feature in your databases would affect your comparisons. In the next 1.XX update we will verify that such indexes are safely ignored and don't affect your comparisons. Full support for this feature will be included in Diff 2.0.
• Service broker: any SB objects are ignored by Diff 1.xx. Full support for all such objects (message queues, contracts, endpoints, routes, etc) has been implemented in Diff 2.0 and will become available whenever that new version is released.
• Keys and certificates: any such objects are ignored by Diff 1.xx. Full support for keys and certificates has been implemented in Diff 2.0 and will become available whenever that new version is released.
• Assemblies and CLR-defined objects: of all the variety of CLR-related schema objects, the current version of Diff only recognizes CLR-defined types and only to the degree necessary to script columns that refer them in table definitions (see above). The Diff has not been extensively tested with other types of CLR-defined objects (procedures, functions, triggers), so it is not known whether they can interfere with its comparison and scripting. We plan to conduct such testing and make sure that Diff 1.XX is aware of such objects and safely ignores them. Full support for .NET assemblies and CLR-defined objects already exists in Diff 2.0 and will become available whenever that new version is released.
• Fulltext search: fulltext indexes and catalogs are ignored in Diff 1.xx. Unfortunately, this might break synchro scripts generated by the Diff when the presence of a fulltext index prevents a table or its columns from being dropped. We might have to add some limited support for text indexes (so that they can be detected as dependencies and dropped/re-created accordingly) in a future 1.XX update. Full support for fulltext catalogs and indexes has been implemented in Diff 2.0 and will become available whenever that new version is released.
• Datamining and "cube" extensions (DMX, MDX): the current version of Diff doesn't support them and so far we don't plan to support them in Diff 2.0 either.

Changes not specific to SQL2008

• ANSI_PADDING compared: behaviour of character and binary columns may be affected by the ANSI_PADDING setting during the table's creation. The server keeps the state of this setting with each column, so it actually becomes one of column attributes and as such should be compared and scripted. Before this version, the Diff ignored the ANSI_PADDING attribute, now it loads it and can (optionally) compare and script it. A new checkbox "Compare ANSI_PADDING" has been added to the 'Scripting/Tables' options page. This option is OFF by default, so the Diff will continue to ignore the ANSI_PADDING attribute until you instruct it otherwise. Even when ANSI_PADDING comparison is enabled, the Diff won't be able to script the situation when individual columns within one table are created with different ANSI_PADDING. Instead, a warning will be issues and the entire table scripted with ANSI_PADDING ON.
• Matching "principals": We changed the way database principals are compared. Although all such objects are listed under the same node of the schema tree, previous versions of Diff would only match user vs. user, role vs. role, approle vs. approle. In this version, principals with the same name but having different type (such as user 'tester' vs. role 'tester') will be paired.

Fixed bugs

• Unnamed constraints in SQL2008: The Diff recognizes automatic names that SQL Server gives to unnamed table constraints, marking such constraints as "unnamed" in the loaded schema and subsequently not including those name in the script. This feature didn't work in SQL2008, because the server now uses a slightly different format for the automatic names. Fixed.
• ALTER PROCS in side-by-side view: The Diff has an option to re-script any views and procedures referring a column or table that has been changed. This feature was intended only for the actual synchro scripts, but (incorrectly) the dependencies were scripted in the side-by-side view as well. With a lot of procedures in the side-by-side panels, that could make the textual comparison very slow or even crash. This problem has been fixed by not allowing the dependencies to be scripted for the side-by-side view.
• Adding NOT NULL columns: the server doesn't allow adding NOT NULL columns unless they have a DEFAULT. The Diff knows this and assigns a temporary DEFAULT that is dropped afterwards. However, SQL 2005 and 2008 only require this default if the table has data in it (whereas SQL2000 requires it for any table). The new version of Diff recognizes this nuance as well and doesn't generate temporary DEFAULTs when adding NOT NULL columns to empty tables in SQL 2005 or 2008.

New features

• Optional "USE target_db_name" statement at the beginning of any script: Although explicitly setting the current database is normally not required when you execute synchronization scripts in the Diff itself, it may be useful if you save the script and then run it from an external tool at a later time. This version of Diff includes an option to enable scripting of "USE target_db_name" at the beginning of any script the Diff generates. This options can be found on the "Scripting/Schema Level" page of the Options dialog. It is OFF by default, so the Diff won't generate the database selection command unless you ask it to.
• PAD_INDEX made optional: an option was added to the "Ignored details" options page to ignore or compare the PAD_INDEX attribute.
• Dependencies of principals: In SQL2005 and newer, a lot of various database objects (and not only the usual 'sysobjects' like tables or procs) can be made owned by a specific database principal (user, role, etc) using the ALTER AUTHORIZATION statement. If the principal is being removed, all these objects must be first "re-owned" to their schema or to DBO. Previous versions tracked those dependencies only for 'sysobjects' and named schemas, this update also tracks UDTs, XML schema collections and even object types that are not otherwise supported by Diff 1.xx (encryption, service broker, assemblies)
• "exec sp_addtype" vs. "create type": although these two ways of defining a UDT are equivalent, there is a quirk in how SQL Server handles them: "sp_addtype" additionally grants REFERENCES access to PUBLIC, whereas "create type" doesn't. Previous versions of Diff counted that as a difference in permissions, which was technically correct, but confusing. In this version, this particular "permission" is used by set an "old style" flag in the UDT object, but is not added to the permission list. The "old style" flag determines how the UDT definition will be displayed in the side-by-side view, but otherwise is ignored. It is not considered as a difference when comparing the types.
• Look-ahead for objects to be scripted: when an object is going to be dropped and the Diff is removing its dependencies, it is often possible to "move away" the dependent objects non-destructively as opposite to dropping them. For example, when dropping a UDT, we can either drop columns of that type or revert them to the underlying physical type. Obviously, if a column or table is going to be dropped anyway within the same scripting operation (e.g. the entire database was selected for scripting), it's easier to drop them at this point. Otherwise, the Diff must not destroy columns it was not asked to sync them. In previous versions, the Diff lacked the mechanism to look ahead and check if a particular object was selected for scripting. Now this has been added.

Fixed bugs

• Restore permissions after ALTER AUTHORIZATION: re-owning an object resets all permissions granted to it. When re-owning in the process of dropping a principal, permissions must be restored afterwards. Previous versions of Diff were aware of that, but didn't always restore the permissions correctly. Fixed.
• Can''t script permissions to owner: it is possible to construct a situation where in the "source" database a securable object such as a table has permissions granted to a pricipal, whereas in the target DB the same object is owned by that same principal. The Diff would then attempt to sync those permissions, however the SQL Server does not allow GRANTing permissions to the principal who owns the object. In this version, the Diff would recognize this situation, issue a warning and skip those permissions.
• Permissions for XML Collections: Although XML Schema Collections were added to recent versions of Diff, permissions for them were not loaded from the database. Fixed.
• Ownership for types and XML collections: these objects were supposed to support explicit ownership (scripted by ALTER AUTHORIZATION), however previous versions didn't correctly load the ownership information from the database. This is fixed now.
• Index options in brackets: before this update, the Diff used obsolete syntax for index options such as FILLFACTOR ("CREATE INDEX ... WITH FILLFACTOR=..."). This worked with the particular FILLFACTOR option, but other options such as PAD_INDEX require the new "WITH (option_name=ON|OFF,...)" syntax. This is implemented now: depending on the target server version, CREATE INDEX is scripted with either old or new syntax. See also (above) a new setting to ignore changes in the PAD_INDEX.
• Owner created with "Ignore owners": in a rare situation when the databases are compared with "Ignore owners", objects may be associated with each other even when they belong to different schemas, e.g. [User1].[Table1] in the source db will be matched to [dbo].[Table1] in the target. This is by design. When such a table is synchronized, all changes will apply to [dbo].[Table1]. This includes the situation when the target table must be dropped and re-created: the new table is created as [dbo].[Table1], not [User1].[Table1]. So far so good, except that in previous version the Diff would first create [User1] (incorrectly) before re-creating the table (correctly, as [dbo].[Table1]). The problem has been fixed in this update: the Diff now doesn't create the user that shouldn't be in the target DB.
• In previous versions, when changing type of a principal (e.g. from user to app role), all objects owned by this principal were moved to DBO. This (a) created possibility of a name conflict in DBO namespace and (b) the Diff should then move the objects back to the re-created principal, which it didn't. The correct solution would be to move the objects into a temporary schema (or app role), then move back to the re-created owner.
• Must not drop DBO: In some rare situations when comparing with "owner mapping", the Diff would try to remove the built-in user/schema [DBO] from the target database. In this version, none of the built-in schemas/users can be dropped.
• Missing "USE" for a new database: when the Diff was configured to include the CREATE DATABASE scripting, it didn't switch to the created database before scripting any other objects. Fixed. Note that in this situation the "USE" statement is generated unconditionally, regardless of the new "Script USE" option described above.
• The recently added check for the minimum required permissions does not work in some [rare] situations, breaking the scan with "Not enough permissions to even attempt reading schema" error, although in fact the schema could be read. In this version, the permission check still exists, however it only displays a warning without breaking the scan.
• Spaces in DataDiff row filter: if you put some spaces in the row filter expression field of DataDiff (as opposite to just leaving it empty), a "WHERE " clause with this empty expression would be included in the resulting query, which subsequently fails. Generally, DataDiff doesn't validate the filter expression so you are responsible for any incorrect syntax, but in this particular situation the spaces are not easily visible and may be very confusing. This version of Diff trims any leading/trailing spaces in the filter expression, so an "expression" containing only spaces will be ignored.
• [N]TEXT and IMAGE data conversion path: when column type changes from TEXT or NTEXT to IMAGE or back and the Diff must preserve the data, simple data conversion to the destination type won't work. In these situations, the Diff now uses two-step conversion through VARBINARY(MAX) and [N]VARCHAR(MAX) types. The correction applies both in schema sync (when the Diff tries to preserve data while changing a column type) and in DataDiff, when synchronizing data in sever-to-server mode.