Create ER Diagram for SQL Server Database Using SSMS and SQL Designer

May 18th, 2022

Entity Relationship Diagrams are widely used for analyzing database structure and troubleshooting issues with logic or deployment. In this article, we provide a detailed walkthrough of what an ER Diagram is and how to create and manage database diagrams.

Download best IDE for SQL Server - dbForge Studio

Contents

What is Entity Relationship Diagram (ERD)?

Entity Relationship Diagram comprises a graphical representation of database structure and relationships between database objects. In other words, an ER diagram is used to sketch out the design of a database and illustrate its structure.

Create a diagram from existing database

ER Diagrams vs. ER Models

ER Model is short for entity-relationship model, which is a high-level data model. It consists of a collection of entities (or objects) and their relationships. The terms ER Model and ER Diagram are sometimes used interchangeably. However, many experts prefer to separate them, emphasizing the conceptual nature of ER Model that describes the structure of data and doesn’t explain how exactly it is stored. ER Models deal with single entities like a Customer while ER Diagrams deal with sets of entities like the Customers table.

How ER Diagrams are used

Design databases

When working on a new data model, a database diagram can help capture and document system requirements. In speaking of the existing databases, Entity Relationship Diagrams can explain the logical structure and the flow of information within the databases in question, as well as assist in enhancing database throughput, debugging errors, and redesigning the architecture.

Troubleshoot and monitor databases

The use of ER Diagrams is also beneficial when it comes to locating and resolving issues with logic and deployment. Diagrams outline the structure of a database and help understand what and where goes wrong. ERD streamlines business processes by identifying redundancies and bottlenecks within business entities.

Manage business information systems

ER Diagrams are used extensively for analyzing and fine-tuning relational databases that are commonly exploited in business processes. Entity Relationship Diagrams can help understand how those databases can be restructured for data to be retrieved quicker and easier.

This list of uses of Entity Relationship Diagrams is not at all exclusive. Database diagrams are also used in business processes reengineering, education, data analysis, and research.

Create a new database diagram using SSMS

SQL Server Management Studio—an integrated environment for managing SQL Server databases—allows creating database design diagrams in a couple of clicks.

To create a database diagram in SSMS:

1. Go to Object Explorer and expand the database node.

2. Right-click the Database Diagram folder and select New Database Diagram.

Note
If the database does not have the objects necessary to create diagrams, the following message appears: This database does not have one or more of the support objects required to use database diagramming. Do you wish to create them? Click Yes.

3. In the Add Table dialog that appears, select the tables to be displayed on the diagram and click Add.

Create diagram from existing database

4. Optional: Arrange the tables on the diagram as required.

5. To save the diagram, go to the File menu, select Save Diagram, and provide a name for the new diagram in the Choose Name dialog that appears. Then click OK.

Save database model diagram

6. Once your diagram is saved, you will be able to find it under the Database Diagram folder.

Software to create database diagram - SSMS

Create a new database diagram using SQL Designer

There is a bunch of decent diagramming tools for SQL Server databases on the market. The one that really stands out is dbForge Studio for SQL Server—an all-embracing IDE for all possible SQL Server database-related tasks. The Studio boasts a powerful SQL Designer aimed at covering the needs of developers, DBAs, data and business analysts. SQL Designer embraces a number of utilities for creating and editing databases, designing tables, building database relationships, etc.

Its robust ER diagram tool allows designing database diagrams and visualizing database structures as Entity Relationship Diagrams.

SSMS Diagram tool vs. dbForge Studio’s SQL Designer

Database Diagram: SSMS vs. dbForge Studio studio

How to create a database diagram in dbForge Studio for SQL Server

In dbForge Studio, you can create a database diagram right from the Start Page. Navigate to the Database Design tab and click New Database Diagram. 

dbForge Studio tool to create database diagram

Then drag and drop database objects from Database Explorer onto the ER diagram. The database diagram will instantly display the objects and their relationships, thus facilitating further analysis.

dbForge software to create database diagram

To save a diagram:

Right-click the diagram background and select Export to Image from the context menu.

You can save the diagram in the following formats: BMP, JPG, GIF, PNG, TIF, and EMF.

Create and edit database objects on a database diagram

