【SQL Server学习笔记】数据库的创建、设置、空间管理
【SQL Server学习笔记】数据库的创建、设置、空间管理
杨应滨 发表于4个月前
【SQL Server学习笔记】数据库的创建、设置、空间管理
  • 发表于 4个月前
  • 阅读 6
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 新注册用户 域名抢购1元起>>>   

#一. 数据库基础

##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模式,并做一次完全备份。

共有 人打赏支持
粉丝 2
博文 13
码字总数 6906
×
杨应滨
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: