文档章节

【SQL Server学习笔记】数据库的创建、设置、空间管理

杨应滨
 杨应滨
发布于 2017/08/16 09:50
字数 3179
阅读 9
收藏 0
点赞 0
评论 0

#一. 数据库基础

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

本文转载自:http://www.cnblogs.com/sunbobohu/p/6927692.html

共有 人打赏支持
杨应滨
粉丝 1
博文 14
码字总数 7911
作品 0
广州
技术主管
最全的Windows Azure学习教程汇总

转载: http://blog.csdn.net/powertoolsteam/article/details/53082470 目录(?)[-] 一 Windows Azure 平台简介 二Windows Azure入门教学系列 三Azure学习笔记 四Azure Storage 基本用法介绍......

chenhao_asd ⋅ 2017/02/26 ⋅ 0

最全的Windows Azure学习教程汇总

Windows Azure 是微软基于云计算的操作系统,能够为开发者提供一个平台,帮助开发可运行在云服务器、数据中心、Web 和 PC 上的应用程序。 Azure 是一种灵活和支持互操作的平台,能够将处于云...

葡萄城控件技术团队 ⋅ 2016/11/08 ⋅ 0

Sql Server2005 Transact-SQL 新兵器学习总结之-DDL触发器

1.简介: Sql Server2005新增加了DDL触发器。 与DML触发器不同的是,它们不会为响应针对表或视图的UPDATE、INSERT或DELETE语句而激发。 相反,它们将为了响应各种数据定义语言(DDL)事件而激发...

范大脚脚 ⋅ 2017/12/14 ⋅ 0

ORACLE学习笔记(二)​

DB Administration Tools Oracle Universal Installer(OUI) :OUI是用来安装、升级和删除Oracle软件。 Oracle Database Configuration Assistance(DBCA) :DBCA是一个图形界面的程序,可...

coolio ⋅ 2014/10/19 ⋅ 0

Sybase ASE学习笔记

通过这两天的学习,由于之前完全没有接触过sybase,发现sybase跟oracle或者SQL server都有比较大的差别,一是需要配置运行sybase的环境,二是sybase采用的是Transaction-SQL。三是,其数据库...

wangxuwei ⋅ 2016/02/25 ⋅ 0

Oracle - 数据库的实例、表空间、用户、表之间关系

一直以来都没将Oracle数据库,实例,表空间,用户,表之间的关系搞清楚,虽然工作了有些时间,在工作中也用到Oracle开发,但却忙于项目没顾得上,今天抽空简单整理了下,主要是根据自己的理解...

Sheamus ⋅ 2015/04/22 ⋅ 2

ORACLE学习笔记(三)​

Initialization Parameter Files 一个Oracle Instance在启动之前,他必须有一些必要的参数,这些参数称为初始化参数,这些参数保存在一个初始化参数文件中。初始化参数文件就是Oracle在执行启...

coolio ⋅ 2014/10/19 ⋅ 0

SQL SERVER 2000的安装步骤(有图) SQL Server 2000企业版安装教程

SQL SERVER 2000的安装步骤(有图) SQL Server 2000企业版安装教程 目前国内的一些关于微软平台的数据库编程教程,喜欢使用Access作为数据库平台的案例,并展开相关的内容。这其实对于开发真正...

yangleitse ⋅ 2010/11/03 ⋅ 0

OCP笔记部分整理-学习参考

database 2. instance 3. session 4. process 5. table 6. index 7. transaction (事务) 8. lock 9. cursor 10. awr(auto workload repository) 11. service 12. data guard 13. rac 查询服务......

pimg2005 ⋅ 2013/04/21 ⋅ 0

SQL Server中数据库文件的存放方式,文件和文件组

原文地址:http://www.cnblogs.com/CareySon/archive/2011/12/26/2301597.html SQL Server中数据库文件的存放方式,文件和文件组 写在前面:上次我关于索引的文章有几个园友发站内信问我如何...

随智阔 ⋅ 2015/03/24 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

浅谈springboot Web模式下的线程安全问题

我们在@RestController下,一般都是@AutoWired一些Service,由于这些Service都是单例,所以并不存在线程安全问题。 由于Controller本身是单例模式 (非线程安全的), 这意味着每个request过来,...

算法之名 ⋅ 今天 ⋅ 0

知乎Java数据结构

作者:匿名用户 链接:https://www.zhihu.com/question/35947829/answer/66113038 来源:知乎 著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。 感觉知乎上嘲讽题主简...

颖伙虫 ⋅ 今天 ⋅ 0

Confluence 6 恢复一个站点有关使用站点导出为备份的说明

推荐使用生产备份策略。我们推荐你针对你的生产环境中使用的 Confluence 参考 Production Backup Strategy 页面中的内容进行备份和恢复(这个需要你备份你的数据库和 home 目录)。XML 导出备...

honeymose ⋅ 今天 ⋅ 0

JavaScript零基础入门——(九)JavaScript的函数

JavaScript零基础入门——(九)JavaScript的函数 欢迎回到我们的JavaScript零基础入门,上一节课我们了解了有关JS中数组的相关知识点,不知道大家有没有自己去敲一敲,消化一下?这一节课,...

JandenMa ⋅ 今天 ⋅ 0

火狐浏览器各版本下载及插件httprequest

各版本下载地址:http://ftp.mozilla.org/pub/mozilla.org//firefox/releases/ httprequest插件截至57版本可用

xiaoge2016 ⋅ 今天 ⋅ 0

Docker系列教程28-实战:使用Docker Compose运行ELK

原文:http://www.itmuch.com/docker/28-docker-compose-in-action-elk/,转载请说明出处。 ElasticSearch【存储】 Logtash【日志聚合器】 Kibana【界面】 答案: version: '2'services: ...

周立_ITMuch ⋅ 今天 ⋅ 0

使用快嘉sdkg极速搭建接口模拟系统

在具体项目研发过程中,一旦前后端双方约定好接口,前端和app同事就会希望后台同事可以尽快提供可供对接的接口方便调试,而对后台同事来说定好接口还仅是个开始、设计流程,实现业务逻辑,编...

fastjrun ⋅ 今天 ⋅ 0

PXE/KickStart 无人值守安装

导言 作为中小公司的运维,经常会遇到一些机械式的重复工作,例如:有时公司同时上线几十甚至上百台服务器,而且需要我们在短时间内完成系统安装。 常规的办法有什么? 光盘安装系统 ===> 一...

kangvcar ⋅ 昨天 ⋅ 0

使用Puppeteer撸一个爬虫

Puppeteer是什么 puppeteer是谷歌chrome团队官方开发的一个无界面(Headless)chrome工具。Chrome Headless将成为web应用自动化测试的行业标杆。所以我们很有必要来了解一下它。所谓的无头浏...

小草先森 ⋅ 昨天 ⋅ 0

Java Done Right

* 表示难度较大或理论性较强。 ** 表示难度更大或理论性更强。 【Java语言本身】 基础语法,面向对象,顺序编程,并发编程,网络编程,泛型,注解,lambda(Java8),module(Java9),var(...

风华神使 ⋅ 昨天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部