Relationship Between Visual Database Tools and Databases
To work with the Visual Database Tools, you need to connect to a database. For details, see Adding New Data Connections in Server Explorer. You can connect to several databases at once.
The database management system (DBMS) affects the available tool features
The type of database you connect to can affect the behavior of the tools, because different commercial DBMS products offer different features. The Visual Database Tools are sensitive to the features present in the DBMS you connect to. Thus, some dialog boxes and property pages will vary depending on the underlying DBMS. This variance might be as simple as different lists of values in a drop-down list, or it might be as substantial as the presence or absence of some dialog-box controls.
In addition, the Query and View Designer will construct SQL in the particular dialect of the underlying DBMS to which you are connected.
The tools retain your work in memory
The Visual Database Tools retain local copies of your work before you save it to the database. This means that you can make changes freely without immediately affecting the underlying database. Thus, you can experiment with "what if" scenarios.
When you finish your modifications to any database object, you have three choices:
- You can save the changes, propagating them to the database. You can save database objects such as tables, diagrams, triggers, indexes, stored procedures, user-defined functions.
- You can create a script file containing the SQL code generated by your changes. Later, you or another user can run this script file to modify the database accordingly. For more information, see Saving a Change Script.
- You can discard your changes. In Query and View Designer, discarding modifications is straightforward; simply close the window without saving your work. But in Database Designer and Table Designer, there are special considerations. For details, see Discarding Changes Made in Database Designer or Table Designer.
The Visual Database Tools employ several strategies for retaining local copies of your work. One strategy applies to the work you do with the Database Designer and Table Designer. For more information, see Interactions Among Database Diagrams and Table Design Windows. Another strategy applies to the work you do with the Query and View Designer. For more information, see Interactions Among Query and View Designer Windows
The tools support multiuser environments
You can work with Visual Database Tools in a multiuser environment — an environment in which more than one user can simultaneously connect to and change the database. When you save a modified database object, the Visual Database Tools verify that the object has not been modified since you last saved it. You might need to reconcile your changes with changes already made by another user. For more information, see Multiuser Environments.