Post reply

Evaluating use of InMemory (session) tables... How do I delete orphaned rows?

  • August 8, 2017 at 11:01 am

    #339218

    If I create an In Memory table as a replacement for temp table (aka Session in memory table?).

    How can I delete potential orphan rows?

    Something defined like this
    CREATE TABLE blahblahblah(
        LeafLevelDatasetId    INT NOT NULL IDENTITY(1, 1),
        DateKey       INT NOT NULL,
        SafeRegionKey     SMALLINT NOT NULL,
        SafeCountryKey     SMALLINT NOT NULL,
        etc...
      ,SpidFilter SMALLINT  NOT NULL DEFAULT (@@spid)
      ,CONSTRAINT CHK_UILeafLevelDatasetSessionTable_SpidFilter CHECK ( SpidFilter = @@spid )
      ,INDEX UNCIX_UILeafLevelDatasetSessionTable NONCLUSTERED (
      SpidFilter, DateKey, SafeRegionKey, SafeCountryKey
      , Attribute1ValueKey, Attribute2ValueKey, Attribute3ValueKey, Attribute4ValueKey, Attribute5ValueKey)
      )
      WITH (MEMORY_OPTIMIZED = ON,DURABILITY = SCHEMA_ONLY);
    GO
    CREATE SECURITY POLICY ui.UILeafLevelDatasetSessionTable_SpidFilter_Policy
      ADD FILTER PREDICATE ui.udfSpidFilter(SpidFilter)
      ON ui.UILeafLevelDatasetSessionTable
      WITH (STATE = ON);
    I found that if I disabled the security constraint, that table already had about 4 million orphaned rows.
    Of course, we should make sure we cleanup regularly in our procedures but how should I go about cleaning up from time to time... just in case?

  • August 9, 2017 at 6:46 am

    #1954609

    While you can't put referential constraints on the memory optimized table, you can JOIN it in a query. If there's a table that you're referencing, just run a DELETE with a LEFT or RIGHT join to remove records that shouldn't be there. Although, better to either not load them in the first place, or delete them with the process that is deleting the rows in the referenced table.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • August 9, 2017 at 10:04 am

    #1954663

    Yes, of course, we'll try to cleanup regularly but... better be safe!

    Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

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