Database objects are identified by schema and name. Name check is case-insensitive, so for example table myTable vs MyTable is considered as the same table.
Views, stored procedures, functions and trigger definitions are retrieved using the OBJECT_DEFINITION() function with some further adjustments of object name inside definition text.
Other objects, including Table definitions, are built by reading object properties from different system metadata tables and views. Application supports the most commonly used object attributes. However in many cases it is only the subset of SQL Server specifications, MssqlMerge does not support some SQL Server language features. Such features are not recognized and if an object has changes in any non-supported attribute then such change is ignored. In case of object merge these attributes can be lost in the target database. Below you will find the information about supported/unsupported features.
All objects are compared as their text presentation, that's the text you observe if you open an object in a Text diff tab (little exclusion made for Table definitions in case of 'Ignore column order' option enabled). In the Pro you can set up text diff options to ignore some general text-related changes like case-insensitive or ignore-whitespace.
Schema definition is always generated in the following form:
CREATE SCHEMA schema_name AUTHORIZATION owner_name
Table definition is considered as changed if it has changed the list of columns or columns have different properties (data type, nullability, identity, expression) or if it has changed constraints. It can be also configured to take into account column order, indexes, triggers and change tracking options.
Here is the high-level presentation of supported/unsupported table definition attributes, based on SQL Server CREATE TABLE specification. Unsupported ones are shown as crossed out:
CREATE TABLE schema_name.table_name
[ AS FileTable ]
( { <column_definition>
| <computed_column_definition>
| <column_set_definition>
| <table_constraint>
}
[ ,... n ]
[ PERIOD FOR SYSTEM_TIME ( .. ) ]
)
[ ON .. ]
[ TEXTIMAGE_ON .. ]
[ FILESTREAM_ON .. ]
[ WITH ( .. ) ]
<column_definition> ::= column_name <data_type>
[ FILESTREAM ]
[ COLLATE collation_name ]
[ SPARSE ]
[ MASKED WITH ( .. ) ]
[ [ CONSTRAINT constraint_name ] DEFAULT constant_expression ]
[ IDENTITY [ ( seed , increment ) ]
[ NOT FOR REPLICATION ]
[ GENERATED ALWAYS AS { ROW | TRANSACTION_ID | SEQUENCE_NUMBER } { START | END } [ HIDDEN ] ]
[ [ CONSTRAINT constraint_name ] {NULL | NOT NULL} ]
ROWGUIDCOL[ ROWGUIDCOL ]
[ ENCRYPTED WITH .. ]
<data_type> ::= [ type_schema_name. ] type_name
[ ( precision [ , scale ] | max | [ { CONTENT | DOCUMENT } ] xml_schema_collection ) ]
<computed_column_definition> ::= column_name AS computed_column_expression
[ PERSISTED [ NOT NULL ] ]
<table_constraint> ::= [ CONSTRAINT constraint_name ]
{
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
( column_name [ ASC | DESC ] [ ,... n ] )
[ WITH .. ]
[ ON .. ]
| FOREIGN KEY
( column_name [ ,... n ] )
REFERENCES referenced_table_name [ ( ref_column [ ,... n ] ) ]
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
}
Note that this definition excludes items defined as table_index, column_constraint, column_index and constraint specification for computed_column_definition. That's because MssqlMerge generates scripts for constraints as separate ALTER TABLE statements. table_constraint is listed here just to demonstrate which constraint features are supported. Scripts for indexes are also generated as separate CREATE INDEX statements.
Here is the high-level presentation of supported/unsupported index features, based on SQL Server CREATE INDEX and CREATE COLUMNSTORE INDEX specifications. Unsupported ones are shown as crossed out:
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON <object> ( column [ ASC | DESC ] [ ,...n ] )
[ INCLUDE ( column_name [ ,...n ] ) ]
[ WHERE <filter_predicate> ]
[ WITH .. ]
[ ON .. ]
[ FILESTREAM_ON .. ]
CREATE CLUSTERED COLUMNSTORE INDEX index_name
ON <object>
[ WITH .. ]
[ ON .. ] | [ ORDER .. ]
CREATE [ NONCLUSTERED ] COLUMNSTORE INDEX index_name
ON <object>
( column [ , ...n ] )
[ WHERE <filter_expression> [ AND <filter_expression> ] ]
[ WITH .. ]
[ ON .. ]
Here is the high-level presentation of supported/unsupported features of user-defined data types and table types, based on SQL Server CREATE TYPE specification. Unsupported ones are shown as crossed out:
CREATE TYPE [ schema_name. ] type_name
{
[
FROM base_type
[ ( precision [ , scale ] ) ]
[ NULL | NOT NULL ]
]
| EXTERNAL NAME assembly_name [ .class_name ]
| AS TABLE ( { <column_definition> | <computed_column_definition> [ ,... n ] }
[ <table_constraint> ] [ ,... n ]
[ <table_index> ] [ ,... n ] } )
}
<column_definition> ::= column_name <data_type>
[ COLLATE collation_name ]
[ NULL | NOT NULL ]
[
DEFAULT constant_expression ]
| [ IDENTITY [ ( seed ,increment ) ]
]
[ ROWGUIDCOL ]
<data type> ::= [ type_schema_name . ] type_name
[ ( precision [ , scale ] | max | [ { CONTENT | DOCUMENT } ] xml_schema_collection ) ]
<table_constraint> ::=
{
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
( column [ ASC | DESC ] [ ,...n ] )
[ WITH .. ]
| CHECK ( logical_expression )
}
Note that this definition excludes column_constraint because MssqlMerge is using a more universal table_constraint which works both for single-column and multi-column constraints.
Here is the high-level presentation of supported/unsupported sequence features, based on SQL Server CREATE SEQUENCE specification. Unsupported ones (NO MINVALUE and NO MAXVALUE) are shown as crossed out. If sequence was created using any of these options then it will be replaced with minimum/maximum value of the data type of the sequence object, as provided by SQL Server metadata.
CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH <constant> ]
[ INCREMENT BY <constant> ]
[ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
[ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ <constant> ] } | { NO CACHE } ]
Based on SQL Server CREATE XML SCHEMA COLLECTION specification, all the XML schema features are supported:
CREATE XML SCHEMA COLLECTION [ <relational_schema>. ]sql_identifier AS Expression
Last updated: 2024-04-25