· 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.
· 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.
· 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.
· 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.
· 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.
· Exception after dropping objects and automatic one-side rescan. When you script DROPs for all objects in a category (e.g. all procedures) then apply the script to the right-hand database, and the Diff is configured to auto-refresh schema after running a script, the Diff would crash. This happened because the Diff only reloaded the right-side (changed) schema and some references to the deleted objects remained in the other one. Fixed: now the Diff properly cleans up its internal links before each comparison.
· Exception reading FOREIGN KEYs (rare). There appeared to exist a rare situation when the Diff would not correctly read from the database the referenced (target) table for a FOREIGN KEY. That would result in an "Access violation" exception while comparing or later displaying the schema. Although we were unable to reproduce the situation or fully understand how it could happen, additional checks have been added to the schema reader to report and discard any "incomplete" FKs before they are added to the in-memory schema model. We'd be very much interested to see examples of such incomplete FKs and the relevant SQL scripts.
· Exception reading indexes on SQL 2005 (rare). Each UNIQUE and PRIMARY KEY constraint in MS SQL has its underlying index. The server keeps records for the constraint itself and for its index in separate system tables. The two are associated by name and the server normally keeps both names in sync even when either constraint or index is being renamed. However, it seems to happen sometimes that a PK name gets upper-cased on the server whereas its corresponding index name remains as originally entered. When that happened, the Diff would fail to associate an index with the PK, causing an "Access violation" error. This only affected reading indexes from MSSQL2005 servers. The only way we were able to reproduce the problem was by manually changing one of the names in [sysobjects] (under SQL 2000), then re-attaching the database to a SQL 2005 server. Since several users have reported the problem, it is likely that such inconsistency in the system tables was caused by some SQL 2000 database management tool which directly modified the system tables before the database was transferred to SQL2005. In this version, the schema reader has been modified to properly handle such situations.
· DataDiff scripting generated all string literals as N'...' even if the column was not a Unicode one. Fixed: now the Diff generates N'...' literals only for Unicode columns (NCHAR()/NVARCHAR()/NTEXT()).
· DataDiff scripting didn't use the Uppercase/Lowercase formatting options. Fixed.
· In SQL 2005, when scripting REVOKE permissions from users, schemas and other entities, the last version of Diff would not include the type qualifier (as in 'revoke control on USER::user1 from user2') required by the MS SQL syntax. Fixed.
· When changing from a column-level permission to a table level one, the last version of Diff would script them in a wrong order, e.g. 'GRANT UPDATE on T to user2; REVOKE UPDATE on T(col1) to user1;'. In this situation revoking the old column-level permission is redundant, but even if it is done, it must be done first. Fixed.
· Some static elements on the connection panel were not reliably repainted when the Diff window was coming to the foreground. Fixed.
· This version is an interim update with just a couple of minor corrections concerning comparison of comments and scripting of indexes. More changes are on the way.
· Bug fixes
· Fixed the problem with ignoring comments in procedures, etc. Even with "[x] Ignore comments" option set, the Diff sometimes marked a difference if a comment was present in one procedure, but not in another.
· Syncronizing an index, the Diff scripted DROP INDEX/CREATE INDEX twice. Fixed.
· The DataDiff now ignores TIMESTAMP columns by default, it also won't script them in INSERTs even if they are compared.
· Pending changes (for the next update)
· Full support for XML indexes and non-key index columns (the INCLUDE() clause) in SQL2005.
· Support of partitioning syntax ("ON partition_schema(column)") for tables and indexes in SQL2005.
· More precise control over permission scripting should be added, including the options to either synchronize permissions or to re-create the original permissions when an object is re-created. The same should be done for extended properties.
· This update includes several important corrections in script generation and reporting.
