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