If a picture is worth a thousand words, then a free data modeling tool is worth a terabyte of data. That is just what you get with the SQL Server 2000 DaVinci data modeling tools. SQL Server 2000 Enterprise Manager has a number of hidden goodies, and
A data model is a visual representation of the physical tables, columns, data types, lengths, null value and referential integrity. The DaVinci tools are a basic set of visual data modeling tools used to build a data model and support a basic data dictionary, which defines all of the tables, columns and relationships that exist in the data model.
I am surprised by the number of DBAs and developers I've worked with who do not know that data modeling features exist natively in SQL Server. Their eyes light up when they get a data model, which they refer to as "wall paper." Their walls may be covered with data models from other systems that they have talked about for months or years, but never really seen. Now the are able to visually identify issues that have been floating around in their heads.
If you are already using the DaVinci tools or a product from another vendor, I commend you. I think data models are underutilized in achieving high performance. They serve as common ground between the users and other members of the IT team that need to migrate data or generate reports. These additional benefits will convince the rest of you non-users about the value of data models:
- Database design is a critical component for high performance. With a well-thought-out design that is implemented properly, high performance can be a reality.
- Improve your team's performance by using a visual representation of your database rather than reviewing T-SQL CREATE, ALTER or DROP statements that are less intuitive for most people than a graphic.
- Streamline the identification of improvements and upgrades to the database by dragging objects on the canvas and easily adding new objects via a simple point-and-click interface.
- Script out the change to the data model as well as save the coding changes to a text file to uniformly apply the changes to multiple systems.
The following is important to keep in mind so you do not lose data. When you change the data model (i.e. your pretty picture in Enterprise Manager), you are changing your database. Take care when you make the changes in your development environment, script out the changes, save the changes to a text file and apply the changes to the production environment during a maintenance window.
In situations where the tables have data, under-the-covers the DaVinci tools create a temporary table, issue a SELECT INTO statement into the temporary table, drop the current table and rename the temporary table to the original table name. This set of processing is not what you want to occur on your production environment during the middle of the day.
You can access the DaVinci tools in Enterprise Manager 2000 by navigating to the SQL Server, opening the Databases folder, expanding the individual database, left clicking on the Diagrams folder and right clicking on the right pane to select the New Database Diagram option. Table 1 below outlines the steps the wizard will follow:
|After reviewing the directions, click the 'Next' button to proceed with the Create Database Diagram Wizard.|
|From the 'Available tables' in the left pane, press the 'Add' button to include the tables in
the 'Tables to add to diagram' pane on the right.
Press the 'Next' button to proceed with the wizard.
|Press the 'Finish' button to complete the wizard and have SQL Server automatically arrange the tables.|
|The tables are automatically arranged on the canvas. The data model outlines the primary keys with a golden key and the foreign keys with a connecting line between the tables.|
Once the data model is built, be prepared to use the DaVinci tools' core functionality (shown in Table 2).
|1||Save the data model||On the top left portion of the interface, click the 'Disk' icon on the toolbar to save the data
The first time the data model is saved, you will be prompted to name the data model. This is shown in the graphic to the right.
|2||Change the view of the data model||On the toolbar, select the 'Show' icon to determine which level of detail you would like
displayed in the data model.
I typically like to use the 'Standard' option because I am able to see the table name, column name, primary key, data type, length and null property
|3||Add a comment to the data model||On the toolbar, select the 'New Text Annotation' icon to add text to the data model.
I typically like to add comments for particular groups of tables as well as notes for the data model (i.e., date, developer, database, project, etc.).
|4||Add an existing table to the data model||On the toolbar, select, 'Add table to Diagram' icon to add an existing table.
Scroll to find the table you want to add and then press the 'Add' button to add the table to the data model.
If the table has a primary key or any referential integrity to other tables, this will appear on the model.
|5||Add a new table to the data model||On the toolbar, select, the 'New Table' icon to add a table that has not existed in the
First name the table. Second add the columns, primary key, etc., for the table as outlined below.
|6||Add a column, which is really a row in one of the tables in the data model||Navigate to the table where the column should be added. Right click on the row. Select the 'Insert Column' option. Fill-in the 'Column Name,' 'Data Type,' 'Length' and 'Allow Nulls' properties.|
|7||Remove a column in a table, which is actually a row||Navigate to the table where the column should be removed. Right click on the row. Select the 'Delete Column' option. The column will be deleted without confirmation.|
|8||Assign a primary key to a table||Right click on the column name and select the 'Set Primary Key' option. A golden key will appear to the left of the column name.|
|9||Build a primary key foreign key relationship||Drag and drop the primary key column to the foreign key column by holding down the left mouse button.|
|10||Remove a table from the diagram versus delete the table from the diagram||The 'Remove Table from Diagram' option retains the table in the database, but it is removed
from the model. The 'Delete Table from Database' permanently destroys the table.
To access this functionality, navigate to the table, right click on the table and select either 'Remove Table from Diagram' or 'Delete Table from Database' option.
A hidden feature within the DaVinci tools is the ability to generate a basic data dictionary. Table 3 shows the primary screens for completing these tasks.
|1||Add a table definition||Right click on the table and select 'Properties' to access the interface. Type in the table description on the 'Tables' tab in the 'Description' text box.|
|2||Add a column definition||Right click on the table and select 'Properties' to access the interface. Type in the column description on the 'Columns' tab in the 'Description' text box.|
Once you have entered all the table descriptions, execute the following query to access all table-level definitions:
SELECT o.id AS 'ObjectID', CAST(o.Name AS varchar(25)) AS 'TableName',
CAST(p.Value AS varchar(50)) AS 'Description' FROM dbo.sysobjects o
INNER JOIN dbo.sysproperties p ON o.id = p.id WHERE p.type = 3 AND
p.smallid = 0 ORDER BY o.id, o.Name
Once you have entered all table and column descriptions, execute the following query to access table and column definitions:
SELECT o.id AS 'ObjectID', CAST(o.Name AS varchar(25)) AS 'TableName',
CAST(c.Name AS varchar(25)) AS 'ColumnName', CAST(p.Value AS varchar(50))
AS 'Description', Type = CASE WHEN p.Type=3 THEN 'Table' WHEN
p.Type=4 THEN 'Column' ELSE 'UNKNOWN' END FROM dbo.sysobjects o
INNER JOIN dbo.sysproperties p ON o.id = p.id INNER JOIN dbo.syscolumns c
ON p.smallid = c.colorder WHERE o.id = c.id AND p.type = 4
ORDER BY o.id, o.Name, p.smallid
To help you prepare for new options in SQL Server 2005, available in November 2005, I highly recommend leveraging the DaVinci tools in SQL Server 2000 if you are not already using a data modeling tool. Among other improvements, I hope the printing capabilities improve to distribute the data model as well as the representation of the primary key and foreign key relationships so that the connecting lines are relative between the two columns rather than two tables. Although these are minor points, hopefully this functionality and other aspects of the tool will be greatly improved with SQL Server 2005. Stay tuned!
ABOUT THE AUTHOR
Jeremy Kadlec is the Principal Database Engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server Users Groups and nationally at SQL PASS. Jeremy is also the SearchSQLServer.com Performance Tuning expert.Ask him a question here.
More information from SearchSQLServer.com
- Chapter: Designing effective database systems
- Tip: Navigate hierarchies using recursive CTEs
- Topic: Research database design and modeling best practices
This was first published in August 2005