Contents 

AdeptSQL Diff Online Help

This dialog controls most of the configurable settings of the program. You can invoke it via "Tools/Schema Options" command in the main menu or from the toolbar. The dialog consists of several pages, arranged into a tree according to the subsystems they control. Please click on the screenshots to see detailed information about a particular group of options.

If you don't need to see and synchronize certain kinds of schema information, like extended properties, permissions or system tables, you can exclude the corresponding steps from the loading process, therefore saving some time loading the schemas. Naturally, the excluded items also couldn't take part in the comparison and scripting.

Note that the Diff will know nothing about these ignored objects, so it will not be able to script them or preserve them in the target database when any changes are made. For example, if you turn off scanning for object permissions, any permissions for an object in the target database will be lost when that object is synchronized.

The options on this page don't take effect until the schema is refreshed. The Diff checks if you've actually changed any of the options and prompts to reload the schema(s) if necessary. You can also manually reload the schemas at any time by pressing the [Refresh] toolbar button.

System objects. Normally the program ignores all system databases when you are selecting the databases for comparison and also ignores any system tables and other system objects during the comparison. But if you ever need to compare system databases (e.g. the MODEL) or system tables in a user database, just uncheck this option.

Predefined roles. The program reads all entries from the 'sysusers' table, including built-in roles like 'db_owner'. In most cases you don't need to see them, so this option allows to filer them out.

Checking for table data. During the schema scan, the Diff would normally check which of the tables actually contain data. For one thing, this allows the program to enable or disable the Compare Data command depending on the selected table being empty or not. More important, this information can be used to optimize some of the generated SQL, because if a table doesn't contain data, it need not be preserved during the changes.

Note that the actual number of rows is not required for the two tasks mentioned above. If the row count is available, the DataDiff will display it in the progress dialog while comparing table data, but otherwise it is not used. Actually counting rows for every table in the database can significantly slow down the scan scan.  Therefore, starting from Diff 1.90,  the default setting is just to check the presence of data (using SELECT TOP 1...) in each table.

If you disable the check for data, that will make the loading of schema a bit faster and the program will assume that all tables do contain data.  

If system tables are included in the scan (see above), the Diff will never check them for data, assuming that system tables always have some (and it would be a very bad idea to do any schema or data synchronization between them, anyway!)

Filtering by owner name. With this option you can restrict the loading to objects belonging to a specific user (in SQL 7.0, SQL 2000) or a specific named schema (SQL 2005).  This affects all objects from [sysobjects] table (tables, views, constraints, triggers, procedures, functions) as well as user-defined types.  Note that if there are dependencies between the loaded objects and objects belonging to some other user/schema (and therefore not loaded), the Diff will not take such dependencies into account and may notbe able to generate a correct synchronization SQL. [Note:  filtering by owner/schema name is obviously a connection-specific option, so in future versions it is likely to be moved from here to the connection panel]

Pre-load object counts. This indicates whether or not the Diff should run a query to obtain estimated numbers of various kinds of schema objects it is going to read. Pre-loading the counters allows to display realistic progress indicators, but obviously the query itself adds some time to the schema scan. This option does not affect the completeness of the resulting schema.

This page controls the way named objects are associated with each other during the schema comparison.

Case sensitivity. The default and the most reasonable setting for the case-sensitivity is Automatic. Although SQL is usually considered a case-insensitive language, for MS SQL this depends on the collation order specified for a database when it was created (and, therefore, for the system tables containing object names).  With the Automatic setting, the Diff runs case-sensitive name comparison only if both databases use case-sensitive collations for object names.

Comparing and mapping owner/schema names. This option group allows to handle situations when equivalent objects in the two databases have different owner names (or schema names in SQL2005). This way, for example, table DBO.Employees on your development server may be compared to Joe.Employees on the production server. You can choose to either ignore owner names completely or (recommended) to specify mappings between the owner names ("dbo=Joe", in this example). The mappings can be added in a pop-up dialog which appears when you click on the [Edit mappings...] button.

This page controls how strict the comparison of stored procedures, triggers, functions and views should be. Each such entity has a piece of SQL script as its body and this SQL may (or may not) be equivalent to another piece of SQL regardless of minor changes in spacing, indentation, character case or comments. If Lexical comparison is choosen, the SQL is converted into a stream of lexical tokens and those are compared. This is the preferrable way.

