SQL Server 2005 Data Modeling Tools

By:   |   Comments (3)   |   Related: 1 | 2 | 3 | > Database Design


Rarely do we (Developers and DBAs) have sufficient time for our projects and every day tasks.  So when it comes to building a new system, inevitability what happens is time is limited and individual tables end up being built instead of a comprehensive data model.  As the system grows, we hope that the front end code is correct and keeps the data related, but when you have a data problem it may take hours to check and validate that the data is correct.  So how can we break this cycle and migrate from building table by table to a comprehensive data model?


Working with your team and letting them know the time needed is the first step.  The second step is exploring data modeling tools to streamline the process to be able to work on projects and daily tasks.  With SQL Server 2005, Management Studio includes the Visual Data Modeling tools.  This tool set enables you to graphically build your data model and translates the model into the appropriate T-SQL commands. 

What are the capabilities of SQL Server 2005's Visual Data Modeling tools?

The chart below outlines the primary activities that can be performed in SQL Server 2005's Visual Data Modeling tools:

ID Task Directions Screen Shot
1 Create a new data model Management Studio | root | Databases | database name | Database Diagrams | Right click on the folder | New Database Diagram | Yes (if prompted) VisualDesignTools 1


2 Portions of the Visual Data Modeling interface When a new data model is created the a blank slate is available.  Four options are available to interact with the tool:
  • Database diagram toolbar
  • Database Diagram menu
  • Right click on the work space or on an object
  • Use the Properties window (View | Properties Window) to get information relative to the active object
VisualDesignTools 2
3 Add existing tables to the data model Right click on the main work space and select the 'Add Table...' option

Highlight the tables that need to be added to the model and then press the 'Add' button

VisualDesignTools 3


4 Create new tables Right click on the main work space and select the 'New Table...' option

Enter the column names, data types and null configurations

VisualDesignTools 4


5 Add Primary Key Right click on the column that you would like to make the primary key and select the 'Set Primary Key' option VisualDesignTools 5


6 Building Referential Integrity To build referential integrity, drag and drop the primary key from the parent table to the foreign key on the child table

Then set the properties for the referential integrity

To display the referential integrity labels, click on the 'Show Relationship Labels' on the database diagram tool bar

VisualDesignTools 6a

VisualDesignTools 6b

7 Index Management Right click on the table and select the 'Indexes\Keys...' option

To create an index click on the 'Add' button and provide a name then configure the index properties

To drop an index select the index from the list on the left and press the 'Delete' button

VisualDesignTools 7


8 Constraint Management Right click on the table and select the 'Check Constraints...' option

To create an constraint click on the 'Add' button and provide a name then configure the constraint  properties

To drop a constraint select the constraint from the list on the left and press the 'Delete' button

VisualDesignTools 8


9 Saving a change script To save a T-SQL script of all changes since the last time the model was saved, click the 'Generate Change Script' from the Database Diagram tool bar

Click the 'Yes' button to save the contents to a text file in the file system

VisualDesignTools 9


10 Add notes to the data model As a best practice, annotations should be added to the model to help document the database design

This can be accomplished by right clicking on the workspace and selecting 'New Text Annotation' option and entering the comments


VisualDesignTools 10
11 Finalized Data Model Once the entire model is completed all of the tables, columns, primary keys, foreign keys, data types, etc should be configured VisualDesignTools 11


12 Sharing the data model To share the data model, it is possible to print the model or share it online in Management Studio

To print the model select File | Print

To show the page breaks, select the 'View Page Breaks' icon from the database diagram tool bar



What are the benefits of using the SQL Server 2005 Management Studio tool?

  • Be able to use a more intuitive point and click interface to build your data models
  • Have the ability to issue a save script for change management purposes and to review the T-SQL commands
  • Ability to share the data model with other users electronically or by printing a physical copy

Caution - Word to the wise...this is a live data modeling tool

  • This data modeling tool is designed to make changes to the database when the model is saved, which means this tool is primarily intended for development environments
  • You are making changes to the database, this is not a mock up tool
  • There is no undo button, once you save CTRL + Z will not bring back your table and data
  • If you drop a table in this model, you will drop it from your database, so be sure when you drop tables
  • Tables are dropped and temp tables are created with the needed columns and the old tables are dropped
  • Indexes are created on the fly which can have a performance impact on the system
Next Steps
  • If you have had a hard time writing queries or working with a user because you could not "see the data model" then reverse engineer one of your databases and share the information with your team.  See if this helps the coding and communication process
  • If you are starting on a new project, rather than building individual tables, use the Visual Data Modeling tools with SQL Server 2005 Management Studio.  See if using this tool streamlines the process and gives you a better understanding of the system from the start.
  • Once you start using the tool, be sure to make your changes in the development environment, save the change script via the tool and then save the data model to retain all of the work in the development environment.  Next migrate the script from development to test and from test to production.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, January 21, 2015 - 4:04:06 PM - Supriya Back To Top (36015)

Great post!!

It really helped me in getting the basic idea of data modelling.

Saturday, October 17, 2009 - 3:24:39 PM - admin Back To Top (4223)


Do you mean 1 to many or 1 to 1 relationship?

If so, I am not aware of that functionality in SSMS.  I know other modeling tools do support those sorts of notations.

Thank you,
The MSSQLTips Team

Wednesday, October 7, 2009 - 5:32:29 AM - logos123 Back To Top (4149)

Can we display relationship properties (not the name) on the Database Diagrams in SQL Server ?


get free sql tips
agree to terms