• Page: 8KB → Extent: 8 contiguous (8KB pages) → Data file: .mdf or .ndf
• Log file: .ldf

1. Data modification is send by application
2. Data pages are located in, or read into, buffer cache and modified
3. Modification is recorded in transaction log on disk
4. Checkpoint writes committed transactions to database

• Types of files
  • Primary
  • Secondary
  • Transaction logs
• File placement
  • Place files on different disks for performance and recovery

Filegroups are named collections of files, and are used to simplify data placement

• Creating user-defined filegroups
  • Performance
  • Manageability
• Specify the default filegroup
• Using read-only filegroups

Estimating the size of a database

• Size of model database
• Predicted growth
• Indexes
• Size of transaction log

• 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

CREATE DATABASE database_name
[ ON
[ PRIMARY ]  [ filespec [, n] ]
[ FILEGROUP filegroup_name [DEFAULT] ]
  filespec [, n]  ] [, n] ]
[ LOG ON
  [filespec [, n] ]
[ COLLATE collation_name ]

DB정보 보기

EXEC sp_helpdb database_name

• Enlarging a database file

  ALTER DATABASE database_name
  MODIFY FILE
  ( NAME = logical_file_name,
    SIZE = size [ KB | MB | GB | TB ] )

• Shrinking a database file

  DBCC SHRINKFILE
  ( { 'file_name' | file_id }
    { [ ,EMPTYFILE ]
    | [ [ , target_size ] [ , {
    NOTRUNCATE | TRUNCATEONLY } ] ]
    } )

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.

CREATE SCHEMA
schema_name | AUTHORIZATION owner_name |
  schema_name AUTHORIZATION owner_name
[ table_definition | view_definition |
  grant_statement | revoke_statement |
  deny_statement ]

스키마 정보 보기

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

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.

Set database options by using:

• SQL Server Management Studio
• ALTER DATABASE statement

^ Option ^ Category Purpose ^

싱글유저 모드로 전환하기 (OFFLINE을 하기전에 실행한다. 이 작업을 수행하지 않으면 OFFLINE이 무척이나 오래걸리게 된다.)

ALTER DATABASE Sample SET SINGLE_USER
WITH ROLLBACK AFTER 60;