Skip to main content

Understanding the Mapping of "M" to SQL Server

Transcript of Understanding the Mapping of "M" to SQL Server video

November 2009

Hi. My name is Jason Roth, and I'm a Senior Programming Writer for the SQL Server Modeling CTP. The Microsoft code name “M” language allows you to design models. When you then load those models into a SQL Server database, it's helpful to understand the relationship between the “M” model definition and the SQL Server representation of that model. We're going to look at a model in the “Intellipad” tool to highlight some of the mappings between “M” and SQL Server.

Go to the Start menu.

Select All Programs.

Microsoft SQL Server Modeling CTP.

And then click Intellipad.

You can use “Intellipad” to write and edit “M” models. I'm going to paste a simple “M” model into this blank document.

module HumanResources
{  
    Addresses :
    {(

{       
Id : Integer64;
        State : Text;
        Zip : Integer32;

}
    )}*;
}

 

This “M” code defines a module named HumanResources . This module describes employee data. At the moment, we've only defined a single extent named Addresses . For simplicity, it contains three fields: Id , State , and Zip .

We can use “M” tools to generate a representation of this model in SQL Server. “Intellipad” contains a useful preview feature that shows the T-SQL statements required to translate an “M” model into a SQL Server database.

Before we can use the T-SQL Preview feature, we must first save this code to a file with a ".m" extension. This tells “Intellipad” that we're working with “M” code, and “Intellipad” will respond by providing “M”-specific features.

Note that we now have a new M Mode menu that we can use to access the T-SQL Preview option.

The left pane now displays our first T-SQL translation. Let's take a look.

Note: This is the relevant portion of the T-SQL Preview output:

if not exists
(
 select *
 from [sys].[schemas]
 where [name] = N'HumanResources'

)
 execute [sp_executesql] N'create schema [HumanResources];
go
create table [HumanResources].[Addresses]
 (

 [Id] bigint not null,
 [State] nvarchar(max) not null,
 [Zip] int not null
 );
go

 

The HumanResources module in “M” corresponds to a HumanResources SQL Server Schema.

The Addresses extent in “M” corresponds to an Addresses table.

The fields, Id , State , and Zip , become columns in the Addresses table.

Let's make some change to our “M” model and then look at the effect on the generated T-SQL statements.

For example, if we want to reference this extent from other extents, we need to specify an identity field.

   )*} where identity Id;

 

Now the Id field is the identity for the Addresses extent.

Note: This is the relevant portion of the T-SQL Preview output:

create table [HumanResources].[Addresses]
 (

 [Id] bigint not null,
 [State] nvarchar(max) not null,
 [Zip] int not null,
 constraint [PK_Addresses] primary key clustered ([Id])
 );
go

 

After adding this identity clause, you can see a new constraint in the Addresses table definition. This constraint specifies that Id is the primary key for the Addresses table.

If we want to have this Id field automatically increment, we can use the AutoNumber keyword following the Id declaration.

       Id : Integer64 => AutoNumber;

 

Note: This is the relevant portion of the T-SQL Preview output:

create table [HumanResources].[Addresses]
 (

 [Id] bigint not null identity,
 [State] nvarchar(max) not null,
 [Zip] int not null,
 constraint [PK_Addresses] primary key clustered ([Id])
 );
go

 

In the SQL preview window, an identity property has been added to the Id column of the Addresses table. In SQL Server, identity columns automatically increment for each inserted row.

Let's now add an Employees extent.

   Employees :
   {(

{
       Id : Integer64 => AutoNumber;
      EmployeeId : Integer32;
       Name : Text;
       EmployeeAddress : Addresses;

}
   )*} where identity Id;

 

The Employees extent has members such as EmployeeId and Name. It also has an EmployeeAddress field that references the Addresses extent.

Let's look at the T-SQL translation for this relationship.

create table [HumanResources].[Employees]
 (

 [Id] bigint not null identity,
 [EmployeeAddress] bigint not null,
 [EmployeeId] int not null,
 [Name] nvarchar(max) not null,
 constraint [PK_Employees] primary key clustered ([Id]),
 constraint [FK_Employees_EmployeeAddress_HumanResources_Addresses] foreign key ([EmployeeAddress]) references [HumanResources].[Addresses] ([Id])
 );
go

 

In the SQL Preview window, we see a new Employees table related to the Employees extent. Again, you can match the columns in the table to the fields in the extent.

But we also see a new foreign key constraint. This constraint specifies that the EmployeeAddress column in the Employees table references the Id column of the Addresses table.

So far we've just been looking at how extents relate to SQL Server tables. However, “M” also has a type construct. This is mainly for replicating a data pattern across multiple extents.

Let's take a simple example. What if the HumanResources model has several extents that contain a Name field. We could factor out the Name field into its own type. Let's do this now by defining a HasName type.

type HasName
{
    Name : Text;
}

 

Note that types do not have any representation in the T-SQL output. They are only useful when mixed into other extents (as a result, they are sometimes called "mixins").

Note: Adding the type did not result in any change to the T-SQL Preview output, because types do not have any representation in SQL Server. They only specify fields that can be added to other extents as shown below.

We're now going to change the Employees extent to use our HasName mixin.

    Employees :
    {(

HasName &
    {
        Id : Integer64 => AutoNumber;

       EmployeeId : Integer32;
        EmployeeAddress : Addresses;

}
    )*} where identity Id;

 

First, remove the Name field.

Finally, add the HasName type with an ampersand, indicating that the extent consists of the HasName mixin and the specified fields.

The T-SQL Preview in this case is the same as before when the Name field was a member of the Employees extent rather than a part of the HasName mixin. In both cases, a Name column is created in the Employees table. However, the HasName mixin can be used in other extents as well to provide a consistent pattern for what extents that have names.

I hope this video helped to explain the translation from “M” to SQL Server. I also hope that you see the value of using “Intellipad” to learn about the T-SQL mapping. For more information about “M” and the “Oslo” repository, see these MSDN Library topics. If you have any feedback about this presentation or any other questions, please visit the link to the SQL Server Modeling CTP forum shown here. Thank you for watching!

 

Complete Code Sample

module HumanResources
{  
    type HasName
    {
        Name : Text;
    }

    Addresses :
    {(

{
        Id : Integer64 => AutoNumber;
        State : Text;
        Zip : Integer32;

}
    )*} where identity Id;

    Employees :
    {(

HasName &
    {
        Id : Integer64 => AutoNumber;

       EmployeeId : Integer32;
        EmployeeAddress : Addresses;

}
    )*) where identity Id;    
}

Microsoft is conducting an online survey to understand your opinion of the MSDN Web site. If you choose to participate, the online survey will be presented to you when you leave the MSDN Web site.

Would you like to participate?