Execute SQLStatement in AX 2012

Below is code example to demonstrate that, SQL queries can be directly executed from AX 2012 to improve performance in some cases.

str sqlStatement = strFmt(@"

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME = 'ModelElement')

    BEGIN

        CREATE TABLE [dbo].[ModelElement](

            [ElementType] [int] NOT NULL DEFAULT (0),

            [RootHandle] [int] NOT NULL DEFAULT (0),

            [ParentHandle] [int] NOT NULL DEFAULT (0),

            [ElementHandle] [int] IDENTITY(1,1) NOT NULL,

            [Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (''),

            [AxId] [int] NOT NULL  DEFAULT (0),

            [ParentId] [int] NULL  DEFAULT (0),

            [Origin] [uniqueidentifier] NULL,

            [PartOfInheritance] [INT] NOT NULL DEFAULT (0),

            CONSTRAINT [PK_ModelElement_ElementHandle] PRIMARY KEY CLUSTERED

            (

                [ElementHandle] ASC

            ),

            CONSTRAINT [I_ModelElement_ElementId] UNIQUE NONCLUSTERED

            (

                [ElementType] ASC,

                [AxId] ASC,

                [ParentHandle] ASC,

                [Name] ASC

            ),

            CONSTRAINT [I_ModelElement_ElementName] UNIQUE NONCLUSTERED

            (

                [ElementType] ASC,

                [Name] ASC,

                [ParentHandle] ASC

            )

        )

        CREATE UNIQUE INDEX I_ModelElement_NameOrderList ON [dbo].ModelElement( ParentHandle, ElementType, Name )

        INCLUDE ( AxId )

        --    CREATE INDEX I_ModelElement_HandleOrderList ON ModelElement( ParentHandle, ElementType, AxId )

        CREATE UNIQUE INDEX I_ModelElement_ParentHandle ON [dbo].ModelElement(ParentHandle, ElementHandle)

        CREATE NONCLUSTERED INDEX [I_ModelElementRecursions] ON [dbo].[ModelElement]

        (

            [ParentHandle] ASC,

            [ElementHandle] ASC,

            [ElementType] ASC

        )

        INCLUDE

        (

             [AxId],

             [Name]

        )

        CREATE NONCLUSTERED INDEX [I_ReadHeaderExt] ON [dbo].[ModelElement]

        (

            [ElementType] ASC,

            [ParentHandle] ASC,

            [AxId] ASC

        )

        INCLUDE (

            [ElementHandle],

            [Name],

            [Origin]

        )

        CREATE NONCLUSTERED INDEX [I_RootHandle] ON [dbo].[ModelElement]

        (

            [RootHandle]

        )

        CREATE UNIQUE INDEX [I_ModelElement_Id1Id2] ON [dbo].[ModelElement] (ElementType, ParentId, AxId)

        WHERE (ParentId <> 0 AND AxId <> 0)

    END

    ELSE

    BEGIN

        DELETE FROM ModelElement;

    END");

    Connection  sessionConn = new Connection();

    Statement   statement = sessionConn.createStatement();

    new SqlStatementExecutePermission(sqlStatement).assert();

    statement.executeUpdate(sqlStatement);

    CodeAccessPermission::revertAssert();