You can visually design databases by creating and modifying database diagrams. Well-designed databases are easy to manage and maintain, they boast improved data consistency and are cost-effective in terms of disk storage space.

Create a new database object on the diagram

To create a new database object, right-click the database background, select New from the context menu that appears, and then select the type of database object you want to create: Table, View, Procedure, or Function. The corresponding database object editor will open.

Create a new database object on a diagram

With dbForge Studio for SQL Server, you can also visually create a new foreign key relationship between two tables.

To create a new relationship:

1. Click the New Relation button on the Diagram toolbar.

2. Click the child table and move the mouse pointer, holding the mouse button pressed, to the parent table. Then release the mouse button.

3. In the Foreign Key Properties dialog that appears, provide the necessary settings and click Apply Changes.

Create a new relationship between tables on a diagram

Modify database objects on the diagram

To edit a database object, double-click it on the diagram. An object editor dialog will appear. Make the necessary changes in the editor and click Apply Changes to save them.

The View editor looks as follows:

Modify database objects on the diagram

Table Designer

Table Designer comes as part of the SQL Designer functionality of dbForge Studio for SQL Server. It comprises a visual table editor that allows you to quickly and easily add columns and manage their attributes, create comments and constraints, define indexes, set triggers, work with data, preview changes, and automatically generate SQL scripts based on these changes.

dbForge Studio Database Diagram - table designer

To save your changes, click Apply Changes.

Tasks that can be done via Table Designer

  • Create and edit tables
  • Edit and manipulate (filter, group, sort, search) table data
  • Modify column collations
  • Create and/or remove CHECK constraints, define conditions for them
  • Add and/or remove unique, primary, and foreign keys
  • Permit or restrict values that can be applied to a column
  • Add and/or remove unique and non-unique indexes
  • Define index types: Clustered, XML, Clustered/Nonclustered Columnstore, or Spatial
  • Create and manage statistics
  • Create and update triggers
  • Define storage properties
  • Edit table creation scripts

Tune the design of a database diagram

With dbForge Studio for SQL Server, you can tune the design of the database diagram with a rich set of options. Options can be set either globally for all the diagrams or individually for a particular diagram.

To set options for the current diagram, right-click it and select Options from the context menu that appears. In the Options dialog, you can configure the settings for the current diagram. You can also reset all the options to default values by clicking Reset.

Tune the design of a database diagram in dbForge Studio

On the General tab of the Options dialog, you can adjust the diagram appearance: change the background color, select to show the grid, enable shadows, etc. On the Notation tab, you can select the skin. dbForge Studio for SQL Server supports three predefined skins: the Default skin, the Simple skin, and the IDEF1X skin. The Default skin uses the richest color palette. The Simple skin is optimized for printing and does not use gradients. The IDEF1X skin is mostly black-and-white to look like IDEF1X diagrams. On the Page and Print tabs, you can set diagram printing options.

Export SQL relationship diagram

To export a diagram to an image, follow these steps:

  1. Right-click the diagram and select Export to Image from the shortcut menu.
  2. Specify the target file name and image format.
  3. Click the Save button.

How to export large diagrams

When trying to export a large diagram in dbForge Studio for SQL Server, you might encounter the following error: The error occurred while diagram exporting. Probably the image is oversized. Try export to another format.

Why does this happen? The bitmap-generating engine needs a contiguous memory area for the bitmap. Even if you have enough memory, you might still get this kind of error because of memory fragmentation.

To get around the error:

  • Export the diagram to the vector format (emf).
  • Use third-party RAM defragmentation and application optimization tools.
  • Try to restart the application and export the diagram again.

Conclusion

Grasping a database model can be quite a daunting task. It is not a problem when the database is small—in this case, it is pretty easy to comprehend how tables reference each other. However, as the database gets larger, it is often difficult to see exactly how the tables are interrelated. That’s where a good diagramming tool can come to assistance. We invite you to download and test-drive dbForge Studio for SQL Server—the IDE that will definitely exceed your expectations. We grant a free 30-day trial to potential customers so that you could evaluate the tool and fully understand its features and ease of handling.

Download dbForge Studio for SQL Server - best IDE for SQL

Comments are closed.


Your subscription could not be saved. Please try again.
Confirm your email by clicking the link in your inbox!

Subscribe to our blog

to get the latest posts delivered to your inbox