Computed Values (M to SQL Mapping)

  • Article

[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]

Computed Values in Microsoft code name “M” represent functions, expressions, or queries that can be reused.

“M”->SQL translates Computed Values into scalar functions, table-valued functions, or views, depending on their most probable usage in SQL (based on the return type and parameters to the function):

  • Scalar functions are created when the Computed Value return type is scalar, which allows you to use them like SELECT Module.Function(2) + 1.

  • Views are created when the Computed Value has no parameters and the return type is an entity or a collection, which allows you to use them like SELECT * FROM Module.View.

  • Table-valued functions are created when the Computed Value has parameters and the return type is an entity or a collection, which allows you to use them like SELECT * FROM Module.Function(10).

Computed columns are not supported.

Examples

SQL tables for entities have columns of the same name as the entity fields as shown in the following table.

“M” Example SQL Example

View

 
module M {
    type Var { i : Integer32; }
    Vars : Var*;
    BigVars() : Var* { Vars where value.i > 100 }
}
 
create view [M].[BigVars]
(
  [i]
)
as
  select [$value].[i] as [i]
  from [M].[Vars] as [$value]
  where [$value].[i] > 100;

Scalar Function

 
module M {
    MyInt : Integer32;
    DoubleMyInt() : Integer32 { MyInt * 2 }
}
 
create function [M].[DoubleMyInt]
(
)
returns int  as
  begin
    return (select [Item] as [Item]
from [M].[MyInt]) * 2
  end

Scalar Function

 
module M {
    Square(x : Integer32) : Integer32 { x * x }
}
 
create function [M].[Square](@x as int)returns int  as
  begin
    return @x * @x
  end

Table Valued Function

 
module M {
    type Var { i : Integer32; }
    Vars : Var*;
    BigVars(minValue : Integer32) : Var*  { Vars where value.i > minValue }
}
 
create function [M].[BigVars](@minValue as int)returns table
  as return (
    select [$value].[i] as [i]
    from [M].[Vars] as [$value]
    where [$value].[i] > @minValue
  )

“M”->SQL does not support entity and collection parameters to Computed Values. Only scalars are supported in parameters.