Multiple ALTER DATABASE SET options

G’day,

Did you know that you can actually combine multiple SET operations into a single ALTER DATABASE statement.

It’s true, you can!

Mostly I never see this done.

Maybe it doesn’t make sense! maybe it’s just more readable if a single ALTER DATABASE statement contains a single SET operation! Maybe management studio has just got us into this habit of writing single ALTER DATABASE statements!

Whatever this reason I hardly ever see code that has one ALTER DATABASE statement with multiple SET operations – in fact mostly that’s the format I stick to myself – but just for the record,

The following set of TSQL statements

ALTER DATABASE [AdventureWorks2008] SET RECOVERY simple;
ALTER DATABASE [AdventureWorks2008] SET PAGE_VERIFY CHECKSUM;
ALTER DATABASE [AdventureWorks2008] SET MULTI_USER;
ALTER DATABASE [AdventureWorks2008] SET ONLINE;
GO

can actually be combined into a single statement

ALTER DATABASE [AdventureWorks2008]
SET
RECOVERY simple,
PAGE_VERIFY CHECKSUM,
MULTI_USER,
ONLINE;
GO

guess this comes down to personal choice in the end :)

Click here for the books online article if you require any further reading.

Have a good day

Cheers

Martin.

Tags: ,

About Martin Catherall

Martin Catherall is Senior SQL consultant at SQL Down Under, based in Melbourne, Australia. Martin is also a Microsoft Data Platform MVP and Regional Mentor (RM) for the Professional Association for SQL Server. Prior to relocating to Australia he was extremely active in the Christchurch, New Zealand data community - founding the local SQL Server user group and organizing SQL Saturday Christchurch - which later became SQL Saturday South Island. He likes learning interesting stuff about processing and storing data and passing on his knowledge. In his spare time he likes to learn guitar and hang out with his two young sons Callum Glen and Robert Michael.