#一. 数据库基础
##1. 建库
create database wc
on primary
( name = wc_data,
filename ='d:\wc_data.mdf',
size =3MB,
maxsize = unlimited,
filegrowth = 1MB),
filegroup wc_fg1
( name = wc_fg1,
filename ='d:\wc_fg1.ndf',
size =3MB,
maxsize =1GB,
filegrowth = 100KB),
filegroup wc_fg2
( name = wc_fg2,
filename ='d:\wc_fg2.ndf',
size =3MB,
maxsize = 100MB,
filegrowth = 1MB)
LOG on
( name = wc_log,
filename = 'd:\wc_log.ldf',
size =3MB,
maxsize = 200MB,
filegrowth = 20%
)
##2.1数据库属性
select * from sys.databases
##2.2数据库大小、属性、兼容级别,文件属性
exec sp_helpdb 'wc'
##3.1只允许一个用户访问数据库
alter database wc
set single_user
with rollback after 10 seconds --指定多少秒后回滚事务
##3.2只有sysadmin,dbcreator,db_owner角色的成员可以访问数据库
alter database wc
set restricted_user
with rollback immediate --立即回滚事务
##3.3不等待立即改变用户访问,如不能立即完成,那么会导致执行错误
alter database wc
set multi_user
with no_wait
##4.1改为单用户模式
alter database wc
set single_user
with rollback immediate
##4.2删除数据库,无法删除数据库 "wc",因为该数据库当前正在使用。
drop database wc
##5.1分离数据库
use master
go
alter database wc
set single_user
with rollback immediate
exec sp_detach_db
@dbname ='wc',
@skipchecks = 'true' --true:在分离数据库前不会更新统计信息
--false:会更新统计信息,默认选项
##5.2.1附加数据库:第一种方法,将在后续版本中删除该功能
exec sp_attach_db
@dbname ='wc',
@filename1 = 'd:\wc_data.mdf' --最多指定16个文件名
##5.2.2附加数据库:第二种方法
create database wc
on (name = wc_data,
filename = 'd:\wc_data.mdf')
for attach
create database wc
on (name = wc_data,
filename = 'd:\wc_data.mdf')
for attach_rebuild_log --附加的同时重建日志文件
`
##6.1数据库重命名
ALTER DATABASE WC
MODIFY NAME = WC_NEW
##6.2删除数据库
DROP DATABASE WC_NEW
二. 数据库选项
##1.1.1只允许一个用户访问数据库
alter database wc
set single_user
with rollback after 10 seconds ##指定多少秒后回滚事务
##1.1.2只有sysadmin,dbcreator,db_owner角色的成员可以访问数据库
alter database wc
set restricted_user
with rollback immediate ##立即回滚事务
##1.1.3不等待立即改变用户访问,如不能立即完成,那么会导致执行错误
alter database wc
set multi_user
with no_wait
##1.2.1设置数据库为在线状态
ALTER DATABASE WC
SET ONLINE
##1.2.2设置数据库为脱机状态
ALTER DATABASE WC
SET OFFLINE
##1.2.3设置数据库为紧急状态
##如果数据库损坏,将数据库置为紧急状态可以允许sysadmin服务器角色到数据库的只读访问
ALTER DATABASE WC
SET EMERGENCY
##1.3.1数据库只读
ALTER DATABASE WC
SET READ_ONLY
##1.3.2数据库可读写
ALTER DATABASE WC
SET RAD_WRITE
##2.配置自动选项
##2.1当数据库还有最后一个用户连接且所有操作已经完成,会关闭数据库且释放资源
alter database wc
SET AUTO_CLOSE OFF
##2.2自动生成关于列中值的分布的统计信息
ALTER DATABASE WC
SET AUTO_CREATE_STATISTICS ON
##2.3自动更新已经为表创建的统计信息
ALTER DATABASE WC
SET AUTO_UPDATE_STATISTICS ON
##2.4当统计信息过期时,查询在编译前不会等待统计信息的更新
ALTER DATABASE WC
SET AUTO_UPDATE_STATISTICS_ASYNC OFF
##2.5当文件的未使用空间超过25%时自动收缩数据和日志文件
##不过收缩后的文件,不小于文件创建时的大小
ALTER DATABASE WC
SET AUTO_SHRINK OFF
##ANSI SQL选项
##3.1设置为ON:指定未显式定义列为NULL
##默认为OFF:列定义为NOT NULL
ALTER DATABASE WC
SET ANSI_NULL_DEFAULT OFF
##3.2设置为ON:与NULL的值的比较将返回UNKNOWN
##默认为OFF:两个NULL比较返回TRUE
ALTER DATABASE WC
SET ANSI_NULLS OFF
##3.3设置为ON:字符串与NULL连接会返回NULL
##默认为OFF:会把NULL当成空串
ALTER DATABASE WC
SET CONCAT_NULL_YIELDS_NULL OFF
##3.4设为ON:对varchar或nvarchar字符串填充到相同的长度
##默认为OFF:不会填充
ALTER DATABASE WC
SET ANSI_PADDING OFF
##3.5设为ON:标识符可以用双引号分隔,字符串可以用单引号分隔
##默认值是OFF
ALTER DATABASE WC
SET QUOTED_IDENTIFIER OFF
##3.6设为ON:在聚合函数中使用任何NULL值、除数为0、算术溢出错误,会报错
##默认OFF:不会报错
ALTER DATABASE WC
SET ANSI_WARNINGS OFF
##3.7当发生溢出、除数为0时查询会报错,事务被回滚
##默认OFF:不会报错,只是引发警告
ALTER DATABASE WC
SET ARITHABORT OFF
##3.8设置ON:表达式中有精度损失时会报错,
##OFF:不会报错,但会根据精度四舍五入
ALTER DATABASE WC
SET NUMERIC_ROUNDABORT OFF
##3.9递归触发器
ALTER DATABASE WC
SET RECURSIVE_TRIGGERS OFF
##4.1默认值为OFF,on表示在架构相同下可以跨数据库引用对象,不会检查对象安全
ALTER DATABASE WC
SET DB_CHAINING ON
##4.2默认值为OFF、on表示允许加载unsafe、external_access的CLR程序集
alter DATABASE WC
SET TRUSTWORTHY OFF
##5.1数据库的排序规则
CREATE DATABASE WCC
COLLATE UKRAINIAN_CI_AI
##5.2修改排序规则
alter database wcc
collate Chinese_PRC_CI_AS
##6.1默认是OFF,ON表示在事务提交后自动关闭T-SQL游标
ALTER DATABASE WC
SET CURSOR_CLOSE_ON_COMMIT OFF
##6.2设置为local表示创建的游标默认为本地访问,global表示全局访问
ALTER DATABASE WC
SET CURSOR_DEFAULT LOCAL
##7.1启用日期相关性优化时,SQL Server收集额外的统计信息
##当两个表通过datetime类型的外键列的关联时,有助于提高性能
ALTER DATABASE WC
SET DATE_CORRELATION_OPTIMIZATION OFF
##7.2.1启用快照隔离
ALTER DATABASE WC
SET ALLOW_SNAPSHOT_ISOLATION OFF
##7.2.2启用读已提交快照
ALTER DATABASE WC
SET READ_COMMITTED_SNAPSHOT OFF
##7.3数据库参数化
##simple表示简单参数化,
##forced表示增加查询的参数化,使查询计划可以重用,提升查询性能
ALTER DATABASE WC
SET PARAMETERIZATION SIMPLE
##8.1.1完全恢复
ALTER database WC
SET RECOVERY FULL
##8.1.2大容量加载模式
ALTER database WC
SET RECOVERY BULK_LOGGED
##8.1.3简单恢复模式
ALTER database WC
SET RECOVERY SIMPLE
##8.2.1默认设置,基于整个数据页的内容产生校验和,写入数据页头
ALTER DATABASE WC
SET PAGE_VERIFY CHECKSUM
##8.2.2通过对每个数据页的512字节扇区的反转位,检测数据页问题
ALTER DATABASE WC
SET PAGE_VERIFY TORN_PAGE_DETECTION
##8.2.3不采用任何校验
ALTER DATABASE WC
SET PAGE_VERIFY NONE
#三. 数据库的拥有者
create database wc
use wc
go
--创建登录
create login newwc with password = 'newc'
--改变数据库的拥有者
exec sp_changedbowner
@loginame = 'newwc'
--查看数据库的拥有者
select sp.name
from sys.databases d
inner join sys.server_principals sp
on sp.sid = d.owner_sid
where d.name = 'wc'
#四. 数据库文件、文件组
##1.1添加文件组
ALTER DATABASE WC
ADD FILEGROUP WC_FG8
##2.1添加数据文件
ALTER DATABASE WC
ADD FILE
(
NAME = WC_FG8,
FILENAME = 'D:\WC_FG8.ndf',
SIZE = 1mb,
MAXSIZE = 10mb,
FILEGROWTH = 1mb
)
TO FILEGROUP WC_FG8
##2.2添加日志文件
ALTER DATABASE WC
ADD LOG FILE
(
NAME = WC_LOG3,
FILENAME = 'D:\WC_FG3.LDF',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 100KB
)
##3.1移动数据文件,由于在SQL Server中文件组,文件不能离线
-- 所以必须把整个数据库设置为离线
ALTER DATABASE WC
SET OFFLINE
ALTER DATABASE WC
MODIFY FILE
(
NAME = WC_fg8,
FILENAME = 'D:\WC\WC_FG8.NDF'
)
ALTER DATABASE WC
SET ONLINE
##3.2修改数据文件的大小,增长大小,最大大小
ALTER DATABASE WC
MODIFY FILE
(
NAME = 'WC_FG8',
SIZE = 2MB, ##必须大于之前的大小,否则报错
MAXSIZE= 8MB,
FILEGROWTH = 10%
)
##3.3修改数据文件或日志文件的逻辑名称
ALTER DATABASE WC
MODIFY FILE
(
NAME = WC_LOG3,
NEWNAME = WC_FG33
)
##3.4删除日志文件
ALTER DATABASE WC
REMOVE FILE WC_FG33
##3.5删除数据文件
--把表新建在文件组wc_fg8上
CREATE TABLE T(NAME VARCHAr(1000))
on [wc_fg8]
INSERT INTO T
SELECT NAME FROM SYS.objects
1.要删除数据文件,那么这个文件必须是空的
2.那么通过shrinkfile来清空文件
3.而要清空文件必须把文件的内容移到其他文件中
4.所以又要确保wc_fg8文件组中还有其他的文件
5.增加一个文件,这样文件wc_fg8中的内容就会移动到新的文件中
ALTER DATABASE WC
ADD FILE
(
NAME = 'WC_FG18',
FILENAME = 'D:\WC_FG18.NDF',
SIZE = 2MB, ##必须大于之前的大小,否则报错
MAXSIZE= 8MB,
FILEGROWTH = 10%
)
TO FILEGROUP WC_FG8
##清空wc_fg8文件的内容
DBCC SHRINKFILE(WC_FG8,EMPTYFILE)
##移除文件,同时也在文件系统中删除底层文件
ALTER DATABASE WC
REMOVE FILE WC_FG8
##4.1设置默认文件组
ALTER DATABASE WC
MODIFY FILEGROUP WC_FG8 DEFAULT
##4.2设为只读文件组
-- 如果文件已经是某个属性,不能再次设置相同属性
ALTER DATABASE WC
MODIFY FILEGROUP WC_FG8 READ_WRITE
##4.3删除文件组
##先必须删除所有对象
DROP TABLE T
##再删除文件组中的所有文件
ALTER DATABASE WC
REMOVE FILE WC_FG88
##最后删除文件组
ALTER DATABASE WC
REMOVE FILEGROUP WC_FG8
#五. 管理数据库空间
把数据虽然删除了,但是这些被删除的数据的空间,并没有释放,那么在查询数据的时候,如果你的这个表一共占用了100页的数据,如果采用表扫描,那么还是得扫描100,如果收缩了,也就是把数据都重新排列,去掉了很多空着的空间,那么可能就剩下30页了,同样扫描,只扫描30页,就必然会快。
就像你看书资料一样,有100页的资料,然后你发现很多资料都没用了,每一页都有三分之二没用的资料,划掉了,也就是一页只有三分之一的资料是有用的。
这个时候,把这些资料,重新录入一遍,从100页减少到了33页,那么你同样看这么多的资料,所需要翻阅的页数就少了很多,速度就自然快了。
收缩数据库影响性能,是有2个方面:
1.在系统运行繁忙的时候,不要去收缩数据库,因为这会阻塞其他的事务,导致这些操作变慢。
2.也没有删除数据,就直接收缩数据库,这样有可能把大量的空闲空间给收缩了,而你的情况有所不同,是因为你先删除了数据,但是删除数据后,这些空间,并没有自动释放,通过收缩数据库,可以主动释放这些被delete的数据占用的空间。
所以,是不一样的。不过,虽然收缩数据库后,查询变快了,但确实不建议你去收缩数据库。
因为收缩数据库是一个全局的,会影响所有的表,而不是一个表,所以如果你在一个表中删除了大量的数据,而你这个表又有聚集索引,那么可以通过rebuild 来重建这个表,这样一样能达到收缩的目的,而且不会影响其他的表 和整个数据库。
/*
显示数据库的空间使用情况:
1.DATABASE_SIZE = RESERVED + UNALLOCATED SPACE(还没有分配) + 日志空间
所以DATABASE_SIZE总是大于reserved + unallocated space之和
2.RESERVED = DATA + INDEX_SIZE + UNUSED(已经分配但还没使用)
3.当delete或truncate一个大型对象后,sp_spaceused返回的值
可能不正确,这时可以用@updatesuage='true'来更新统计信息,
由于此操作可能会花费一些时间,所以只有确定这么做对其他进程没有负面应影响时才使用.
在删除或重新生成大型索引时,或者在删除或截断大型表时,
数据库采用延迟操作,数据库在事务提交后,才会释放这些延迟操作所占资源。
此外,延迟的删除操作不会立即释放已分配的空间,
所以sp_spaceused不能准确显示实际可用空间值.
*/
--显示当前数据库的空间使用情况
EXEC sp_spaceused
--显示表的空间使用
exex sp_spaceused
@objname = 'wcT',
@updateusage = 'true'
--显示整个服务器中所有数据库的日志使用情况
DBCC SQLPERF(LOGSPACE)
--收缩数据库
DBCC SHRINKDATABASE('WC', --要收缩的数据库名称或数据库ID
10 --收缩后,数据库文件中空间空间占用的百分比
)
DBCC SHRINKDATABASE('WC', --要收缩的数据库名称或数据库ID
10, --收缩后,数据库文件中空闲空间占用的百分比
NOTRUNCATE --在收缩时,通过数据移动来腾出自由空间
)
DBCC SHRINKDATABASE('WC', --要收缩的数据库名称或数据库ID
10, --收缩后,数据库文件中空间空间占用的百分比
TRUNCATEONLY --在收缩时,只是把文件尾部的空闲空间释放
)
--收缩文件
DBCC SHRINKFILE(wc_fg8, --要收缩的数据文件逻辑名称
7 --要收缩的目标大小,以MB为单位
)
DBCC SHRINKFILE(wc_fg8, --要收缩的数据文件逻辑名称
EMPTYFILE --清空文件
)
注意:这里特别需要注意,收缩文件时,特别是收缩日志文件时,收缩是否有效,决定于 日志的回复模式,如果是full模式,之前做过备份,那么收缩基本上是不会成功的,
所以需要先修改为simple模式,然后再收缩,在收缩后再修改为full模式,并做一次完全备份。