Question :
What is the difference between SQL Server databases A & B where:
A. Database restored from lower level compatibility(100) to higher compatibility (130)
B. New Database created in higher compatibility (130)
Answer :
I’m not sure if this gets to the root of your question – if not, please update your question with more information.
According the the documentation for ALTER DATABASE (Transact-SQL) Compatibility Level, the compatibility mode of a database:
Sets certain database behaviors to be compatible with the specified
version of SQL Server.
As I understand the documentation, two databases running under the same compatibility mode should operate the same way with regard to those specific behaviors.
Restored databases do not (necessarily) automatically change their compatibility mode.
From the documentation:
When a database is upgraded from any earlier version of SQL Server,
the database retains its existing compatibility level, if it is at
least minimum allowed for that instance of SQL Server. Upgrading a
database with a compatibility level lower than the allowed level,
automatically sets the database to the lowest compatibility level
allowed. This applies to both system and user databases.