In this case all SQL keywords will be case-insensitive and the case-sensitivity of identifiers will be determined by the "Name comparison" settings above. If you don't want to have SQL comments compared, they can be removed from the token stream before the comparison by setting the Ignore comments option. When you un-check the Lexical comparison option, the two SQL definitions will be compared as monolithic strings, together with spaces, newlines and everything. Still, the case-sensitivity option would apply in this case, as well.

The Smart comparison option works only for expressions, such as those in DEFAULT or CHECK constraints or in computed columns. The server parses such expressions and tends to insert extra parenthesis around numeric literals and generally re-format them before storing in the database. This "smart comparison" option allows to normalize numeric literals before they are compared and removes parenthesis around them, so some of the minor changes introduced by server's parsing and reformatting can be eliminated.  It does not solve the problem completely, as other changes may involve adding/removing of optional parameters (e.g. in CONVERT(...)) or of parenthesis around sub-expressions.

NOTE that all these settings only apply to the schema tree comparison, but not to the side-by-side one. In other words, they determine if a specific pair of procedures will be marked in the schema tree as identical or as changed. When you double-click on this schema item and the procedures are displayed in the side-by-side comparison window, this (visual) comparison is governed by a completely separate set of rules. These rules can be seen and configured in a separate dialog, called from "Tools/Compare Options" menu command.

This inconsistency was caused by the specifics of the current implementation and should be eliminated in future versions. Meanwhile, it is worth to remember that it is possible to configure the text comparison in such a way that two procedures may appear identical in the schema view but different in the side-by-side comparison, or vice versa. Such situations may be confusing and you should avoid them.

Although this page of the dialog should be rather self-explaining, let's consider two examples of the situations which these two settings control.

Do changes in UDF make difference? Suppose you use some EmployeeID in various tables throughout the database. The ID is just an integer and so it is defined in the older (let's say production) database: "EmployeeID int not null". However you wished your scripts to look nicer and in the new (let's say development) version replaced them with a new user-defined type: "EmployeeID TEmployeeID not null". The actual type has remained INT. Do you wish the Diff to show all such columns as changed and try to synchronize them?

Do changes in physical type make difference? Suppose both databases are now synchronized and define all such columns as TEmployeeID. Then for some reason you decide that numbers are not good enough and TEmployeeID must be CHAR(5) instead. You rebuild from scratch your development database with this new definition and now comparing with the production version again. Both version have columns defined via TEmployeeID, but the actual physical types behind them are different. Do you need the Diff show all these changes and try to synchronize them?

Ignore FILEGROUP clause. If you check this option, the Diff would never script ON FILEGROUP clause for tables and indexes, and neither it will script filegroup definitions on the database level (ALTER DATABASE ADD FILEGROUP...). Note that regadless of this flag, the Diff never scripts ON FILEGROUP for tables and indexes which are on the default filegroup anyway.

Ignore FILLFACTOR attribute. This option is checked by default, which means the Diff would not script "WITH FILLFACTOR=..." for indexes and constraints.

Ignore NOT FOR REPLICATION. The NFR flag in IDENTITY columns, contraints and triggers will be ignored on comparison and not scripted.

Ignore COLLATE. The collation order for character columns and for the entire database will not be compared or scripted.

Options listed in the second frame don't affect loading from the database, but make certain object properties to be ignored during comparison (differences in these properties will not be displayed in the schema tree) and not scripted in either CREATE or ALTER scripts.

Case-sensitivity. Specifies whether the comparison of identifiers will be case sensitive or not. This also affects the sort order of objects in the schema tree. Identifiers in the compared databases may be either case sensitive or not, depending on the collation orders specified for each of these databases of for their servers. The Diff does not try to determine this "real" case-sensitivity, but leaves it to you to choose.

The Ignore changes in expressions option should be considered a temporary workaround, until a smarter algorithm (fully parsing expressions and comparing the expression trees) appears in future versions.

Separator - specify the one used when generating SQL from schema.

Delimiter on a new line - usually checked when the delimiter is a word (like "go"). The SQL looks better when "go" is placed on its own line.

Indentation step - used when generating CREATE TABLE statements and other similar code.

