Creating Views with Computed Values
[This is prerelease documentation and is subject to change in future releases. Blank topics are included as placeholders.]
Computed values can be used to create complex views. The expression contained inside the braces can be any legal Microsoft code name “M” expression. This includes query expressions, which are very similar to C# LINQ expressions. Therefore you can include almost any SQL expression in a computed value.
Examples
The examples start with a trivial case, and proceed up to a constrained restriction case.
Simple View
The following code is a basic computed value that generates a view.
module m
{
C() { { FirstName = "John", LastName = "Smith" } }
}
It generates the following T-SQL code.
create view [m].[C]
(
[FirstName],
[LastName]
)
as
select N'John' as [FirstName], N'Smith' as [LastName];
This particular example is trivial, but you can insert any valid expression within the braces. For example, business applications often require complex date functions, like the date of the last day of the following month.
Restricted View on a Table (Columns)
It is common to restrict database users from direct access to tables and only permit access through views.
The following code prevents the user from seeing the Salary column in Employees table.
module view_name
{
type Person
{
Id : Integer64 = AutoNumber();
Name:Text;
Salary : Integer32;
} where identity(Id);
People:Person*;
RestrictView () { from p in People select p.Name; }
}
This “M” code generates the following T-SQL code.
create table [view_name].[People]
(
[Id] bigint not null identity,
[Name] nvarchar(max) not null,
[Salary] int not null,
constraint [PK_People] primary key clustered ([Id])
);
go
create view [view_name].[RestrictView]
(
[Item]
)
as
select [p].[Name] as [Item]
from [view_name].[People] as [p];
go
Restricted View on a Table (Constraint)
It is common to restrict database users from direct access to tables and only permit access through views. Users might only be able to see certain columns or rows meeting certain conditions.
The following code generates a view that prevents the user from seeing employees with salary at or above 10,000.
module M
{
type Employee
{
Id : Integer32 = AutoNumber();
name : Text;
salary : Integer32;
} where identity(Id);
Employees : Employee*;
V() { from e in Employees where e.salary < 10000 select e }
}
This “M” code generates the following T-SQL code.
create table [M].[Employees]
(
[Id] int not null identity,
[name] nvarchar(max) not null,
[salary] int not null,
constraint [PK_Employees] primary key clustered ([Id])
);
go
create view [M].[V]
(
[Id],
[name],
[salary]
)
as
select [e].[Id] as [Id], [e].[name] as [name], [e].[salary] as [salary]
from [M].[Employees] as [e]
where [e].[salary] < 10000;
go