How to Create a Database Diagram in SQL Server: A Step-by-Step Guide

Knowing how to create a database diagram in SQL Server allows you to design and manage databases visually directly within SQL Server Management Studio (SSMS). In this guide, Viettel IDC will walk you through each step to create and visualize your database structure quickly and efficiently.

How to Create a Database Diagram in SQL Server: Step-by-Step Instructions

Step 1: Open the Database Diagrams Feature in SSMS

- Launch SQL Server Management Studio (SSMS) and connect to your SQL Server instance.

- From the View menu, select Object Explorer (F8).

- Expand Databases and locate the database for which you want to create a diagram (for example, Northwind).

Step 2: Create a New Database Diagram

- Right-click Database Diagrams and select New Database Diagram.

If this is your first time using the feature, SSMS will prompt you to create the required support objects for Database Diagrams. Simply click Yes to continue. Once completed, the Database Diagrams folder will be enabled and ready to use.

Step 3: Add Tables to the Diagram

- In the Add Table dialog box, select the tables you want to include in the diagram.

- Hold Ctrl to select multiple tables.

- Click Add, then click Close.

To automatically include related tables:

- Right-click a table in the diagram.

- Select Add Related Tables.

- SSMS will automatically add tables connected through foreign key relationships.

If your database tables have not been created yet, you can create them using the CREATE DATABASE and CREATE TABLE statements.

Example:

CREATE TABLE Customers (

    CustomerID INT PRIMARY KEY,

    FullName NVARCHAR(100),

    Phone NVARCHAR(20)

);

To execute the SQL statement:

- Select the database.

- Click New Query.

- Enter your SQL command.

- Press F5 to run the query.

Step 4: Remove Unnecessary Tables from the Diagram

If there are tables you do not want to display:

- Right-click the table.

- Select Remove From Diagram.

This removes the table from the diagram only and does not delete it from the database.

Step 5: Rearrange the Diagram Layout

- Right-click an empty area within the diagram.

- Select Arrange Tables.

SSMS will automatically reposition tables to reduce overlap and improve readability.

Step 6: Adjust Table Size and Position

- Use the Zoom feature to zoom in or out.

- Select a desired scale such as 25%, 50%, or 100%, or choose To Fit to fit the entire diagram within the viewing area.

- Drag and drop tables to change their positions.

- Resize tables by dragging their borders.

Step 7: Automatically Resize All Tables

- Press Ctrl + A to select all tables.

- Right-click any selected table and choose Autosize Selected Tables.

SSMS will automatically adjust each table's size to fit its column contents.

Step 8: Copy the Diagram to the Clipboard

After finishing your modifications:

- Click Save and provide a name for the diagram.

- Right-click an empty area of the diagram.

- Select Copy Diagram to Clipboard.

Step 9: Paste the Diagram into Microsoft Word

- Open Microsoft Word.

- Create a table with one row and one column (a single cell).

- Paste the diagram into the cell.

Step 10: Save the Word Document or Export It as a PDF

Once you have completed your edits:

- Save the document by selecting File → Save As and choosing the .docx format.

- To share or print the diagram, export it as a PDF by selecting File → Save As → PDF or File → Export → Create PDF/XPS.

How to Export a Diagram in SQL Server

After creating and formatting your database diagram, you can export it by copying it to the clipboard and pasting it into Word, PowerPoint, or another documentation tool before saving it as an image, document, or PDF file.

Benefits of Using Database Diagrams in SQL Server

A SQL Server Database Diagram is a visual tool that enables users to view, design, and manage database structures directly within SQL Server Management Studio (SSMS). It serves as a map of your database, helping you quickly understand how data is organized through:

- Tables and columns within each table.

- Primary keys and foreign keys.

- Relationships between tables established through foreign keys.

Creating database diagrams in SQL Server provides numerous practical benefits for database design, management, and analysis.

Visualize Table Relationships More Clearly

Database Diagrams make it easy to see how tables are connected through primary and foreign keys. This visual representation helps you understand data flow without manually reviewing every table or SQL script.

Improve SQL Learning and Training

Microsoft SQL Server is one of the most widely used relational database management systems. Beginners can learn database structures more effectively through visual diagrams rather than relying solely on theoretical concepts.

Accelerate Existing Database Analysis

Database Diagrams allow users to quickly understand database structures and relationships without manually examining each table. This makes system evaluation and database handover processes faster and more accurate.

Reduce Database Design Errors

A visual schema helps identify how tables should be connected, minimizing misunderstandings during database design. Clear visibility into relationships reduces the likelihood of structural errors.

Improve Query Performance

By reviewing the diagram, developers can identify redundant relationships or tables that may benefit from additional indexing. This helps optimize query performance while reducing manual analysis efforts.

Enhance Database Normalization

Database diagrams make it easier to identify duplicate data or improperly structured tables. With a complete view of the schema, moving toward higher normalization levels such as 2NF and 3NF becomes more straightforward and accurate.

Facilitate Communication Across Teams

Database diagrams act as a common language between developers, DBAs, business analysts, and stakeholders. Even non-technical team members can better understand database structures through visual representations rather than lengthy technical documentation.

FAQs

Many developers encounter issues when creating database diagrams in SQL Server. Below are some common problems and their solutions.

How to Fix Database Diagram Creation Errors in SQL Server

If SQL Server has not initialized the required support objects or if the sysdiagrams table is corrupted, you may encounter errors when creating Database Diagrams.

To resolve the issue:

- Verify that you have sufficient permissions.

- Ensure the database has a valid owner.

- Check the integrity of the sysdiagrams table.

If the sysdiagrams table is damaged, you can allow SQL Server to recreate it automatically or restore it from backup if existing diagrams need to be preserved.

Once the supporting objects are repaired, the Database Diagram feature should function normally.

Error: "Cannot insert the value NULL into column 'did'"

The error message "Cannot insert the value NULL into column 'did'" typically occurs when creating or saving a Database Diagram in SQL Server.

The primary cause is a corrupted sysdiagrams system table. The column responsible for generating unique diagram IDs is improperly configured, preventing SQL Server from assigning a new diagram identifier during the save process.

To fix the issue, review the structure of the sysdiagrams table. Two common solutions are:

If you do not need to preserve existing diagrams:

- Delete the corrupted sysdiagrams table.

- Reopen Database Diagrams.

- Allow SQL Server to create a new, properly structured table.

- Create the diagram again.

If existing diagrams must be retained:

- Back up the current diagram data.

- Rename the existing table.

- Create a new sysdiagrams table with the correct structure.

- Restore the diagram data.

- Continue using your diagrams as before.

Conclusion

Understanding how to create a database diagram in SQL Server is an essential skill for SQL beginners, developers, and database administrators working with complex database environments. Combined with proper use of SQL Server Management Studio, Database Diagrams help visualize table relationships, streamline database design, and improve data analysis efficiency.

If you need a stable, secure, and high-performance server environment to deploy your SQL Server databases, Viettel Cloud Server is an ideal choice. Built on Tier III-certified infrastructure, it delivers fast data access, seamless scalability, and reliable performance for both businesses and individual developers.