I've come here for a little cry. For months I was managing to immerse in real DB stuff - except unpaid - and now I've got real employment again. Azure Tag Policy Definitions. Did the world ever wake up and decided they wanted json packages to define logic? No they didn't, ever.

(the new job is DBA, but just applying a taxonomy the estate is driving me nuts. It makes me realise how good mature documentation is - SQL Server, MySQL - and how bad the docs are for some superb new cloud tech is)

Morning

Mornin

g

Thanks for the running tally

Morning

Morning all

Morning

@ypercubeᵀᴹ you there?

yes

It isn't exactly a transitive closure right?

I mean, I've seen some videos about it this weekend, and a transitive closure requires to be "closed".

a -> b -> c (and then again -> a)

ok. and this isn't?

In this case is only an "un-closed" segment.

Is that right?

what do you mean? looks closed to me

Maybe I cannot explain it properly.

a -> b -> c
is not the same as

a -> b -> c  -> a

no, it isn't

are you asking if a->b->c is a closure vs a->b->c->a ?

Yes

ok. so a->b->c means:

we have a s et of paths:

{ a->b, b->c }

the closure of this set would be

{ a->b, b->c, a->c }

it is closed because no more paths can be deduced from it.

a->c or c->a ?

a->c

Ok, thanks

if you start with { a->b->c->a }, which means{ a->b, b->c, c->a }, it is not closed either.

The closure would be

{ a->b, b->c, c->a, a->c, c->b, b->a, a->a, b->b, c->c }

All possible relations

in this case, yes

If you start with
{a->b, b->a, c->c}
the closure will be
{a->b, b->a, a->a, b->b, c->c}

And the pattern can be applied not only to nodes and paths between 2 nodes, but any (math) relation.

Just for fun @McNets @ypercubeᵀᴹ dbfiddle.uk/…

@PaulWhite nice !

@ypercubeᵀᴹ This is a bit more graph-y but less efficient dbfiddle.uk/…

It's probably trivial in a real graph database idk

I suppose the problem itself isn't that interesting once you have the separate trees within a graph

I have to admit that the graph functions and code is confusing. MS documentation has very little examples so that doesn't make it easier

@ypercubeᵀᴹ No doubt about that

Things like MATCH(SHORTEST_PATH(GN1(-(GE)->GN2)+)); are pretty opaque

Presumably it is clearer than recursive queries once the basics are understood

Shortest path from node1 - edge -> node2 with + meaning recursive

All that replacing 'normal' join syntax between the nodes and edges

@PaulWhite check this dbfiddle.uk/…

@ypercubeᵀᴹ I tried something similar earlier, but connecting things both ways just seems a bit odd.

yes but not to mathematicians

Right, absolutely. And it solves the problem quite neatly. The oddness comes from the SQL Graph design saying it is a (uni?) DAG.

@ypercubeᵀᴹ FYI dbfiddle.uk/…

The execution plan and hints in particular!

is there a way to define an Edge table as being directional or not?

@ypercubeᵀᴹ I'm no expert, but as I understand it so far, no.

Yeah, nothing in the docs.

I notice that you can have FKs between node, edge and normal tables as well. No restrictions as far as I can see.

I'm sorry, I was attending a meeting

@ypercubeᵀᴹ I really like your solution by the way.

I wonder how efficient these solutions are

or in other words at what graph/tree size they would take the server on its knees

Early testing when the graph stuff came out showed at about 3-4 hops a traditional query would cause my server to whimper while the graph equivalent query zoomed along

@PaulWhite it's really confusing to me

Where can I get more info?

@McNets Glad I could help. Always on deck to answer XML-questions :)

@MikaelEriksson thanks again

I would probably go with 1.

@MikaelEriksson ok, thank you

nvarchar will not work with utf-8 either. Needs to be utf-16 or no encoding at all.