01 CREATE DATABASE simple version.sql

USE [master];
GO

-- What version of SQL Server am I running on?
SELECT  SERVERPROPERTY (N'ProductVersion') AS [ProductVersion] ,
        SERVERPROPERTY (N'ProductLevel') AS [ProductLevel];
GO

-- What databases are already on this SQL Server instance?
SELECT  [name] ,
        [database_id] ,
        [create_date] 
FROM sys.[databases];
GO

-- In the simplest form, we can create a database as follows:
CREATE DATABASE [PluralsightDemo];
GO

-- Rules regarding the name:
--   Maximum 128 characters (123 if the logical name isn't
--   specified for the log file)
--   Can't already be used by another database on the same
--   SQL Server instance

-- Viewing databases on the SQL Server instance, full view of
-- things
SELECT  [name] ,
        [database_id] ,
        [source_database_id] ,
        [owner_sid] ,
        [create_date] ,
        [compatibility_level] ,
        [collation_name] ,
        [user_access] ,
        [user_access_desc] ,
        [is_read_only] ,
        [is_auto_close_on] ,
        [is_auto_shrink_on] ,
        [state] ,
        [state_desc] ,
        [is_in_standby] ,
        [is_cleanly_shutdown] ,
        [is_supplemental_logging_enabled] ,
        [snapshot_isolation_state] ,
        [snapshot_isolation_state_desc] ,
        [is_read_committed_snapshot_on] ,
        [recovery_model] ,
        [recovery_model_desc] ,
        [page_verify_option] ,
        [page_verify_option_desc] ,
        [is_auto_create_stats_on] ,
        [is_auto_update_stats_on] ,
        [is_auto_update_stats_async_on] ,
        [is_ansi_null_default_on] ,
        [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_recursive_triggers_on] ,
        [is_cursor_close_on_commit_on] ,
        [is_local_cursor_default] ,
        [is_fulltext_enabled] ,
        [is_trustworthy_on] ,
        [is_db_chaining_on] ,
        [is_parameterization_forced] ,
        [is_master_key_encrypted_by_server] ,
        [is_published] ,
        [is_subscribed] ,
        [is_merge_published] ,
        [is_distributor] ,
        [is_sync_with_backup] ,
        [service_broker_guid] ,
        [is_broker_enabled] ,
        [log_reuse_wait] ,
        [log_reuse_wait_desc] ,
        [is_date_correlation_on] ,
        [is_cdc_enabled] ,
        [is_encrypted] ,
        [is_honor_broker_priority_on] ,
        [replica_id] ,
        [group_database_id] ,
        [default_language_lcid] ,
        [default_language_name] ,
        [default_fulltext_language_lcid] ,
        [default_fulltext_language_name] ,
        [is_nested_triggers_on] ,
        [is_transform_noise_words_on] ,
        [two_digit_year_cutoff] ,
        [containment] ,
        [containment_desc] ,
        [target_recovery_time_in_seconds]
FROM    sys.[databases];
GO

-- Given no explicit configurations, what are the details
-- of our new database?
EXEC sys.[sp_helpdb] N'PluralsightDemo';
GO

-- Where is the default path designated?
SELECT  SERVERPROPERTY (N'instancedefaultdatapath') AS [DefaultFile] ,
        SERVERPROPERTY (N'instancedefaultlogpath') AS [DefaultLog];

-- The default path is designated in the registry and you
-- can change via SSMS or via xp_instance_regwrite
USE [master];

EXEC [xp_instance_regwrite] N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
	N'DefaultData',
	REG_SZ,
    N'S:\SQLskills\MDF';

EXEC [xp_instance_regwrite] N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
	N'DefaultLog',
	REG_SZ,
    N'S:\SQLskills\LOG';
GO

-- Given no explicit configurations, what are the details
-- of our new database?
EXEC sys.[sp_helpdb] N'PluralsightDemo';
GO

-- Default sizes use the model database
EXEC sys.[sp_helpdb] N'model';
GO

-- Cleanup before the next demo
USE [master];
GO

DROP DATABASE [PluralsightDemo];
GO

02 Defining files and file groups with your database creation.sql

USE [master];

-- Minimum file requirements = 1 primary file and
-- 1 transaction log file
CREATE DATABASE [PluralsightDemo] 
ON PRIMARY
		-- First file in primary filegroup is the primary file
(	NAME = N'PluralsightDemo',-- logical_file_name 		
	FILENAME = N'S:\SQLskills\MDF\PluralsightDemo.mdf' ,  
		-- 'os_file_name' 
	SIZE = 1024MB ,
		-- size [ KB | MB | GB | TB ] ] 
	MAXSIZE = UNLIMITED,
		-- max_size [ KB | MB | GB | TB ] | UNLIMITED 
	FILEGROWTH = 1024MB )
		-- growth_increment [ KB | MB | GB | TB | % ] 
LOG ON -- specifies log file details
(	NAME = N'PluralsightDemo_log',
		-- logical_file_name
	FILENAME = N'S:\SQLskills\LDF\PluralsightDemo_log.ldf' , 
		-- 'os_file_name' 
	SIZE = 1024MB ,
		-- size [ KB | MB | GB | TB ] ]
	MAXSIZE = 2048GB ,
		-- max_size [ KB | MB | GB | TB ] | UNLIMITED 
	FILEGROWTH = 1024MB);
 -- Avoid growth by percentage!
 -- See Paul Randal's blog post, "Importance of data
 -- file size management", http://bit.ly/18NWw9p
