-- Drop and create database PerfTest
-- Use multiple filegroups and multiple files per filegroup
SET NOCOUNT ON
USE master
GO

PRINT N'Drop database PerfTest'
GO
IF DB_ID (N'PerfTest') IS NOT NULL BEGIN
	DROP DATABASE [PerfTest]
END
GO

PRINT N'Create database PerfTest'
GO
CREATE DATABASE [PerfTest]
ON PRIMARY (
	NAME		= PerfTest_Primary_File1,
	FILENAME	= N'C:\PerfTest_Primary_File1.mdf',
	SIZE		= 10MB,
	FILEGROWTH	= 10%
),
(
	NAME		= PerfTest_Primary_File2,
	FILENAME	= N'C:\PerfTest_Primary_File2.mdf',
	SIZE		= 10MB,
	FILEGROWTH	= 10%
),
-- FILEGROUP PerfTest_FG1
FILEGROUP PerfTest_FG1 (
	NAME		= PerfTest_FG1_File1,
	FILENAME	= N'C:\PerfTest_FG1_File1.mdf',
	SIZE		= 10MB,
	FILEGROWTH	= 10%
),
(
	NAME		= PerfTest_FG1_File2,
	FILENAME	= N'C:\PerfTest_FG1_File2.mdf',
	SIZE		= 10MB,
	FILEGROWTH	= 10%
),
-- FILEGROUP PerfTest_FG2
FILEGROUP PerfTest_FG2 (
	NAME		= PerfTest_FG2_File1,
	FILENAME	= N'C:\PerfTest_FG2_File1.mdf',
	SIZE		= 10MB,
	FILEGROWTH	= 10%
),
(
	NAME		= PerfTest_FG2_File2,
	FILENAME	= N'C:\PerfTest_FG2_File2.mdf',
	SIZE		= 10MB,
	FILEGROWTH	= 10%
),
-- FILEGROUP PerfTest_FG3
FILEGROUP PerfTest_FG3 (
	NAME		= PerfTest_FG3_File1,
	FILENAME	= N'C:\PerfTest_FG3_File1.mdf',
	SIZE		= 10MB,
	FILEGROWTH	= 10%
),
(
	NAME		= PerfTest_FG3_File2,
	FILENAME	= N'C:\PerfTest_FG3_File2.mdf',
	SIZE		= 10MB,
	FILEGROWTH	= 10%
),
-- FILEGROUP PerfTest_FG4
FILEGROUP PerfTest_FG4 (
	NAME		= PerfTest_FG4_File1,
	FILENAME	= N'C:\PerfTest_FG4_File1.mdf',
	SIZE		= 10MB,
	FILEGROWTH	= 10%
),
(
	NAME		= PerfTest_FG4_File2,
	FILENAME	= N'C:\PerfTest_FG4_File2.mdf',
	SIZE		= 10MB,
	FILEGROWTH	= 10%
)
-- Log
LOG ON (
	NAME		= PerfTest_Log_File1,
	FILENAME	= N'C:\PerfTest_Log_File1.ldf',
	SIZE		= 5MB,
	FILEGROWTH	= 10%
),
(
	NAME		= PerfTest_Log_File2,
	FILENAME	= N'C:\PerfTest_Log_File2.ldf',
	SIZE		= 5MB,
	FILEGROWTH	= 10%
)
GO

PRINT 'Default database options'
GO

SELECT
	is_ansi_nulls_on,
	is_ansi_padding_on,
	is_ansi_warnings_on,
	is_arithabort_on,
	is_concat_null_yields_null_on,
	is_numeric_roundabort_on,
	is_quoted_identifier_on,
	is_read_committed_snapshot_on
FROM sys.databases
WHERE name ='PerfTest'
GO

PRINT 'Set database options'
GO
ALTER DATABASE [PerfTest] SET ANSI_NULLS				ON
ALTER DATABASE [PerfTest] SET ANSI_PADDING				ON
ALTER DATABASE [PerfTest] SET ANSI_WARNINGS			ON
ALTER DATABASE [PerfTest] SET ARITHABORT				ON
ALTER DATABASE [PerfTest] SET CONCAT_NULL_YIELDS_NULL	ON
ALTER DATABASE [PerfTest] SET NUMERIC_ROUNDABORT		OFF
ALTER DATABASE [PerfTest] SET QUOTED_IDENTIFIER		ON
ALTER DATABASE [PerfTest] SET READ_COMMITTED_SNAPSHOT	ON
GO

SELECT
	is_ansi_nulls_on,
	is_ansi_padding_on,
	is_ansi_warnings_on,
	is_arithabort_on,
	is_concat_null_yields_null_on,
	is_numeric_roundabort_on,
	is_quoted_identifier_on,
	is_read_committed_snapshot_on
FROM sys.databases
WHERE name ='PerfTest'
GO

PRINT '<< DONE >>'
GO