Tired of hearing “M” is to “T-SQL” what X is to Y…

I have heard a couple of variations of these analogies. I do not like them. I think they are simply absurd.

At last PDC, “M” was to “SQL” what C is to Assembler. This year it was, what VB is to C. And now I even read this:

The code name “M” language is like a more manageable (though more limited) form of Transact-SQL, the language normally used to describe data schema and values.

Kraig Blockschmidt, Microsoft Data Development Technologies: Past, Present, and Future 

“M” has some overlaps with T-SQL, ok. But far from every concept in “M” can be translated into T-SQL. What about structural subtyping? Types without identities? Polymorphic references and function arguments? Languages/DSLs? Ordered collections? Lot’s more.

And only a very small, although useful subset of T-SQL maps to “M”. Also most of the translation to SQL is opinionated, not natural.

What the schema and values part of M compares much more to, is XML and XSD.

Would you even try to compare XML to T-SQL?

12 thoughts on “Tired of hearing “M” is to “T-SQL” what X is to Y…

  1. Almost everything you list is translated to M.

    M does not have native ordered collection, but it if it did, rest assured that it will translate to TSQL.

    One of the primary goals of M is to allow the storage of a given model and model instances into a database.

    You may not like that goal, but it is a P0 goal for the language.

    You absolutely can write down things that don’t go into a database or that will never go into a database, but don’t be confused about one of the primary use cases.

    • That said, describing documents is absolutely one of the things that we are looking at doing — and this is the primary use case for XML/XSD.

      Net: We have found that both writing things down for the database and writing things down for documents (regardless of the storage tech) is harder than it needs to be.

      “M” is a language for data — data that can span the structured and semi-structured world — but we absolutely want our model to be easily stored in a _natural_ relational database.

      • Document “markup” is important, too. M-syntax for that is horrible. Why not just stick with XML for that case? It’s great for document markup.

        Xml is horrible, though, when it has only markup, but no data.

        I’d like Xml in “M”.

          module XmlInM {
            type Person : {
                Name : Text;
                Description : Xml;
            People : {Person*} {
                { Name = "Douglas Purdy", <p>
                    All his job is about, is making developers 
                    <b>love<b> <big>SQL</big>.</p> }
    • Just to give you a few examples:

      module Unsupported {
          type Simple : {
              Property : Integer;
              Property2 : { Integer* }; // unordered col
              Property3 : [ Integer* ];  // ordered col
              Property4 : {
                  Sub : Text;
              Property5 : {{
                  Sub : Text;
          // doesn't work. Every property above is perfect M, but 
          // does not compile to SQL
          // Table : {Simple*};
          // that should be ordered, but it isn't
          Integers : [Integer32*];
          // since NoIdentity has no identity
          // this would just be a contained
          // complex type in M.
          type ReferenceToNoIdentity {
              NoIdentity : NoIdentity;
          type NoIdentity {
              Property : Integer32;
          Doesn't compile, but neigher throws an error
          ReferenceToNoIdentityTable : {(ReferenceToNoIdentity
              where value.NoIdentity in NoIdentityTable)*}
          NoIdentityTable : {NoIdentity*};

      I’m not saying that these concepts should compile to T-SQL, all what I’m claiming is, that “M” is much more a object graph than a relational structure. And the impedence missmatch is huge.

      The day you have compiled all M to SQL, you’ll have an object database. Good! 🙂

  2. Ok, I’ll bite. 🙂 (how unusual of me).

    Putting aside whatever financial and business decisions there may be behind the name shift and scope change, the new project/[product?] name for Oslo puts things in a new perspective. Until now it was targeted as a semi-‘fluffy-scoped’ general modelling thing. But labelled as a SQL modelling tool, [at least by name,] I would expect a bit more alignment with the relational model. Right now only a tiny fraction of what can be expressed in TSQL-DDL can be expressed in M, and when forward/reverse engineering M TSQL it frequently breaks. Yes, it is CTP code so that can be expected, but I really hope they will align the product with the name a bit in upcoming CTPs/betas/etc…

    …just my 2 baht / cents / pfennig. 🙂

    • Well, I don’t. I hope that it stays that semi-fluffy-scoped general modelling thing. I think it’s useful. And that SQL-thing is ok, but hopefully won’t show up in the final name for the language “M”.

      I don’t think that “M” should cover SQL, so that forward/reverse is always possible. I don’t think it should be more relational either. It should rather support higher level modelling concepts like containments, etc.

      • Ok, but in that case the “SQL modelling” name is as wrong and confusing as it can be. So yes, time to rename it [again] if that is the case…

        If even the most simple/basic SQL modelling scenarios are not covered (see the simple four table sample I posted at http://bit.ly/4wLuqq , and/or try reverse+forward engineering northwind and adventureworks) then I think the name should really be something else. If the final product will be called “SQL Server modelling” or similar, and if such basic and common scenarios are not covered then there will be a lot of confusion and frustration when people actually try to use it for modelling relational db schemas…

        (again, there is of course the possibility that the “sql” part has more to do with MSFT internal politics than where they’re taking the product… …I guess we’ll find out some day… 🙂 )

  3. it looks like we did get ordered collections in this CTP.

    i thought we were waiting on the next spec rev to get that in, so this is great and it tells you something about how much time i have had to track the impl.

    but as i said, if we were to do it (which we did), we would map it to T-SQL, which is exactly what we do (pinky and I were just talking about it and we were both a little surprise with how far this made it for this release).

    module foo {
        type bar {
            Id:Integer32 => AutoNumber();
            values: [Integer32*];
        } where identity Id;
        baz: { bar* };
        baz { instanceA {values => [1,2,3]}, instanceB {values => [33,45,55]}, instanceA {values => [4,5,6]}}
    create table [foo].[baz]
        [Id] int not null identity,
        constraint [PK_baz] primary key clustered ([Id])
    create table [foo].[baz.values]
        [Id] bigint not null identity,
        [Parent] int not null,
        [Value] int not null,
        constraint [PK_baz.values] primary key clustered ([Id]),
        constraint [FK_baz.values_Parent_foo_baz] foreign key ([Parent]) references [foo].[baz] ([Id]) on delete cascade
    insert into [foo].[baz.values] ([Id], [Parent], [Value])
        values (@seed_foo_baz_values + @increment_foo_baz_values * 1, @seed_foo_baz + @increment_foo_baz * 1, 1),
            (@seed_foo_baz_values + @increment_foo_baz_values * 2, @seed_foo_baz + @increment_foo_baz * 1, 2),
            (@seed_foo_baz_values + @increment_foo_baz_values * 3, @seed_foo_baz + @increment_foo_baz * 1, 3),
            (@seed_foo_baz_values + @increment_foo_baz_values * 4, @seed_foo_baz + @increment_foo_baz * 1, 4),
            (@seed_foo_baz_values + @increment_foo_baz_values * 5, @seed_foo_baz + @increment_foo_baz * 1, 5),
            (@seed_foo_baz_values + @increment_foo_baz_values * 6, @seed_foo_baz + @increment_foo_baz * 1, 6);
    nsert into [foo].[baz.values] ([Id], [Parent], [Value])
        values (@seed_foo_baz_values + @increment_foo_baz_values * 7, @seed_foo_baz + @increment_foo_baz * 2, 33),
            (@seed_foo_baz_values + @increment_foo_baz_values * 8, @seed_foo_baz + @increment_foo_baz * 2, 45),
            (@seed_foo_baz_values + @increment_foo_baz_values * 9, @seed_foo_baz + @increment_foo_baz * 2, 55);
  4. I was talking to Haroon and Gudge about this.

    It turns out that we got ordered collection support in TSQL gen by accident, as SequenceTypeSymbol extends from CollectionTypeSymbol in the semantic graph.

    I am checking to see how we would change the TSQL generation if we detected the SequenceType in the backend.

  5. Haven’t seen that SQL mapping before. But is this natural? Arrays are not natural to sql, so the mapping has to be opinionated. Also it can hardly support reverse-engeneering without hard-coding the “opinions” as conventions.

  6. All compilers have opinions about what they emit.

    Our opinion is to have as natural a database as humanly possible.

    That is the design goal; can you open Excel and do a query over the resulting tables and values we generate.

    This generation makes complete sense to me and it look good in a database tool.

    As for ordered collections — think we add ordered collections to the schema fragment this CTP, but you should write down ordered values previously.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s