/*============================================================================
  File:     6_Filegroups_Files.sql

  Summary:  This script reviews how to view filegroup and file 
			configuration and make edits using T-SQL.

  Date:     April 2017

  SQL Server Version: 2005/2008/2008R2/2014/2014/2016
------------------------------------------------------------------------------
  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.
============================================================================*/

/*
	View databases for an instance
*/
SELECT 
	[d].[database_id], 
	[d].[name], 
	[p].[name], 
	[d].[create_date]
FROM [sys].[databases] [d]
JOIN [sys].[server_principals] [p] ON [d].[owner_sid] = [p].[sid];


/*
	View filegroups in a database
*/
USE [AdventureWorks2016];
GO

SELECT *
FROM [sys].[filegroups];

SELECT *
FROM [sys].[database_files];


/*
	Add a new filegroup
*/
USE [master];
GO
ALTER DATABASE [AdventureWorks2016] 
	ADD FILEGROUP [NewData];
GO


/*
	Set it as the default 
*/
ALTER DATABASE [AdventureWorks2016] 
	MODIFY FILEGROUP [NewData] DEFAULT;
GO


/*
	Cannot set a FG as a default until it has files in it)
	Add some files, then set as default
*/
USE [master]
GO
ALTER DATABASE [AdventureWorks2016] 
	ADD FILE ( NAME = N'AdventureWorks_2014_NewData_1', 
		FILENAME = N'C:\Databases\AdventureWorks2016\AdventureWorks_2016_NewData_1.ndf' , 
			SIZE = 1024MB, 
			FILEGROWTH = 1024MB)
	TO FILEGROUP [NewData];

ALTER DATABASE [AdventureWorks2016] 
	ADD FILE ( NAME = N'AdventureWorks_2014_NewData_2', 
		FILENAME = N'C:\Databases\AdventureWorks2016\AdventureWorks_2016_NewData_2.ndf' , 
			SIZE = 1024MB, 
			FILEGROWTH = 1024MB)
	TO FILEGROUP [NewData];
GO

ALTER DATABASE [AdventureWorks2016] 
	MODIFY FILEGROUP [NewData] DEFAULT;
GO

/*
	Add another filegroup and some files
*/
USE [master];
GO
ALTER DATABASE [AdventureWorks2016] 
	ADD FILEGROUP [ArchiveData];
GO

ALTER DATABASE [AdventureWorks2016] 
	ADD FILE ( NAME = N'AdventureWorks_2014_ArchiveData_1', 
		FILENAME = N'C:\Databases\AdventureWorks2016\AdventureWorks_2016_ArchiveData_1.ndf' , 
			SIZE = 1024MB, 
			FILEGROWTH = 1024MB)
	TO FILEGROUP [ArchiveData];

ALTER DATABASE [AdventureWorks2016] 
	ADD FILE ( NAME = N'AdventureWorks_2014_ArchiveData_2', 
		FILENAME = N'C:\Databases\AdventureWorks2016\AdventureWorks_2016_ArchiveData_2.ndf' , 
			SIZE = 1024MB, 
			FILEGROWTH = 1024MB)
	TO FILEGROUP [ArchiveData];
GO


/*
	revisit existing files and filegroups
*/
USE [AdventureWorks2016];
GO

SELECT 
	[fg].[name], 
	[fg].[data_space_id], 
	[fg].[is_default], 
	[fg].[is_read_only], 
	[f].[name], 
	[f].[file_id], 
	[f].[type_desc], 
	[f].[physical_name], 
	[f].[size], 
	[f].[size]*8/1024 [SizeMB],
	[f].[max_size], 
	[f].[growth]
FROM [sys].[database_files] [f]
LEFT OUTER JOIN [sys].[filegroups] [fg] ON [f].[data_space_id] = [fg].[data_space_id]
ORDER BY [fg].[data_space_id], [f].[file_id];


/*
	Move an existing object to a new filegroup
*/
USE [AdventureWorks2016];
GO

CREATE UNIQUE CLUSTERED INDEX [PK_TransactionHistoryArchive_TransactionID]
	ON [Production].[TransactionHistoryArchive] (
		[TransactionID] ASC
		)
	WITH DROP_EXISTING
ON [ArchiveData];
GO


USE [AdventureWorks2016];
GO

CREATE NONCLUSTERED INDEX [IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID] 
	ON [Production].[TransactionHistoryArchive] (
		[ReferenceOrderID] ASC,
		[ReferenceOrderLineID] ASC
		)
	WITH (DROP_EXISTING = ON, SORT_IN_TEMPDB = ON, ONLINE = OFF)
	ON [ArchiveData];
GO


USE [AdventureWorks2016];
GO

CREATE NONCLUSTERED INDEX [IX_TransactionHistoryArchive_ProductID] 
	ON [Production].[TransactionHistoryArchive] (
		[ProductID] ASC
		)
	WITH (DROP_EXISTING = ON, SORT_IN_TEMPDB = ON, ONLINE = OFF)
	ON [ArchiveData];
GO


USE [AdventureWorks2016];
GO

CREATE NONCLUSTERED INDEX [IX_TransactionHistoryArchive_TransactionDate_TransactionType] 
	ON [Production].[TransactionHistoryArchive] (
		[TransactionDate] ASC,
		[TransactionType] ASC
		);

/*
	check location of objects
*/
SELECT [o].[name], [i].[name], [i].[type_desc], [fg].[name]
FROM [sys].[indexes] [i]
JOIN [sys].[objects] [o] ON [i].[object_id] = [o].[object_id]
JOIN [sys].[filegroups] [fg] ON [i].[data_space_id] = [fg].[data_space_id]
WHERE [o].[type] = 'U'
ORDER BY [fg].[name];
GO


/*
	Changing file settings
*/
USE [master]
GO

ALTER DATABASE [AdventureWorks2016] 
	MODIFY FILE ( NAME = N'AdventureWorks2016_Data', FILEGROWTH = 1024MB);
GO

ALTER DATABASE [AdventureWorks2016] 
	MODIFY FILE ( NAME = N'AdventureWorks2016_Log', 
		SIZE = 1024MB , 
		MAXSIZE = 4096MB , 
		FILEGROWTH = 512MB);
GO


/*
	Can view files for all databases from master.sys.master_files
*/
SELECT *
FROM [master].[sys].[master_files];