Tip

SQL Server's DaVinci code for data modeling

 
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

    Requires Free Membership to View

the DaVinci tools seem to elude more DBAs and developers than just about any other feature.

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.

TABLE OF CONTENTS
   Benefits of using DaVinci
   Use caution when changing the data model
   Building a new data model
   Working with the data model
   Data dictionary data entry
   Data dictionary reporting: Table-level definitions
   Data dictionary reporting: Table and column definitions
   Conclusion

 
Benefits of using DaVinci

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.

 
Use caution when changing the data model

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.

The DaVinci tools are very simple to use, but if you do not understand how they work you can have serious problems.
 
Building a new data model

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:

 
 Screen shot Directions
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.

Table 1: New database diagram
 
Working with the data model

Once the data model is built, be prepared to use the DaVinci tools' core functionality (shown in Table 2).

 
ID Functionality Directions Screen shot
1 Save the data model On the top left portion of the interface, click the 'Disk' icon on the toolbar to save the data model.

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 database.

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.

Table 2: Add functionality to the database.
 
Data dictionary data entry

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.

 
ID Functionality Directions Screen shot
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.

Table 3: Add table and column definitions.
 
Data dictionary reporting: Table-level definitions

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

 
Data dictionary reporting: Table and column definitions

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

 
Conclusion

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



This was first published in August 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.