1、数据库基础
[sql]
1. --1.建库
2. create database wc
3. on primary
4. ( name = wc_data,
5. 'd:\wc_data.mdf',
6. size =3MB,
7. maxsize = unlimited,
8. filegrowth = 1MB),
9.
10. filegroup wc_fg1
11. ( name = wc_fg1,
12. 'd:\wc_fg1.ndf',
13. size =3MB,
14. maxsize =1GB,
15. filegrowth = 100KB),
16.
17. filegroup wc_fg2
18. ( name = wc_fg2,
19. 'd:\wc_fg2.ndf',
20. size =3MB,
21. maxsize = 100MB,
22. filegrowth = 1MB)
23.
24. LOG on
25. ( name = wc_log,
26. '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. 'wc',
74. 'true' --true：在分离数据库前不会更新统计信息
75. --false：会更新统计信息，默认选项
76.
77. --5.2.1附加数据库:第一种方法，将在后续版本中删除该功能
78. exec sp_attach_db
79. 'wc',
80. 'd:\wc_data.mdf' --最多指定16个文件名
81.
82. --5.2.2附加数据库：第二种方法
83. create database wc
84. on (name = wc_data,
85. 'd:\wc_data.mdf')
86. for attach
87.
88. create database wc
89. on (name = wc_data,
90. '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]
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 NONE3、数据库的拥有者
[sql]
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. '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]
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. '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. '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. '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. '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_FG85、管理数据库空间
把数据虽然删除了，但是这些被删除的数据的空间，并没有释放，那么在查询数据的时候，如果你的这个表一共占用了100页的数据，如果采用表扫描，那么还是得扫描100，如果收缩了，也就是把数据都重新排列，去掉了很多空着的空间，那么可能就剩下30页了，同样扫描，只扫描30页，就必然会快。
就像你看书资料一样，有100页的资料，然后你发现很多资料都没用了，每一页都有三分之二没用的资料，划掉了，也就是一页只有三分之一的资料是有用的。
这个时候，把这些资料，重新录入一遍，从100页减少到了33页，那么你同样看这么多的资料，所需要翻阅的页数就少了很多，速度就自然快了。
收缩数据库影响性能，是有2个方面：
1.在系统运行繁忙的时候，不要去收缩数据库，因为这会阻塞其他的事务，导致这些操作变慢。
2.也没有删除数据，就直接收缩数据库，这样有可能把大量的空闲空间给收缩了，而你的情况有所不同，是因为你先删除了数据，但是删除数据后，这些空间，并没有自动释放，通过收缩数据库，可以主动释放这些被delete的数据占用的空间。
所以，是不一样的。不过，虽然收缩数据库后，查询变快了，但确实不建议你去收缩数据库。
因为收缩数据库是一个全局的，会影响所有的表，而不是一个表，所以如果你在一个表中删除了大量的数据，而你这个表又有聚集索引，那么可以通过rebuild 来重建这个表，这样一样能达到收缩的目的，而且不会影响其他的表 和整个数据库。
[sql]
1. /*================================================
2. 显示数据库的空间使用情况:
3.
4. 1.DATABASE_SIZE = RESERVED + UNALLOCATED SPACE(还没有分配) + 日志空间
5. space之和
6.
7. 2.RESERVED = DATA + INDEX_SIZE + UNUSED(已经分配但还没使用)
8.
9. 3.当delete或truncate一个大型对象后，sp_spaceused返回的值
10. 'true'来更新统计信息,
11. 由于此操作可能会花费一些时间,所以只有确定这么做对其他进程没有负面应影响时才使用.
12. 在删除或重新生成大型索引时，或者在删除或截断大型表时，
13. 数据库采用延迟操作,数据库在事务提交后,才会释放这些延迟操作所占资源。
14. 此外,延迟的删除操作不会立即释放已分配的空间,
15. 所以sp_spaceused不能准确显示实际可用空间值.
16. ==================================================*/
17.
18. --显示当前数据库的空间使用情况
19. EXEC sp_spaceused
20.
21.
22. --显示表的空间使用
23. exex sp_spaceused
24. 'wcT',
25. 'true'
26.
27.
28. --显示整个服务器中所有数据库的日志使用情况
29. DBCC SQLPERF(LOGSPACE)
30.
31.
32. --收缩数据库
33. DBCC SHRINKDATABASE('WC', --要收缩的数据库名称或数据库ID
34. --收缩后，数据库文件中空间空间占用的百分比
35. )
36.
37. DBCC SHRINKDATABASE('WC', --要收缩的数据库名称或数据库ID
38. --收缩后，数据库文件中空闲空间占用的百分比
39. --在收缩时，通过数据移动来腾出自由空间
40. )
41.
42. DBCC SHRINKDATABASE('WC', --要收缩的数据库名称或数据库ID
43. --收缩后，数据库文件中空间空间占用的百分比
44. --在收缩时，只是把文件尾部的空闲空间释放
45. )
46.
47.
48. --收缩文件
49. DBCC SHRINKFILE(wc_fg8, --要收缩的数据文件逻辑名称
50. --要收缩的目标大小，以MB为单位
51. )
52.
53. DBCC SHRINKFILE(wc_fg8, --要收缩的数据文件逻辑名称
54. --清空文件
55. )注意：这里特别需要注意，收缩文件时，特别是收缩日志文件时，收缩是否有效，决定于 日志的回复模式，如果是full模式，之前做过备份，那么收缩基本上是不会成功的，
所以需要先修改为simple模式，然后再收缩，在收缩后再修改为full模式，并做一次完全备份。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
