Solved Determine Join Type Between Tables In MSSQL Diagram View?

[dalski]

Does the SSMS ER also show many-to-many (infinity symbols on both endpoints)?

Be like Access I'd imagine; primary key at parent, infinity at child both infinites would end at a joining table. I'm sure your sort of calibre knows this & you've just put this as on off-the-fly comment.

Well, you find out VERY interesting things!

Thanks Albert, credit to you a lot of people don't admit when they're wrong. I also don't care how I look, just want to learn as much as I can. I should've figured out the indexing would change the relationship on my own so thanks for notifying & that makes sense; like Access. I didn't put the two together as I'm trying to familiarize myself with MSSQL, thanks again.

 

[Albert D. Kallal]

Be like Access I'd imagine; primary key at parent, infinity at child both infinites would end at a joining table. I'm sure your sort of calibre knows this & you've just put this as on off-the-fly comment.


Thanks Albert, credit to you a lot of people don't admit when they're wrong. I also don't care how I look, just want to learn as much as I can. I should've figured out the indexing would change the relationship on my own so thanks for notifying & that makes sense; like Access. I didn't put the two together as I'm trying to familiarize myself with MSSQL, thanks again.

The problem is that if you do what I done for years?
That is build the relationship using the UI/diagram tool? You THEN set/create the index after?
(which is what most people probably do....)
The diagram does not update - so, you don't notice this effect.

I ONLY noticed this, because I was taking a screen shot for my post here - I then noticed the 2 yellow keys!
I never noticed this before, since I by habit do things always in the same order.

To make matters worse? I don't remember the last time I enforced a one to one anyway! It's rare!
And even in cases when I did have a one to one?
I probably did not bother to enforce it beyond that of just a standard enforced FK and a enforced cascade delete, and that's rather sufficient in near all cases anyway. In most such cases (a one to one), then it's mostly code that manages this, and not the user's UI in the application anyway.
Unless the column count is "really" getting out of control and too high? Then I'll just add those columns to the base table anyway, and thus the need for "one to one" is rare anyway.

So, while I didn't notice this for years? Well, it's a jolly good thing anyway.....

R
Albert

 

[Isaac]

Thanks Tom that is helpful, but wow what a headache. On such an intrinsic, core area of normalization there must be a simple quick way built into MSSQL .

So we can see the relationships between keys in the Object Browser & it's Properties Window when clicking the relationship in Diagram View, and also by clicking the ellipsis in Properties > Tables & Columns Spec; stumped the relationship type is not in there; let alone in Properties Or Object Browser. But surely somewhere possible to see the actual relationship type. Dare I say it, Access is superior in it's simplicity here. Don't get me wrong even my feeble mind sees the benefit of MSSQL, but if it is not possible to see relationship types in a simplistic manner (especially in the design view) then am I the only one who sees this as a huge disappointment?

View attachment 122357 View attachment 122359

Simply installing and using a product like Redgate with SQL Prompt will pretty much erase the whole need for stuff like this, as you'll see in not only Intellisense but their helpful popups all kinds of relationship and key type information. This is where stuff like redgate shines, and while I see the problem you're complaining about, yet, in the real world most people are using something like Redgate as an SSMS add-on and aren't really suffering from the problem

 

[Isaac]

My position is that experienced developers who understand how MVFs are designed and how to implement them are more likely to avoid them in favor of more robust approaches.

Novices who don't understand normalization, much less the inner workings of MVFs, are more likely to use them and do so inappropriately.

Like a lot of tools, in the wrong hands, they do more harm than good. In the right hands, they can be useful. Unfortunately, they appeal more to those less likely to use them safely.

Because people are drawn to a feature that "DOES something". ("Oh, wow, this DOES such-and-such, cool") - the instant assumption is that if it does something, if it has the capability, then I ought to immediately use it. Without understanding the ramifications of the doing.
It's an instant assumption that capability = should-use

 

[ButtonMoon]

Bear in mind that joins and "relationships" are utterly different things. Access's diagram feature muddles up those concepts whereas SQL Server Management Studio does not. It's also the case that both SSMS and Access are not capable of drawing real ER Diagrams. Instead they use a pictorial notation that is peculiar to Microsoft and lacks most of the things that people expect to see in a ER Diagram. Most SQL Server developers and administrators don't use the diagram tool in SSMS.

 

[Albert D. Kallal]

Bear in mind that joins and "relationships" are utterly different things. Access's diagram feature muddles up those concepts whereas SQL Server Management Studio does not. It's also the case that both SSMS and Access are not capable of drawing real ER Diagrams. Instead they use a pictorial notation that is peculiar to Microsoft and lacks most of the things that people expect to see in a ER Diagram. Most SQL Server developers and administrators don't use the diagram tool in SSMS.

To an extent that one needs a good ER diagram for documentation, sure, Access (or SSMS) not all that great for such a diagram tool.

But, it's better then nothing! And I remember when Access came out - having a diagram tool was REALLY amazing, and previous systems that had such a feature were rather expensive tools.

However, just like Access has both a Graphical interface for the ER diagram (to setup relationships), and then one for building queries?

SSMS also have both diagrams also, and works much the same, and thus has no more (or less) distinction then Access does.

And the default for editing views in SSMS is much the same as editing a saved query in Access.
Again, you get a designer with GUI diagram in both products.

So, I can't really say that Access or SSMS works much different. (in context of mixing up of the setting up of relationships, or that of building a query).

So, not sure of the distinction here?

If we are to assume that the equivariant of editing a saved query in Access is the same as editing a view in SQL server?

Then, really, both work near identical - and both bring up and show a graphical query designer by default.

Only minor difference is that SSMS does not bring up the designer by default when typing in a query, but that's not the same as editing a saved query - and in both products, a default editor is displayed.

And even when not editing a view?
I'll still often bring up the query builder - and SSMS allows you to use the designer for SQL, but without having a saved query.....

I tend to like the GUI for the "spatial" view of relation between the join(s) I have in such a query....

So, often, even when typing in a query in SSMS, I will bring up the SQL query designer - probably a habit from having used Access so much...

I just highlight the SQL and then right click, and choose "design in query editor".

So, I can't say Access, or SSMS mixes up the two features (relationships, and building of SQL) any more, or less between the two products....

Only real difference is the SSMS relatonships eidtor don't correctly draw join lines from the table column name, and you have to manually adjust those lines to "line up" with the given column name.

However, the SSMS query designer does draw the join lines correctly from column names just like Access does between columns in query editor....

Eg this:

R
Albert