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

1、数据库基础

[sql] view plain copy
 
  1. --1.建库  
  2. create database wc  
  3. on primary  
  4. ( name = wc_data,  
  5.   filename ='d:\wc_data.mdf',  
  6.   size =3MB,  
  7.   maxsize = unlimited,  
  8.   filegrowth = 1MB),  
  9.     
  10. filegroup wc_fg1  
  11. ( name = wc_fg1,  
  12.   filename ='d:\wc_fg1.ndf',  
  13.   size =3MB,  
  14.   maxsize =1GB,  
  15.   filegrowth = 100KB),  
  16.     
  17. filegroup wc_fg2  
  18. ( name = wc_fg2,  
  19.   filename ='d:\wc_fg2.ndf',  
  20.   size =3MB,  
  21.   maxsize = 100MB,  
  22.   filegrowth = 1MB)  
  23.     
  24. LOG on  
  25. ( name = wc_log,  
  26.   filename = 'd:\wc_log.ldf',  
  27.   size =3MB,  
  28.   maxsize = 200MB,  
  29.   filegrowth = 20%  
  30. )  
  31.   
  32. --2.1数据库属性  
  33. select * from sys.databases  
  34.   
  35.   
  36. --2.2数据库大小、属性、兼容级别,文件属性  
  37. exec sp_helpdb 'wc'  
  38.   
  39. --3.1只允许一个用户访问数据库  
  40. alter database wc  
  41. set single_user   
  42. with rollback after 10 seconds --指定多少秒后回滚事务  
  43.   
  44. --3.2只有sysadmin,dbcreator,db_owner角色的成员可以访问数据库  
  45. alter database wc  
  46. set restricted_user   
  47. with rollback immediate    --立即回滚事务  
  48.   
  49. --3.3不等待立即改变用户访问，如不能立即完成，那么会导致执行错误  
  50. alter database wc  
  51. set multi_user  
  52. with no_wait     
  53.   
  54.   
  55. --4.1改为单用户模式  
  56. alter database wc  
  57. set single_user  
  58. with rollback immediate  
  59.   
  60. --4.2删除数据库,无法删除数据库 "wc"，因为该数据库当前正在使用。  
  61. drop database wc  
  62.   
  63.   
  64. --5.1分离数据库  
  65. use master  
  66. go  
  67.   
  68. alter database wc  
  69. set single_user  
  70. with rollback immediate  
  71.   
  72. exec sp_detach_db   
  73.     @dbname ='wc',  
  74.     @skipchecks = 'true' --true：在分离数据库前不会更新统计信息  
  75.                          --false：会更新统计信息，默认选项  
  76.   
  77. --5.2.1附加数据库:第一种方法，将在后续版本中删除该功能  
  78. exec sp_attach_db   
  79.     @dbname ='wc',  
  80.     @filename1 = 'd:\wc_data.mdf' --最多指定16个文件名  
  81.       
  82. --5.2.2附加数据库：第二种方法  
  83. create database wc  
  84. on (name = wc_data,  
  85.     filename = 'd:\wc_data.mdf')  
  86. for attach    
  87.   
  88. create database wc  
  89. on (name = wc_data,  
  90.     filename = 'd:\wc_data.mdf')  
  91. for attach_rebuild_log   --附加的同时重建日志文件  
  92.                      `  
  93.                            
  94. --6.1数据库重命名  
  95. ALTER DATABASE WC  
  96. MODIFY NAME = WC_NEW  
  97.   
  98. --6.2删除数据库  
  99. DROP DATABASE WC_NEW  

 

2、数据库选项

[sql] view plain copy
 
  1. --1.1.1只允许一个用户访问数据库  
  2. alter database wc  
  3. set single_user   
  4. with rollback after 10 seconds --指定多少秒后回滚事务  
  5.   
  6.   
  7. --1.1.2只有sysadmin,dbcreator,db_owner角色的成员可以访问数据库  
  8. alter database wc  
  9. set restricted_user   
  10. with rollback immediate    --立即回滚事务  
  11.   
  12.   
  13. --1.1.3不等待立即改变用户访问，如不能立即完成，那么会导致执行错误  
  14. alter database wc  
  15. set multi_user  
  16. with no_wait  
  17.   
  18.   
  19. --1.2.1设置数据库为在线状态  
  20. ALTER DATABASE WC  
  21. SET ONLINE  
  22.   
  23. --1.2.2设置数据库为脱机状态  
  24. ALTER DATABASE WC  
  25. SET OFFLINE  
  26.   
  27. --1.2.3设置数据库为紧急状态  
  28. --如果数据库损坏，将数据库置为紧急状态可以允许sysadmin服务器角色到数据库的只读访问  
  29. ALTER DATABASE WC  
  30. SET EMERGENCY    
  31.   
  32.   
  33. --1.3.1数据库只读  
  34. ALTER DATABASE WC  
  35. SET READ_ONLY  
  36.   
  37. --1.3.2数据库可读写  
  38. ALTER DATABASE WC  
  39. SET RAD_WRITE   
  40.   
  41.   
  42.   
  43. --2.配置自动选项  
  44. --2.1当数据库还有最后一个用户连接且所有操作已经完成,会关闭数据库且释放资源  
  45. alter database wc  
  46. SET AUTO_CLOSE OFF  
  47.   
  48. --2.2自动生成关于列中值的分布的统计信息  
  49. ALTER DATABASE WC  
  50. SET AUTO_CREATE_STATISTICS ON  
  51.   
  52. --2.3自动更新已经为表创建的统计信息  
  53. ALTER DATABASE WC  
  54. SET AUTO_UPDATE_STATISTICS ON  
  55.   
  56. --2.4当统计信息过期时,查询在编译前不会等待统计信息的更新  
  57. ALTER DATABASE WC  
  58. SET AUTO_UPDATE_STATISTICS_ASYNC OFF  
  59.   
  60. --2.5当文件的未使用空间超过25%时自动收缩数据和日志文件  
  61. --不过收缩后的文件,不小于文件创建时的大小  
  62. ALTER DATABASE WC  
  63. SET AUTO_SHRINK OFF  
  64.   
  65.   
  66. --ANSI SQL选项  
  67. --3.1设置为ON:指定未显式定义列为NULL  
  68. --默认为OFF:列定义为NOT NULL  
  69. ALTER DATABASE WC  
  70. SET ANSI_NULL_DEFAULT OFF  
  71.   
  72. --3.2设置为ON:与NULL的值的比较将返回UNKNOWN  
  73. --默认为OFF:两个NULL比较返回TRUE  
  74. ALTER DATABASE WC  
  75. SET ANSI_NULLS OFF  
  76.   
  77. --3.3设置为ON:字符串与NULL连接会返回NULL  
  78. --默认为OFF:会把NULL当成空串  
  79. ALTER DATABASE WC  
  80. SET CONCAT_NULL_YIELDS_NULL OFF  
  81.   
  82. --3.4设为ON:对varchar或nvarchar字符串填充到相同的长度  
  83. --默认为OFF:不会填充  
  84. ALTER DATABASE WC  
  85. SET ANSI_PADDING OFF  
  86.   
  87. --3.5设为ON:标识符可以用双引号分隔，字符串可以用单引号分隔  
  88. --默认值是OFF  
  89. ALTER DATABASE WC  
  90. SET QUOTED_IDENTIFIER OFF  
  91.   
  92. --3.6设为ON：在聚合函数中使用任何NULL值、除数为0、算术溢出错误，会报错  
  93. --默认OFF:不会报错  
  94. ALTER DATABASE WC  
  95. SET ANSI_WARNINGS OFF  
  96.   
  97. --3.7当发生溢出、除数为0时查询会报错，事务被回滚  
  98. --默认OFF:不会报错，只是引发警告  
  99. ALTER DATABASE WC  
  100. SET ARITHABORT OFF  
  101.   
  102. --3.8设置ON:表达式中有精度损失时会报错，  
  103. --OFF:不会报错，但会根据精度四舍五入  
  104. ALTER DATABASE WC  
  105. SET NUMERIC_ROUNDABORT OFF  
  106.   
  107. --3.9递归触发器  
  108. ALTER DATABASE WC  
  109. SET RECURSIVE_TRIGGERS OFF  
  110.   
  111.   
  112. --4.1默认值为OFF，on表示在架构相同下，  
  113. --可以跨数据库引用对象，不会检查对象安全  
  114. ALTER DATABASE WC  
  115. SET DB_CHAINING ON  
  116.   
  117. --4.2默认值为OFF，  
  118. --on表示允许加载unsafe、external_access的CLR程序集  
  119. alter DATABASE WC  
  120. SET TRUSTWORTHY OFF  
  121.   
  122. --5.1数据库的排序规则   
  123. CREATE DATABASE WCC  
  124. COLLATE UKRAINIAN_CI_AI  
  125.   
  126. --5.2修改排序规则  
  127. alter database wcc  
  128. collate Chinese_PRC_CI_AS  
  129.   
  130.   
  131. --6.1默认是OFF,ON表示在事务提交后自动关闭T-SQL游标  
  132. ALTER DATABASE WC  
  133. SET CURSOR_CLOSE_ON_COMMIT OFF  
  134.   
  135. --6.2设置为local表示创建的游标默认为本地访问，global表示全局访问  
  136. ALTER DATABASE WC  
  137. SET CURSOR_DEFAULT LOCAL  
  138.   
  139.   
  140.   
  141. --7.1启用日期相关性优化时，SQL Server收集额外的统计信息  
  142. --当两个表通过datetime类型的外键列的关联时，有助于提高性能  
  143. ALTER DATABASE WC  
  144. SET DATE_CORRELATION_OPTIMIZATION OFF  
  145.   
  146. --7.2.1启用快照隔离  
  147. ALTER DATABASE WC  
  148. SET ALLOW_SNAPSHOT_ISOLATION OFF  
  149.   
  150. --7.2.2启用读已提交快照  
  151. ALTER DATABASE WC  
  152. SET READ_COMMITTED_SNAPSHOT OFF  
  153.   
  154. --7.3数据库参数化  
  155. --simple表示简单参数化，  
  156. --forced表示增加查询的参数化，使查询计划可以重用，提升查询性能  
  157. ALTER DATABASE WC  
  158. SET PARAMETERIZATION SIMPLE  
  159.   
  160.   
  161.   
  162. --8.1.1完全恢复  
  163. ALTER database WC  
  164. SET RECOVERY FULL  
  165.   
  166. --8.1.2大容量加载模式  
  167. ALTER database WC  
  168. SET RECOVERY BULK_LOGGED  
  169.   
  170. --8.1.3简单恢复模式  
  171. ALTER database WC  
  172. SET RECOVERY SIMPLE  
  173.   
  174.   
  175. --8.2.1默认设置，基于整个数据页的内容产生校验和，写入数据页头  
  176. ALTER DATABASE WC  
  177. SET PAGE_VERIFY CHECKSUM  
  178.   
  179. --8.2.2通过对每个数据页的512字节扇区的反转位，检测数据页问题  
  180. ALTER DATABASE WC  
  181. SET PAGE_VERIFY TORN_PAGE_DETECTION  
  182.   
  183. --8.2.3不采用任何校验  
  184. ALTER DATABASE WC  
  185. SET PAGE_VERIFY NONE  

3、数据库的拥有者

 

[sql] view plain copy
 
  1. create database wc  
  2.   
  3. use wc  
  4. go  
  5.   
  6. --创建登录  
  7. create login newwc with password = 'newc'  
  8.   
  9. --改变数据库的拥有者  
  10. exec sp_changedbowner  
  11.     @loginame = 'newwc'  
  12.       
  13. --查看数据库的拥有者   
  14. select sp.name  
  15. from sys.databases d  
  16. inner join sys.server_principals sp  
  17.         on sp.sid = d.owner_sid   
  18. where d.name = 'wc'  

4、数据库文件、文件组

 

 

 

 

[sql] view plain copy
 
  1. --1.1添加文件组  
  2. ALTER DATABASE WC  
  3. ADD FILEGROUP WC_FG8  
  4.   
  5.   
  6. --2.1添加数据文件  
  7. ALTER DATABASE WC  
  8. ADD FILE  
  9. (  
  10.     NAME = WC_FG8,  
  11.     FILENAME = 'D:\WC_FG8.ndf',  
  12.     SIZE = 1mb,  
  13.     MAXSIZE = 10mb,  
  14.     FILEGROWTH = 1mb  
  15. )  
  16. TO FILEGROUP WC_FG8  
  17.   
  18. --2.2添加日志文件  
  19. ALTER DATABASE WC  
  20. ADD LOG FILE  
  21. (  
  22.     NAME = WC_LOG3,  
  23.     FILENAME = 'D:\WC_FG3.LDF',  
  24.     SIZE = 1MB,  
  25.     MAXSIZE = 10MB,  
  26.     FILEGROWTH = 100KB  
  27. )  
  28.   
  29.   
  30. --3.1移动数据文件,由于在SQL Server中文件组,文件不能离线  
  31. --所以必须把整个数据库设置为离线  
  32. ALTER DATABASE WC  
  33. SET OFFLINE  
  34.   
  35. ALTER DATABASE WC  
  36. MODIFY FILE  
  37. (  
  38.     NAME = WC_fg8,  
  39.     FILENAME = 'D:\WC\WC_FG8.NDF'  
  40. )  
  41.   
  42. ALTER DATABASE WC  
  43. SET ONLINE  
  44.   
  45.   
  46. --3.2修改数据文件的大小,增长大小,最大大小  
  47. ALTER DATABASE WC  
  48. MODIFY FILE  
  49. (  
  50.     NAME = 'WC_FG8',  
  51.     SIZE = 2MB,      --必须大于之前的大小,否则报错  
  52.     MAXSIZE= 8MB,  
  53.     FILEGROWTH = 10%  
  54. )  
  55.   
  56. --3.3修改数据文件或日志文件的逻辑名称  
  57. ALTER DATABASE WC  
  58. MODIFY FILE  
  59. (  
  60.     NAME = WC_LOG3,  
  61.     NEWNAME = WC_FG33  
  62. )  
  63.   
  64. --3.4删除日志文件  
  65. ALTER DATABASE WC  
  66. REMOVE FILE WC_FG33  
  67.   
  68.   
  69. --3.5删除数据文件  
  70. --把表新建在文件组wc_fg8上  
  71. CREATE TABLE T(NAME VARCHAr(1000))  
  72. on [wc_fg8]  
  73.   
  74. INSERT INTO T  
  75. SELECT NAME FROM SYS.objects  
  76.   
  77. /*===================================================  
  78. 1.要删除数据文件，那么这个文件必须是空的  
  79. 2.那么通过shrinkfile来清空文件  
  80. 3.而要清空文件必须把文件的内容移到其他文件中  
  81. 4.所以又要确保wc_fg8文件组中还有其他的文件  
  82. 5.增加一个文件，这样文件wc_fg8中的内容就会移动到新的文件中  
  83. =====================================================*/  
  84. ALTER DATABASE WC  
  85. ADD FILE  
  86. (  
  87.     NAME = 'WC_FG18',  
  88.     FILENAME = 'D:\WC_FG18.NDF',  
  89.     SIZE = 2MB,      --必须大于之前的大小,否则报错  
  90.     MAXSIZE= 8MB,  
  91.     FILEGROWTH = 10%  
  92. )  
  93. TO FILEGROUP WC_FG8  
  94.   
  95. --清空wc_fg8文件的内容  
  96. DBCC SHRINKFILE(WC_FG8,EMPTYFILE)  
  97.   
  98. --移除文件，同时也在文件系统中删除底层文件  
  99. ALTER DATABASE WC  
  100. REMOVE FILE WC_FG8  
  101.   
  102.   
  103.   
  104. --4.1设置默认文件组  
  105. ALTER DATABASE WC  
  106. MODIFY FILEGROUP WC_FG8 DEFAULT  
  107.   
  108. --4.2设为只读文件组  
  109. --如果文件已经是某个属性，不能再次设置相同属性  
  110. ALTER DATABASE WC  
  111. MODIFY FILEGROUP WC_FG8 READ_WRITE  
  112.   
  113.   
  114. --4.3删除文件组  
  115. --先必须删除所有对象  
  116. DROP TABLE T  
  117.   
  118. --再删除文件组中的所有文件  
  119. ALTER DATABASE WC  
  120. REMOVE FILE WC_FG88  
  121.   
  122. --最后删除文件组  
  123. ALTER DATABASE WC  
  124. REMOVE FILEGROUP WC_FG8  

5、管理数据库空间 

把数据虽然删除了，但是这些被删除的数据的空间，并没有释放，那么在查询数据的时候，如果你的这个表一共占用了100页的数据，如果采用表扫描，那么还是得扫描100，如果收缩了，也就是把数据都重新排列，去掉了很多空着的空间，那么可能就剩下30页了，同样扫描，只扫描30页，就必然会快。

就像你看书资料一样，有100页的资料，然后你发现很多资料都没用了，每一页都有三分之二没用的资料，划掉了，也就是一页只有三分之一的资料是有用的。

这个时候，把这些资料，重新录入一遍，从100页减少到了33页，那么你同样看这么多的资料，所需要翻阅的页数就少了很多，速度就自然快了。


收缩数据库影响性能，是有2个方面：
1.在系统运行繁忙的时候，不要去收缩数据库，因为这会阻塞其他的事务，导致这些操作变慢。

2.也没有删除数据，就直接收缩数据库，这样有可能把大量的空闲空间给收缩了，而你的情况有所不同，是因为你先删除了数据，但是删除数据后，这些空间，并没有自动释放，通过收缩数据库，可以主动释放这些被delete的数据占用的空间。

所以，是不一样的。不过，虽然收缩数据库后，查询变快了，但确实不建议你去收缩数据库。

因为收缩数据库是一个全局的，会影响所有的表，而不是一个表，所以如果你在一个表中删除了大量的数据，而你这个表又有聚集索引，那么可以通过rebuild 来重建这个表，这样一样能达到收缩的目的，而且不会影响其他的表 和整个数据库。

[sql] view plain copy
 
  1. /*================================================  
  2. 显示数据库的空间使用情况:  
  3.   
  4. 1.DATABASE_SIZE = RESERVED + UNALLOCATED SPACE(还没有分配) + 日志空间  
  5.   所以DATABASE_SIZE总是大于reserved + unallocated space之和  
  6.     
  7. 2.RESERVED = DATA + INDEX_SIZE + UNUSED(已经分配但还没使用)  
  8.   
  9. 3.当delete或truncate一个大型对象后，sp_spaceused返回的值  
  10.   可能不正确，这时可以用@updatesuage='true'来更新统计信息,  
  11.   由于此操作可能会花费一些时间,所以只有确定这么做对其他进程没有负面应影响时才使用.  
  12.   在删除或重新生成大型索引时，或者在删除或截断大型表时，  
  13.   数据库采用延迟操作,数据库在事务提交后,才会释放这些延迟操作所占资源。  
  14.   此外,延迟的删除操作不会立即释放已分配的空间,  
  15.   所以sp_spaceused不能准确显示实际可用空间值.  
  16. ==================================================*/  
  17.   
  18. --显示当前数据库的空间使用情况  
  19. EXEC sp_spaceused  
  20.   
  21.   
  22. --显示表的空间使用  
  23. exex sp_spaceused  
  24.     @objname = 'wcT',  
  25.     @updateusage = 'true'  
  26.   
  27.   
  28. --显示整个服务器中所有数据库的日志使用情况  
  29. DBCC SQLPERF(LOGSPACE)     
  30.   
  31.   
  32. --收缩数据库  
  33. DBCC SHRINKDATABASE('WC',  --要收缩的数据库名称或数据库ID  
  34.                     10     --收缩后，数据库文件中空间空间占用的百分比  
  35.                     )  
  36.   
  37. DBCC SHRINKDATABASE('WC',  --要收缩的数据库名称或数据库ID  
  38.                     10,    --收缩后，数据库文件中空闲空间占用的百分比  
  39.                     NOTRUNCATE --在收缩时，通过数据移动来腾出自由空间  
  40.                     )  
  41.                       
  42. DBCC SHRINKDATABASE('WC',  --要收缩的数据库名称或数据库ID  
  43.                     10,    --收缩后，数据库文件中空间空间占用的百分比  
  44.               TRUNCATEONLY --在收缩时，只是把文件尾部的空闲空间释放  
  45.                     )  
  46.                       
  47.   
  48. --收缩文件  
  49. DBCC SHRINKFILE(wc_fg8,   --要收缩的数据文件逻辑名称  
  50.                 7         --要收缩的目标大小，以MB为单位  
  51.                 )  
  52.                   
  53. DBCC SHRINKFILE(wc_fg8,   --要收缩的数据文件逻辑名称  
  54.                 EMPTYFILE --清空文件  
  55.                 )                            


 注意：这里特别需要注意，收缩文件时，特别是收缩日志文件时，收缩是否有效，决定于 日志的回复模式，如果是full模式，之前做过备份，那么收缩基本上是不会成功的，

所以需要先修改为simple模式，然后再收缩，在收缩后再修改为full模式，并做一次完全备份。

posted @ 2017-06-01 10:38  波波虎  阅读(2630)  评论(0)  编辑  收藏  举报