GO

EXEC sp_helpdb 'PluralsightDemo';

-- Cleanup
USE [master];
GO

DROP DATABASE [PluralsightDemo];
GO

03 Defining a database with multiple data files.sql

USE [master];

-- Multiple data files
-- A maximum of 32,767 files and 32,767 filegroups
-- (not a goal, mind you)
CREATE DATABASE [PluralsightDemo] 
ON PRIMARY 
(	NAME = N'PluralsightDemo', 
	FILENAME = N'S:\SQLskills\MDF\PluralsightDemo.mdf' , 
	SIZE = 1024MB , 
	FILEGROWTH = 1024MB ), 
(	NAME = N'PluralsightDemo_2', 
	FILENAME = N'S:\SQLskills\MDF\PluralsightDemo_2.ndf' , 
	SIZE = 1024MB , 
	FILEGROWTH = 1024MB ) 
LOG ON 
(	NAME = N'PluralsightDemo_log', 
	FILENAME = N'S:\SQLskills\LDF\PluralsightDemo_log.ldf' , 
	SIZE = 1024MB , 
	FILEGROWTH = 1024MB);
GO

-- Why not have multiple transaction log files?

EXEC sp_helpdb 'PluralsightDemo';

-- Cleanup
USE [master];
GO

DROP DATABASE [PluralsightDemo];
GO

04 Creating a database with a user-defined filegroup.sql

USE [master];

-- User-defined filegroup - why use them?
CREATE DATABASE [PluralsightDemo] 
ON PRIMARY 
(	NAME = N'PluralsightDemo', 
	FILENAME = N'S:\SQLskills\MDF\PluralsightDemo.mdf' , 
	SIZE = 1024MB , 
	FILEGROWTH = 1024MB ), 
FILEGROUP [Application] 
(	NAME = N'PluralsightDemo_2', 
	FILENAME = N'S:\SQLskills\MDF\PluralsightDemo_2.ndf' , 
	SIZE = 1024MB , 
	FILEGROWTH = 1024MB ) 
LOG ON 
(	NAME = N'PluralsightDemo_log', 
	FILENAME = N'S:\SQLskills\LDF\PluralsightDemo_log.ldf' , 
	SIZE = 1024MB , 
	FILEGROWTH = 1024MB);
GO

-- What happens if someone adds a table?
USE [PluralsightDemo];
GO

-- Either reference the filegroup explicitly
CREATE TABLE [dbo].[category]
    (
      [category_no] INT IDENTITY (1, 1)
                        NOT NULL ,
      [category_desc] VARCHAR (31) NOT NULL ,
      [category_code] CHAR (2) NOT NULL ,
      CONSTRAINT [category_ident] PRIMARY KEY CLUSTERED
		( [category_no] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [Application]
    )
ON  [Application];
GO

-- Or alternatively, set the filegroup as the default
-- (I recommend this instead of depending on people to remember)
SELECT  [name]
FROM    sys.[filegroups]
WHERE   [is_default] = 1
        AND name = N'Application';
GO

ALTER DATABASE [PluralsightDemo] 
MODIFY FILEGROUP [Application] DEFAULT;
GO

SELECT  [name]
FROM    sys.[filegroups]
WHERE   [is_default] = 1
        AND name = N'Application';
GO

-- Cleanup
USE [master];
GO

DROP DATABASE [PluralsightDemo];
GO

05 Defining collation.sql

USE [master];
GO

/*
	Collations define the code page used for 
	non-Unicode character data type storage and
	sort order for Unicode and non-Unicode character types
*/

-- What is the current SQL Server instance collation?
SELECT  SERVERPROPERTY (N'Collation');
GO

-- What are the current database collation settings?
SELECT  [name] ,
        [database_id] ,
        [collation_name]
FROM    sys.[databases];
GO

-- And what are the collation options?
SELECT  [name] ,
        [description]
FROM    sys.[fn_helpcollations]();
GO

-- Creating a database designating a non-default collation
USE [master];
GO

-- Creating a database with collation explicitly set
CREATE DATABASE [PluralsightDemo] 
ON PRIMARY 	
(	NAME = N'PluralsightDemo',							
	FILENAME = N'S:\SQLskills\MDF\PluralsightDemo.mdf' ,  
	SIZE = 4096MB , 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 1024MB ) 
LOG ON 
(	NAME = N'PluralsightDemo_log', 
	FILENAME = N'S:\SQLskills\LDF\PluralsightDemo_log.ldf' , 
	SIZE = 1024MB , 
	MAXSIZE = 2048GB ,
	FILEGROWTH = 1024MB) 
COLLATE French_CS_AS;
GO

-- We can validate in sys.databases, or also use the
-- following technique
SELECT  DATABASEPROPERTYEX (N'PluralsightDemo', N'Collation')
	AS [Collation];

-- Cleanup
USE [master];
GO

DROP DATABASE [PluralsightDemo];
GO