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 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]
[TABLE]
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:
[TABLE]
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 mai
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
ntenance 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.
[TABLE]
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:
[TABLE]
Table 1: New database diagram
[TABLE]
Once the data model is built, be prepared to use the DaVinci tools' core functionality (shown in Table 2).
[TABLE]
Table 2: Add functionality to the database.
[TABLE]
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.
[TABLE]
Table 3: Add table and column definitions.
[TABLE]
Once you have entered all the table descriptions, execute the following query to access all table-level definitions:
[TABLE]
Once you have entered all table and column descriptions, execute the following query to access table and column definitions:
[TABLE]
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
