Has anyone used BIML to create Memory optimized tables.

  • Phillip.Putzback

    Ten Centuries

    Points: 1251

    I was hoping to experiment with memory optimized tables for my staging layer to speed up the process. Googling "BIML Create Memory Optimized Table MEMORY_OPTIMIZED=ON" is not giving me nay results. This seems like it would be a common setting for a table used in a staging layer.

    I am using the table.GetDropAndCreateDdl() code to create my scripts. I thought I could put the "MEMORY_OPTIMIZED=ON" after it but the function ends up creating a GO at the end.

    The XML result

    , [LoadDateTime] datetime2(7) NOT NULL

    -- Constraints

    )

    ON "default"

    WITH (DATA_COMPRESSION = NONE)

    GO

    -------------------------------------------------------------------

    WITH (MEMORY_OPTIMIZED=ON)</DirectInput>

    Thoughts?

  • Site Owners

    SSC Guru

    Points: 80376

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Jonathan AC Roberts

    SSCoach

    Points: 17298

    You cannot have DATA_COMPRESSION on memory optimised table, so there is no point in specifying it.

    So remove that option and just leave: WITH (MEMORY_OPTIMIZED=ON)

  • Phillip.Putzback

    Ten Centuries

    Points: 1251

    I didn't add the compression statement, that is generated by the BIML code.

  • Jonathan AC Roberts

    SSCoach

    Points: 17298

    Phillip.Putzback wrote:

    I didn't add the compression statement, that is generated by the BIML code.

    Ok, sorry, I've never used BIML. Also I've only used memory optimised table once, and I found them slower than temporary tables so I gave up.

    I would have thought there should be just one with WITH statement:

    WITH (DATA_COMPRESSION = NONE, MEMORY_OPTIMIZED=ON)

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply