I am studying "Beginning SQL Server 2008 Express for Developers
From Novice to Professional" by Robin Dewson. On pages 191 to 198, it
deals with database diagrams. Following the instructions for creating
one did not work.

I expanded the Database Diagrams node for the database that is
being developed (the one the is developed through the book). I get a
dialog box of "This database does not have one ore more of the support
objects required to use database diagramming. Do you wish to create
them?" So far, so good. I answer Yes as I am supposed to, and then
there is supposed to be an Add Table dialog box and on it should go.

Instead, there is nothing. The Database Diagrams node is
expanded as empty (so that the square with the plus sign in it is gone
and there are no child items), but there is no dialog box, and no
diagram.

When I installed SQL Server 2008 Express, I specified a *full*
installation and it did succeed.

How do I get database diagramming to work?

Sincerely,

Gene Wirchenko

On Fri, 26 Nov 2010 14:09:01 -0800, Gene Wirchenko <genew (AT) ocis (DOT) net>
wrote:

Hi Gene,

To create a new database diagram, you first need to right-click the
Database Diagrams node and click New Database Diagram. At that point,
the Add Table dialog should appear.

If it doesn't (or if you ever closse it and need it back later), you
can right-click any empty space in the diagram pane and choose Add
Table to summon the Add Table dialog.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

On Fri, 26 Nov 2010 23:18:23 +0100, Hugo Kornelis
<hugo (AT) perFact (DOT) REMOVETHIS.info.INVALID> wrote:

[snip]

Thank you. (It did not occur to me to right-click on an empty
node.)

The book does not say that. It is nicely laid out, but there are
quite a few minor errors. Obviously, one of them just bit me.

I suppose some might be due to slightly different versions. I
think that there are at least two downloads of SQL Server 2008
Express. Could anyone elaborate on this?

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
For SQL Express, there are usually streamlined downloads for service packs.
That is, you can download and install SQL 2008 SP2 in one go. With other
editions you need to apply the service pack separately.

It is possible that there are differences in how database diagrams work
between service packs. Then again, it is not that likely. The diagrams is
a fairly old feature, and I don't think Microsoft touches it very much
at all.

I would also like to raise a word of caution of using the graphical
tools in SSMS to create and moreover to change table definitions. There
are serious flaws in how SSMS implement the table changes, of which some
are not so directly apparent, which makes it even more devious. The
diagrams as such may helpful to visualise the database, if you don't
have a real data-modelling tool around (and they tend to be expensive,
why many haven't.) But for actually creating tables you are better off
with CREATE and ALTER TABLE.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Fri, 26 Nov 2010 14:32:35 -0800, Gene Wirchenko <genew (AT) ocis (DOT) net>
wrote:

Hi Gene,

I'm not really into Express. I do know that the download is available
in a version with minimal tools, and a version with more advanced
tools. Maybe those are the two versions you're refering?

I don't expect there to be this kind of differences between these two
versions - functionality is either supported or not, but not
implemented in a different way for the different versions. I guess the
authors and editors of your book simply dropped the ball.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

On Sat, 27 Nov 2010 11:21:35 +0100, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

You may recall the experience I had getting SQL Server Express
running on my system. I tried to install the SP, and it failed. I
decided that I was not playing that game again.

I was thinking of SSE as a whole, not any particular feature.

Thank you for that bit. Do you have any examples of these
errors? A URL would be fine.

The book I am studying does both.

Sincerely,

Gene Wirchenko

On Sun, 28 Nov 2010 01:06:25 +0100, Hugo Kornelis
<hugo (AT) perFact (DOT) REMOVETHIS.info.INVALID> wrote:

In general, I like how the book is laid out, but I am trying
everything. I am the world's meanest editor/proofreader. Combine
these and the result is a lot of sticky notes in my book.

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
Meanest? You haven't seen Hugo in action!


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
There are maybe Connect items about them, but I'm not sure - I filed these
bugs in a system preceding Connect originally.

Anyway, here is a list:

1) They use ALTER TABLE only in situations when it would be accepted
on SQL 6.5. In many cases, ALTER TABLE could do, they instead create
the table under a new name, move data over, move referencing foreign
keys, recreates indexes etc, drops the old table and then rename the
new table to the right name.

2) In the scheme in 1) the transaction scope is wrong. There are three
transactions for something that should be a single transaction - at
least when everything happens behind your back.

3) The script consists of a number of batches. If you opt to generate
the script and run it manually, and some statement fails, this may
abort the transaction. The rest of the script will still be executed,
but now without the transaction. (I am told that if you run the script
from within SSMS, execution will be aborted.)

4) When constraints are moved or readded, they are added with NOCHECK.
This means that SQL Server does not verify the correctness of the
existing data. This goes faster, but it also means that the optimizer
will not trust the constraints, which can have performance implications.

5) Say that you have a Parent and a Child table. You first open the
Child table, and add a column. You generate a script. But then you
decide that the change is wrong, and you close the table without
saving. Then you open Parent and change that table and generate a
script. When you review it, you find that the abandoned change in
Child is there!

Overall, my impression is that the people who wrote this tool in the
dim and distant past, had very little understanding of what it makes
schema changes. The result is a tool which is incorrectly designed from
bottom up. (The reason I talk about the dim and distant past is
that the same bugs are in the SQL 2000 tools as well. They were very
faithfully ported to SQL 2005.)



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Mon, 29 Nov 2010 23:01:26 +0100, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

[snip]

Ouch! Forewarned and all that. Thank you.

Sincerely,

Gene Wirchenko