SQL Server学习(二)

创建数据库:
包括:
1.定义数据库
2.事务日志的工作过程
3.设置数据库选项
4.检索数据库信息

定义数据库:
1.数据库名字
2.数据库的大小
3.数据库所在文件

定义数据库的两种方法:
1.SQL Server 企业管理器
2.在查询分析器中输入 CREATE DATABASE 语句

语法:
CREATE DATABASE database_name
 [ON
  [PRIMARY] ( NAME = logical_file_name,
  FILENAME = 'os_file_name'
  [, SIZE = size]
  [,MAXSIZE = {max_size|unknow}]
  [,FILEGROWTH = growth_increament])
  }[,...n]
 ]
 [LOG ON
  {( NAME = logical_file_name,
  FILENAME = 'os_file_name'
  [,SIZE = size]
  [,MAXSIZE = {max_size|UNLIMITED}]
  [,FILEGROWTH = growth_increment])
  }[,...n]
 ]
 [COLLATE collation_name]
  FILEGROWTH = 1MB)
  COLLATE Chinese_PRC_CI_AS

//注释: PRIMARY指定主文件组中的文件,包括所有数据库系统表,建议文件扩展名为.mdf
FILEGROTH: 指定文件的增长量.0表示不增长,默认值为10%
COLLATION:指定数据库的默认排序规则.

CREATE DATABASE Sample
 ON
  PRIMARY ( NAME = SampleData,
  FILENAME = 'D:/Data/Sample.mdf',
  SIZE = 10MB,
  MAXSIZE = 15MB,
  FILEGROWTH = 20%)
 LOG ON
  ( NAME = SampleLog,
  FILENAME = 'D:/Data/Sample.txt',
  SIZE = 3MB,
  MAXSIZE = 5MB,
  FILEGROWTH = 1MB)
  COLLATE Chinese_PRC_CI_AS
go

物理数据库构架、逻辑数据库成分

创建数据库时可以使用除.mdf, .ndf, .ldf之外的文件扩展名

事务日志工作过程
1.应用程序发送数据修改请求;
2.在缓存中定位数据页或将数据页读入缓存;
3.修改记录在磁盘上的事务日志;
4.检查点将已提交的事务写入数据库中.

在检查点处，SQL Server 确保所有已修改的事务日志记录和数据库页都写入磁盘。

前滚事务的条件：在重新启动 SQL Server 时所发生的各数据库的恢复过程中，仅在不知道事务中所有的数据修改是否已经从高速缓冲中实际写入磁盘时才必须前滚事务。

前滚事务的条件是：仅当完成了事务日志工作过程(上面)的第1，2，3步骤

设置数据库选项的两种方式：
1。企业管理器
2。ALTER DATABASE 语句

一共有五类数据库选项
1.自动选项
AUTO_CREATE_STATISTICS(自动创建统计信息), AUTO_UPDATE_STATISTICS（自动更新统计信息）
2.游标选项
CURSOR_CLOSE_ON_COMMIT(提交事务时，所有打开的游标都将自动关闭), URSOR_DEFAULT LOCAL | GLOBAL(限制游标的范围)
3.恢复选项
RECOVERY FULL (完全)| BULK_LOGGED(大容量日志记录) | SIMPLE(简单), TORN_PAGE_DETECTION(残缺页检测)
4.SQL选项
ANSI_NULL_DEFAULT, ANSI_NULLS
5.状态选项
READ_ONLY(只读) | READ_WRITE(读写),
SINGLE_USER | RESTRICTED_USER | MULTI_USER

检索数据库信息
语法 SELECT DATABASEPROPERTYEX ( database, property )

文件组
文件组主要包括主数据文件(.mdf),扶助数据文件(.ndf),日志文件(.ldf)

原因:
1.利用文件组将不同文件分布在不同磁盘,从而将频繁查询和频繁修改的文件分离开,减少磁盘驱动器的争用,加快速度.

2.系统管理员可以备份和恢复独立的文件或文件组，以此代替备份或恢复整个数据库。对需要拥有有效备份和恢复策略的大型数据库来说，必须备份文件或文件组。

浏览文件组信息
使用函数:
sp_helpfile [[@文件名=]‘名称’] 返回与当前数据库关联的文件的物理名称和属性
sp_helpfilegroup [文件组名] 返回与当前数据库关联的文件组的名称和属性
例如建立了Sample的数据库后查询
use Sample
exec sp_helpfile
go
得到如下结果:
SampleData 1 D:/Data/Sample.mdf PRIMARY 10240 KB 15360 KB 20% data only
SampleLog 2 D:/Data/Sample.txt NULL 3072 KB 5120 KB 1024 KB log only

查询:
exec sp_helpfilegroup
得到:
PRIMARY 1 1

改变文件组：
举例：
ALTER DATABASE Sample
ADD FILEGROUP orderGroup
go
//增加一个文件组orderGroup到Sample

ALTER DATABASE Sample
ADD FILE
(
 NAME = 'orderYear',
 FILENAME = 'D:/Data/orderYear.ndf',
 SIZE = 3MB
)
TO FILEGROUP orderGroup
GO
//增加一个文件orderYear到文件组orderGroup。

再用“exec sp_helpfile”得到
SampleData 1 D:/Data/Sample.mdf PRIMARY 10240 KB 15360 KB 20% data only
SampleLog 2 D:/Data/Sample.txt NULL 3072 KB 5120 KB 1024 KB log only
orderYear 3 D:/Data/orderYear.ndf orderGroup 3072 KB Unlimited 1024 KB data only

注意四点：
1.每个数据库都有一个文件组作为默认文件组运行。若sql server创建表或索引时没有为其指定文件组，那么将从默认文件组中进行存储页分配。可以指定默认文件组。如果没有指定默认文件组，则主文件组就是默认文件组。
2.若不指定用户定义文件组，则所有数据文件都包含在主文件组中。
3.一个文件只能属于一个文件组，只有数据文件才能作为文件组的成员，日志文件不能作为文件组成员。一个文件组也只能被一个数据库使用。
4.如果数据库包含两个以上的文件组，建议将默认文件组改为用户定义文件组。这样，可以防止系统表的主文件组的空间意外地被用户表的数据耗尽。