====== Lesson 1: Planning Databases ======

===== How Is Data Stored? =====
  * Page: 8KB -> Extent: 8 contiguous (8KB pages)  -> Data file: .mdf or .ndf
  * Log file: .ldf


===== How the Transaction Log Works =====
  - Data modification is send by application
  - Data pages are located in, or read into, buffer cache and modified
  - Modification is recorded in transaction log on disk
  - Checkpoint writes committed transactions to database


===== Considerations for File Placement =====
  * Types of files
    * Primary
    * Secondary
    * Transaction logs
  * File placement
    * Place files on different disks for performance and recovery


===== What Are Filegroups? =====
Filegroups are named collections of files, and are used to simplify data placement


===== Considerations for Creating Filegroups =====
  * Creating user-defined filegroups
    * Performance
    * Manageability
  * Specify the default filegroup
  * Using read-only filegroups


===== Considerations for Capacity Planning =====
Estimating the size of a database
  * Size of model database
  * Predicted growth
  * Indexes
  * Size of transaction log


====== Lesson 2: Creating Databases ======
===== Considerations for Creating Databases =====
  * Database files include primary, secondary, and log files
  * Considerations for database size
    * Initial size of data files
    * Initial size of log files
    * Potential growth of physical data storage


===== Syntax for Creating Databases =====
<code>
CREATE DATABASE database_name
[ ON
[ PRIMARY ]  [ filespec [, n] ]
[ FILEGROUP filegroup_name [DEFAULT] ]
  filespec [, n]  ] [, n] ]
[ LOG ON
  [filespec [, n] ]
[ COLLATE collation_name ]
</code>

:!: DB정보 보기
<code sql>
EXEC sp_helpdb database_name
</code>


===== How to Resize Database Files =====
  * Enlarging a database file<code>
ALTER DATABASE database_name
MODIFY FILE
( NAME = logical_file_name,
  SIZE = size [ KB | MB | GB | TB ] )
</code>
  * Shrinking a database file<code>
DBCC SHRINKFILE
( { 'file_name' | file_id }
  { [ ,EMPTYFILE ]
  | [ [ , target_size ] [ , {
  NOTRUNCATE | TRUNCATEONLY } ] ]
  } )
</code>


===== What Are Schemas? =====
A schema is a namespace for database objects.\\
Every database contains a schema called dbo. The dbo schema is the default schema for all users who do not have an explicitly defined default schema.


===== Syntax for Creating Schemas =====
<code>
CREATE SCHEMA
schema_name | AUTHORIZATION owner_name |
  schema_name AUTHORIZATION owner_name
[ table_definition | view_definition |
  grant_statement | revoke_statement |
  deny_statement ]
</code>

:!: 스키마 정보 보기
<code>
1) SELECT * FROM SYS.SCHEMAS
2) SELECT DISTINCT TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES
3) SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT
   FROM INFORMATION_SCHEMA.COLUMNS
</code>


===== How Object Name Resolution Works =====
SQL Server 2005 uses the following process to resolve an unqualified object name:
  * If the user has a default schema, SQL Server attempts to find the object in the default schema.
  * If the object is not found in the user's default schema, or if the user has no explicitly defined default schema, SQL Server attempts to find the object in the dbo schema.


====== Lesson 3: Managing Databases ======

===== Database Options =====
Set database options by using:
  * SQL Server Management Studio
  * ALTER DATABASE statement
^  Option  ^  Category Purpose  ^
^  Automatic  | Controls automatic behaviors such as statistics, database closing, and shrinking |
^  Availability  | Controls whether database is online, who can connect to it, and whether database is read-only |
^  Cursor  ^ Controls cursor bahavior and scope |
^  Recovery  | Controls the recovery model for the  database |
^  SQL  | Controls ANSI compliance options such as ANSI nulls and recursive triggers |

:!: 싱글유저 모드로 전환하기 (OFFLINE을 하기전에 실행한다. 이 작업을 수행하지 않으면 OFFLINE이 무척이나 오래걸리게 된다.)
<code sql>
ALTER DATABASE Sample SET SINGLE_USER
WITH ROLLBACK AFTER 60;
</code>


===== Sources of Database Metadata =====
^  Information Source   ^  Description  ^
^  SQL Server Management Studio  | Visual tool that generates reports displaying server and database metadata |
^  Catalog views  | Provide metadata about database objects returning rows of information |
^  Metadata functions  | Return a single value of metadata information per function |
^  System stored procedures  | Retrieve metadata using stored procedures |