Align column types - when generating CREATE TABLE statement, the program can check the lengths of all column names in the table and pad them with blanks so that all data type would start at the same column. With fixed padding modifier adds additional padding, if necessary, to align the data types to a specific column.

Align NULL etc - similar to column alignment above, but aligns everything that goes in a column definition after the data type (NULL, NOT NULL, constraints).

Put column constraints on a new line - puts each such constraint on a new line, indenting it against the start of the column definition. If this is not checked, all constraints for a column go on the same line.

Character case options. Specifies whether the resulting SQL should be in lowercase, in upper case, with capitalized first letter in each word or just left unchanged. You can specify this option separately for the reserved SQL keywords and for identifiers.

Using fully-qualified names. Choose when the owner name should be included into the object names: never, always or if different from a database owner or the currently connected user. This applies both to the display in the Schema View and to the names in generated scripts.

MS SQL Server supports two styles of "quoting" identifiers which contain non-alphanumeric characters: by using square brackets [] or by using double-quotes. The latter may be easily confused, both by the programmer and the server, with the way you represent string literals (either with double or single quotes). The Diff allows you to use either style of notation and here is where you can choose it.

Normally, the Diff would only put the quotes or brackets where they are needed, which leads to more compact and readable SQL. The quoting is needed around names that contain spaces, any other special or local characters, or can be confused for a reserved word.  

By checking the Quote all option you can force it to quote all identifiers, just like MS Query Analyzer does.

To protect you from accidental loss of data, the Diff can put transaction code around any generated SQL. See also here for the details of how SQL Code Viewer supports transactions.

These settings control the situation when you select the root node of the schema tree and command the program to script the entire schema.

Generate CREATE/ALTER DATABASE option enables scripting on the database level itself. When this option is checked and CREATE scripts are generated for the whole schema, they will start with CREATE DATABASE statement. When an update script is being generated, it may include ALTER DATABASE to synchronize names, filegroups and various options of the two databases. By default it is assumed that both databases are already in place and their names, filegroup allocation and properties don't require any synchronization.

With "Produce only CREATE DATABASE", the program will only script database-level information when you select the root node of the schema: CREATE/ALTER DATABASE, database options, db-level extended properties and statement permissions. To script tables, views and other objects you would have to select appropriate sub-nodes of the schema tree.

Set database options. Here you can choose exactly which of the database-level options the program should take into account generating a CREATE or UPDATE script. The options can be scripted using either "ALTER DATABASE ... SET ..." syntax or by calling sp_dboption.

Do not script users... It may be a common situation when users defined on a development server and on a production server are different and do not require synchronization. This options tells the Diff that when the schema root is selected for scripting, the SQL for creating or otherwise synchronizing the users should not be included in the script.

