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

 

2、数据库选项

--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

3、数据库的拥有者

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'
4、数据库文件、文件组 

--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

5、管理数据库空间 

把数据虽然删除了，但是这些被删除的数据的空间，并没有释放，那么在查询数据的时候，如果你的这个表一共占用了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 --清空文件                 )