CODEWITHSUNDEEP
sql-serverflyway
Maryo David
Maryo David

Reputation: 589

Versioned or Repeatable Scripts for Sequence & Partitions - Flyway?

I have SQL scripts for creating the Partition Function & Partition Scheme. I want the migrations to be taken care of via flyway scripts. I would like to know whether these SQL scripts can be considered as Repeatable scripts or Versioned Scripts?

Similarly, I have scripts for SEQUENCE creation, should this be considered as Versioned Scripts or Repeatable Scripts?

Upvotes: 2

Views: 348

Answers (1)

Grant Fritchey
Grant Fritchey

Reputation: 2775

You can make this a repeatable script only if it's got a check for the existence of the function before you attempt to create it. Otherwise, it'll try to create it on every deployment, causing errors. Something along the lines of:

IF NOT EXISTS( SELECT * FROM sys.partition_functions WHERE name = 'MyFunction' ) 
BEGIN
CREATE PARTITION FUNCTION...
END

You do the same thing for SEQUENCE, but you want to look for it here: sys.sequences. That's it.

Although, probably, I wouldn't make SEQUENCE or PARTITION repeatable. They're usually created once and then you're done. However, I can absolutely imagine circumstances where you're doing all sorts of different kinds of deployments to more than one system where having this stuff be repeatable, to ensure it's always there, regardless of the version, to be a good idea.

Upvotes: 1

Related Questions