The Constraint placement comboboxes allow to choose where in the table definition various types of constraints will be scripted. This setting primarily applies when scripting a CREATE TABLE statement (when two tables are being synchronized, it is:

• Don't generate - these constraints are not included into the "create table" statement. You can generate them later from the "Summary Collections" node of the schema tree, or may be you just want to get rid of some of the constraints. For FOREIGN KEYs this can help to avoid forward references to the tables which aren't defined yet.
• With column means that whenever a constraint depends on a single column only, it will be scripted with this column (e.g. "UserID int references Users(UserID)"). Constraints depending on several columns will be scripted on the table level.
• At table level - the constraint will be scripted inside the CREATE TABLE statement, after all columns. This is not a valid setting for DEFAULT constraints, so the combobox for them does not contain this option.
• Separate - the constraint will be scripted in a separate ALTER TABLE ... ADD statement after the table definition. This setting can be specified for any kind of constraint, although it usually only makes sense to do so for the FOREIGN KEYs.

Checked

Unchecked

T2 (
    ID Integer Not Null,
    Name VarChar(30) Not Null,

    Primary Key(ID,NAME)
);

create table T2 (
    ID Integer Not Null,
    Name VarChar(30) Not Null,

    Constraint PK_T2_014935CB
        Primary Key (ID,NAME)
);

The QI/AN settings. There are two settings (QUOTED_IDENTIFIERS and ANSI_NULLS) which MS SQL Server actually keeps with each stored procedure (and also view, function or trigger). These checkboxes control if the QI and AN settings should be considered part of procedure definition and scripted before ALTER or CREATE PROC.

They also affect the comparison of procedures (and other code objects): if two procedures are identical except for their QI and/or AN settings, they will be marked as changed only if the scripting for SET QI / SET AN is enabled.

Additionaly, there is the "Always/When needed" sub-option for the QUOTED_IDENTIFIER setting. The QI setting only affects a procedure when it has any names or string literals enclosed in double-quotes, otherwise the difference in QI can be safely ignored. So if you choose "Always", the Diff will detect (and script) changes in QI for all code objects, otherwise it will do so only for objects having some double-quoted tokens in their bodies.

Encrypted objects. MS SQL Server can keep procedures, functions, triggers and views in the database in encrypted form, which means accurately comparing and scripting them is impossible. If one database has an encrypted object and the other has the same object in non-encrypted form, there is no way to tell if they are actually identical or not.

If the "Always consider changed" option is checked, such objects should be considered different and synchronized, otherwise they would be ignored.

The "Keep encrypted" option specifies whether such object should be scripted WITH ENCRYPTION in an update script. It is logical to assume that if a procedure was encrypted and we are updating it, we should make sure it remains encrypted, even if its counterpart in the other database isn't. The next two options work in combination with "Keep encryption". The "Script with SQLShield encryption" is only useful for those who have SQL Shield server plugin installed. With this option ON, the Diff would generate a special "magic" comment ("WITH /*sqlshield*/ ENCRYPTION") causing the plugin to encrypt this procedure in a special "unbreakable" way. "Protect from tracing" causes another "magic" comment to be generated in encrypted procedures: "WITH ENCRYPTION /*sp_password*/". When a procedure "WITH ENCRYPTION" is sent to the server, its full definition can be caught in a MS SQL Profiler log, which is a security risk. However, if the profiler finds "sp_pasword" anywhere in the SQL (even in a comment), it would not show the statement text in the log.

The "Script DROP/CREATE" section allows to configure the Diff to generate DROP / CREATE sequences for the selected type of objects instead of using ALTER.

Ignored 'WITH' options.  When checked, these options force the Diff to ignore the differences in 'WITH RECOMPILE' and/or 'WITH EXECUTE AS ...' during the comparison as well as preserve them when producing the synchro script. Let's say that a procedure P in the left-hand database doesn't have "WITH RECOMPILE", whereas the procedure P in the right-hand (target) database does have this attribute. If "Ignore WITH RECOMPILE" is set, this will not be considered a difference. If the two procedures don't have other differences, they will be marked as identical in the schema tree and not included into update scripts.  If there are other differences, the procedures will be scripted using the WITH RECOMPILE setting of the procedure in the target database.  This is the same logic as used when comparing/scripting "NOT FOR REPLICATION" or "WITH ENCRYPTION" attributes

Schema Highlighting... Specifies the font styles and colors used to highlight differences in the schema view. Select any of the four items in the tree and modify its appearance using the font style checkboxes and the color selection button.

Use tooltips to display...  When you hover the mouse cursor over a schema item, the program displays a popup hint (tooltip). It maybe either the explanation of the difference highlighting (like "This item is in the left-hand database only") or the full text of the item if it doesn't fit into the window. The latter is more informative once you get used to the color highlighting.

Toolbar button style. The Diff maintains two sets of button images: the system-color old style glyphs and better looking XP-style true-color ones.  Unless you have a real crappy 256-color video, the Diff will start up with the true-color button set.  You can switch to the old-style buttons, if you like (requires a restart of the program)

Show the horizontal scroller. Unless you check this, there will be no scroller at the bottom of the schema view, which saves space for another line or two of the schema tree.

Expand changed nodes. Once the Diff has scanned the two schemas, it either expands the whole schema tree or only those collection nodes that have some changes inside. For complex databases, it may be more convenient to have this option checked.

Some of the dialogs in the program have a "Don't show me again" checkbox. Once it is checked, that dialog would not be shown any more and the only way to re-enable it would be from from this options page.

There are objects in the schema that are normally displayed under their parent objects (triggers, indexes, etc - see the screenshot).  As it is sometimes useful to see all such objects in a single list, the Diff provides what we call 'summary collections':

This options page allows you to specify which types of schema objects should or should not be represented in their summary collections.