How can I move system-versioned tables to a different file group in the same database?

Kman 46 Reputation points
2022-06-07T21:21:05.417+00:00

I have a SQL 2019 SQL server and due to a lot of reasons we want to move all our tables to a new File group and file. The regular tables are easy to move but I couldn't figure a way out to move system-versioned tables with a computed fields and a couple of fields with a datatype that is set to be GENERATED ALWAYS AS ROW START/END.

is there a way to move these tables?

Thanks in advance

Developer technologies | Transact-SQL
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 122.4K Reputation points MVP Volunteer Moderator
    2022-06-08T21:22:51.283+00:00

    Attached is a full demo on how to do it. (For some reasons, the forum did not agree when I included the code directly in the post.) You will have to change the file paths.

    And, no, there is no issue with the date columns or the computed columns.

    209598-slask.txt

    1 person found this answer helpful.

  2. Erland Sommarskog 122.4K Reputation points MVP Volunteer Moderator
    2022-06-08T06:26:32.587+00:00

    The normal way to move a table is

    CREATE CLUSTERED INDEX ix ON tbl(col1, col2) WITH (DROP_EXISTING = ON) ON NewFileGroup
    

    I don't have the time to run a test myself right now, but I can't see why this would not work for a temporal table? Just keep in mind that you have two tables.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.