/*============================================================================
  File:     5_Database_Settings.sql

  Summary:  This script reviews how to view existing database
			configuration settings and alter them.

  Date:     April 2018

  SQL Server Version: 2005/2008/2008R2/2012/2014/2016/2017
------------------------------------------------------------------------------
  Written by Erin Stellato, SQLskills.com

  For more scripts and other useful content, go to:
	http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Erin Stellato.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/


/*
	check current settings 
*/
SELECT 
	[database_id],
	[name],
	[collation_name],
	[recovery_model_desc],
	[compatibility_level],
	[is_auto_shrink_on],
	[is_auto_create_stats_on],
	[is_auto_update_stats_on],
	[is_auto_update_stats_async_on],
	[is_parameterization_forced],
	[page_verify_option_desc]	
FROM [master].[sys].[databases]
ORDER BY [database_id];


/*
	change settings
*/
ALTER DATABASE [AdventureWorks2017] SET RECOVERY FULL;
GO
ALTER DATABASE [AdventureWorks2017] SET COMPATIBILITY_LEVEL = 140;
GO
ALTER DATABASE [AdventureWorks2017] SET AUTO_SHRINK OFF;
GO
ALTER DATABASE [AdventureWorks2017] SET AUTO_CREATE_STATISTICS ON;
GO
ALTER DATABASE [AdventureWorks2017] SET AUTO_UPDATE_STATISTICS ON;
GO
ALTER DATABASE [AdventureWorks2017] SET AUTO_UPDATE_STATISTICS_ASYNC OFF;
GO
ALTER DATABASE [AdventureWorks2017] SET PARAMETERIZATION SIMPLE;
GO
ALTER DATABASE [AdventureWorks2017] SET PAGE_VERIFY CHECKSUM;
GO

ALTER DATABASE [AdventureWorks2017] 
	SET AUTO_SHRINK OFF, 
		AUTO_CREATE_STATISTICS ON, 
		AUTO_UPDATE_STATISTICS ON;
GO

/*
	Changing collation only affects new objects going forward!
*/
ALTER DATABASE [AdventureWorks2017] COLLATE SQL_Latin1_General_CP1_CI_AS;
GO


/*
	Check database-scoped settings
*/
USE [AdventureWorks2017];
GO
SELECT 
	[configuration_id], 
	[name], 
	[value], 
	[value_for_secondary]
FROM [sys].[database_scoped_configurations]; 
GO

ALTER DATABASE SCOPED CONFIGURATION 
	SET MAXDOP = 1;
GO  