When we create views, we are effectively storing a query definition that is executed by SQL Server, even though we treat them as read-only tables in AX. If we write a view that uses another view as its data source, SQL Server will determine the best execution plan based on the desired output columns and the queries defined by the views.

This is important to understand, and it will help us create more efficient views. For example, there is an almost natural belief that writing a view that filters data to be used by another view is more efficient, as the data source has fewer rows. This is incorrect because the base view's data doesn't physically exist; it is merely a query definition.

Another example is how aggregated columns in a base view are treated by views based on it. Let's say our base view has an aggregated column, and we write another view that uses this base view but does not use the base view's aggregated column. The aggregation will not be executed by SQL Server.

For example, suppose we created the following views using SSMS:

CREATE VIEW [baseView] AS
SELECT SalesId, Sum(QtyOrdered) totalOrdered
    FROM SalesLine 
    GROUP BY SalesId
GO
CREATE view [otherView] AS
SELECT SalesId from baseView
GO

Enable Include actual execution plan in the Query menu, and write the following code:

SELECT SalesId FROM otherView

All of this produces the following execution plan:

It is clear that the query executed by SQL Server is directly querying SalesLine.

One of the limitations of SQL views defined in AX is that you cannot use CASE statements or subqueries as columns. To solve this problem, Microsoft now allows methods to be created that return a SQL statement that can be used as a column in the view. These are called computed columns.

View methods are static server methods that should return a string of valid Transact-SQL (actual SQL code).

This could be something like the following:

public server static str thisIsOne()
{

    return "SELECT 1";
}

When added to the view as an integer view method called One, it adds a column with the following code:

CAST((SELECT 1) as INTEGER) as One

public server static str vehicleIdAndName()
{
    str viewName = tableStr(ConFMSVehicleUtilisation);
    str sql;
    // this is the data source's name, 
    // which may not be the same as the table name
    str datasourceName = identifierStr(ConFMSVehicleTable);
    str vehicleIdField = fieldStr(ConFMSVehicleTable, VehicleId);
    str nameField      = fieldStr(ConFMSVehicleTable, Name);
    // to contain the actual SQL column name
    str vehicleId; 
    str name;
    // the following will return a string similar to T1.VehicleId
    vehicleId = SysComputedColumn::returnField(viewName, 
                                               datasourceName,
                                               vehicleIdField);
    name      = SysComputedColumn::returnField(viewName, 
                                               datasourceName, 
                                               nameField);
    sql = vehicleId + "+ '-' + " + name;
    return sql;
}

SELECT T1.SalesId from SalesTable T1

sql = vehicleId + "+ '-' + MIN(" + name + ")";

T1.VehicleId +'-'+MIN(T1.Name)

SELECT T1.VEHICLEID AS VEHICLEID,
--/
(CAST((T1.VEHICLEID+ '-' + min(T1.NAME)) AS NVARCHAR(60)))
AS VEHICLEIDANDNAME
--/

The code is similar to the preceding code, but we have a big consideration here when joining a table as a subquery. AX will naturally join the two system fields, Partition and DataAreaId, and they are also added to the indexes. Simply, we must add these fields to our joins (depending on the table's SaveDataPerPartition and SaveDataPerCompany properties) when writing a subquery as a view method.

The following method will sum records from ConFMSVehServiceTable for each vehicle:

public server static str vehicleServiceCount()
{
    str viewName = tableStr(ConFMSVehicleUtilisation);
    str sql;
    // this is the data source's name,
    // which may not be the same as the table name
    str datasourceName = identifierStr(ConFMSVehicleTable);
    str vehicleIdField = fieldStr(ConFMSVehicleTable, VehicleId);
    str dataAreaFIeld  = fieldStr(ConFMSVehicleTable, DataAreaId);
    str partitionField = fieldStr(ConFMSVehicleTable, Partition);
    // to contain the actual SQL column name
    str vehicleId;
    str dataAreaId;
    str partition;
    // the following will return a string similar to T1.VehicleId
	dataAreaId = SysComputedColumn::returnField(viewName,
                                               datasourceName,
                                               dataAreaFIeld);
    partition = SysComputedColumn::returnField(viewName,
                                               datasourceName,
                                               partitionField);
    vehicleId = SysComputedColumn::returnField(viewName,
                                               datasourceName,
                                               vehicleIdField);

    sql  = "SELECT COUNT(*) FROM ConFMSVehServiceTable LVS 
";
    sql += "    WHERE LVS.VehicleId  = " + vehicleId  + "
";
    sql += "      AND LVS.DataAreaId = " + dataAreaId + "
";
    sql += "      AND LVS.Partition  = " + partition + "
";

    return sql;
}

Next, add this to the view's field list by following these steps:

Although we can write Transact-SQL directly, there are some useful functions that are provided to us. All of these are stored in the SysComputedColumn class.

The common functions we tend to use are as follows:

To return a fully-qualified SQL field name use the command SysComputedColumn ::returnField.

To create a Transact-SQL CASE statement use SysComputedColumn::switch. This has two parameters:

You can use the SysComputedColumn::if command to create a simple CASE statement with on CASE and one ELSE.

The reason these methods exist is twofold: firstly, they are a helper to those not skilled in Transact-SQL, and secondly, the code returned will be valid for supported versions of SQL Server. This doesn't mean you can't use Transact-SQL, and in some cases, it will be easier to read and possibly perform faster. If you do write Transact-SQL, you are responsible for ensuring that the code will work on the current and future versions of SQL Server.

The most common mistake we have seen is using X++ functions when building the SQL statement, like this for example:

    sql += "      AND LVS.DataAreaId = " + curExt() + "
";

This will hardcode the company that was active when the view was synchronized into the statement; it will return a value only if the user is in that company.

Others are attempts to use X++ table methods when creating the statement. Again, the result of the method was determined when the view was synchronized, hardcoding the static result of the statement.

Finally, the data returned will bypass any security you have in place within AX. We therefore need to be mindful of this when writing the computed column. For example, if a user doesn't have access to CustTabe.CreditMax and we write a computed column that gets this value of AX on a view the user does have access to, they will see this data.