SQL Server 2008学习笔记：日常维护、深入管理、性能优化

　　SQLServer2008是Microsoft公司在数据库领域中非常重要的产品，是关系型数据库的代表产品之一，很多大型数据库应用系统都采用SQLServer作为后台数据库。《SQLServer2008学习笔记：日常维护、深入管理、性能优化》设计了大量的实验，介绍了数据库管理员和开发人员常用的管理、维护和优化SQLServer2008数据库技术。

　　通过阅读《SQLServer2008学习笔记：日常维护、深入管理、性能优化》，读者可以了解SQLServer2008的体系结构、安装过程、数据库管理和配置、数据库安全、数据库日常维护、Transact-SQL语言等数据库管理员的必备常识，还可以掌握SQLServer2008的多种高级应用，包括使用最新推出的空间数据技术、使用ReportingServices设计统计报表、用全文搜索技术对数据库

　　中的二进制文件数据进行搜索以及对SQLServer数据库进行性能监测、分析和优化等。

　　《SQLServer2008学习笔记：日常维护、深入管理、性能优化》内容丰富、注重实用，理论讲解与大量实验相结合，特别适用于SQLServer数据库管理员和应用程序开发人员，以及对相关技术感兴趣的读者，也可以作为大中专院校或培训机构的相关课程教材。

第一篇日常应用1

第1章安装及常用工具使用3
1.1基础知识4
1.1.1SQLServer2008产品概况4
1.1.2安装SQLServer2008的软、硬件需求4
1.2应用情景6
安装SQLServer2008数据库6
查看和配置SQLServer服务14
配置Windows防火墙允许访问SQLServer2008服务器15
使用SQLServerManagementStudio16
使用SQLServerBusinessIntelligenceDevelopmentStudio19
SQLServer配置管理器20
osql实用工具21
sqlcmd实用工具22

第2章Transact-SQL语言24
2.1基础知识25
2.1.1Transact-SQL的语法元素25
2.1.2数据库对象的命名26
2.1.3同义词27
2.1.4数据类型27
2.1.5常量31
2.1.6变量32
2.1.7运算符33
2.1.8常用函数35
2.1.9流程控制语句38
2.1.10Transact-SQL语句的解析、编译和执行40
2.2应用情景40
使用PRINT语句向客户端返回用户定义的消息40
使用SETSTATISTICSIO语句41
使用SETSTATISTICSTIME语句42
创建同义词43
删除同义词44
从系统视图sys.synonyms中查询同义词信息44
创建用户定义数据类型45
删除用户定义数据类型47
从INFORMATION_SCHEMA.DOMAINS中获取用户定义数据类型信息48
查看用户定义数据类型的使用情况49
练习使用日期和时间函数50
练习使用数学函数51
使用SETDATEFORMAT设置日期和时间格式51
练习使用字符串函数52
练习使用IF…ELSE语句53
练习使用WHILE语句54
练习使用WAITFOR语句55
练习使用GOTO语句55
练习使用TRY…CATCH语句55
查看错误消息56
使用RAISERROR语句生成错误消息57
使用sp_addmessage存储过程添加用户定义错误58
使用sp_altermessage存储过程修改用户定义错误消息59
使用sp_dropmessage存储过程删除用户定义错误消息59

第3章数据库管理60
3.1基础知识61
3.1.1系统数据库61
3.1.2数据库文件61
3.1.3文件组62
3.1.4页和区63
3.2应用情景63
在SQLServerManagementStudio中创建数据库64
在SQLServerManagementStudio中查看数据库信息65
使用SELECT语句查看数据库信息66
使用sp_helpdb查看数据库信息67
使用sp_spaceused查看数据库空间使用信息69
使用简单的CREATEDATABASE语句创建数据库70
使用CREATEDATABASE语句在创建数据库时指定数据文件71
在SQLServerManagementStudio中创建和使用文件组72
使用CREATEDATABASE语句在创建数据库时指定文件组73
使用CREATEDATABASE语句在创建数据库时指定事务日志文件74
向数据库中添加文件组75
修改文件组属性76
删除文件组76
向数据库中添加文件76
修改数据库中的文件77
删除数据库中的文件78
从系统视图sys.database_files中获取数据文件的信息79
在SQLServerManagementStudio中扩充数据或事务日志空间80
在SQLServerManagementStudio中收缩数据或事务日志空间82
使用DBCCSHRINKDATABASE语句收缩数据库83
使用DBCCSHRINKFILE语句收缩指定的数据库文件84
设置自动收缩数据库选项84
重命名数据库85
删除数据库86
查看数据库磁盘使用情况87
移动用户数据库87

第4章表和视图89
4.1基础知识90
4.1.1表90
4.1.2表约束90
4.1.3视图91
4.2应用情景92
创建表92
查看和管理表中的数据96
查看表的磁盘空间信息97
使用DBCCCHECKIDENT命令检查和设置表的标识值97
重命名表99
修改表的列名99
向表中添加列100
修改列属性100
删除表中的列101
删除表101
创建主键约束102
修改主键约束104
删除主键约束104
创建、修改和删除唯一性约束105
从sys.key_constraints获取约束信息106
创建检查约束107
修改检查约束109
删除检查约束109
从INFORMATION_SCHEMA.CHECK_CONSTRAINTS获取检查约束信息109
创建和使用默认约束110
创建和使用外键约束111
从sys.foreign_keys获取表中的外键约束114
从sys.foreign_key_columns获取外键约束的列信息115
从INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS获取外键约束信息116
从INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE获取约束信息117
从INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE获取表中的约束118
从INFORMATION_SCHEMA.TABLE_CONSTRAINTS获取约束的基本信息119
从INFORMATION_SCHEMA.KEY_COLUMN_USAGE获取键约束列的信息120
创建视图121
修改视图125
删除视图125
从INFORMATION_SCHEMA.TABLES中获取表和视图信息126
从INFORMATION_SCHEMA.VIEWS中获取视图信息127
从INFORMATION_SCHEMA.COLUMNS中获取列信息128
从INFORMATION_SCHEMA.VIEW_COLUMN_USAGE获取视图中列的信息130
从sys.columns中获取列信息130
从INFORMATION_SCHEMA.VIEW_TABLE_USAGE获取视图中包含表的信息133
从系统表sys.objects中获取所有数据库对象的信息134

第5章规则和索引135
5.1基础知识136
5.1.1规则136
5.1.2索引136
5.2应用情景137
创建规则138
查看规则138
绑定规则139
解除绑定规则139
删除规则140
在SQLServerManagementStudio中查看索引信息140
使用sp_helpindex存储过程141
从系统视图sys.indexes中查询索引信息142
从系统视图sys.index_columns中查询索引信息143
从系统视图sys.sysindexkeys中查询索引的键或列信息145
创建索引146
创建带包含列的索引148
修改索引148
删除索引149
从系统视图sys.dm_db_index_usage_stats中查询索引操作的信息149
在SQLServerManagementStudio中查看统计信息151
使用系统视图sys.stats查看统计信息153
使用系统视图sys.stats_columns查看统计信息中列的信息154
使用DBCCSHOW_STATISTICS命令查看统计信息的明细信息155
使用sp_autostats存储过程查看索引自动创建的统计信息156
创建统计信息156
修改统计信息160
删除统计信息161
使用索引优化数据库查询效率162
无法使用索引的SELECT语句164
重新组织和重新生成索引165
禁用索引168
使用索引视图169

第6章数据库安全管理172
6.1基础知识173
6.1.1SQLServer登录173
6.1.2SQLServer系统用户174
6.1.3角色的概念174
6.1.4权限概述176
6.2应用情景177
创建登录名177
修改登录名180
删除登录名180
获取尝试登录的次数181
创建用户181
修改用户183
删除用户183
获取当前登录的用户名183
创建角色184
管理角色中的用户186
判断当前用户是否属于指定角色或Windows组188
修改角色188
删除角色189
创建架构190
修改架构192
删除架构193
从系统视图INFORMATION_SCHEMA.SCHEMATA中查询架构信息193
设置权限194
从系统视图sys.sysusers中查询用户信息200

第7章SQLServer硬件管理和配置202
7.1基础知识203
7.1.1Windows内存管理203
7.1.2优化Windows内存性能204
7.1.3优化SQLServer服务器的磁盘性能205
7.2应用情景206
SQLServer对大容量内存的支持206
手动配置内存选项207
监测SQLServer内存使用情况210
监测SQLServer磁盘活动情况213
获取磁盘读写情况215
获取数据库文件的I/O统计信息215
获取I/O工作情况216
使用系统监测器来监测CPU活动情况217
使用SQLServer函数查看ServerSQLCPU活动情况217
获取CPU的工作情况218
线程管理219
获取网络数据包统计信息220

第8章服务器与客户端配置221
8.1基础知识222
8.1.1SQLServer服务器与客户端结构222
8.1.2服务器配置选项223
8.2应用情景224
创建服务器组224
注册服务器225
启动、暂停和停止SQLServer227
配置服务器属性229
从系统视图sys.configurations中查询服务器配置选项信息231
使用系统存储过程sp_configure查询服务器配置选项信息232
使用系统存储过程sp_configure修改服务器配置选项233
配置网络连接233
配置客户端网络235
配置ODBC数据源236
测试客户端的配置238

第9章维护数据库239
9.1基础知识240
9.1.1备份数据库的类型和模式240
9.1.2还原数据库的类型242
9.1.3数据库快照242
9.2应用情景243
将表中数据导出到文本文件244
将表中数据导出到Access数据库249
从文本文件向SQLServer数据库中导入数据251
从Access数据库中导入数据255
分离数据库257
附加数据库259
在SQLServerManagementStudio中创建数据库备份261
使用BACKUPDATABASE语句备份整个数据库263
使用BACKUPLOG语句备份数据库中的日志264
使用存储过程sp_addumpdevice创建逻辑备份设备264
从系统视图sys.backup_devices中查询备份设备信息264
实现差异备份265
部分备份266
备份指定的文件组或文件266
仅复制数据库备份267
在SQLServerManagementStudio中还原数据库备份267
使用RESTOREDDATABASE语句还原数据库备份271
使用RESTOREDLOG语句还原数据库日志271
创建数据库快照272
查看数据库快照273
恢复到数据库快照274
删除数据库快照274
使用DBCCCHECKALLOC命令检查磁盘空间分配结构的一致性274
使用DBCCCHECKTABLE命令检查表或索引视图的完整性275
使用DBCCCHECKCATALOG命令检查数据库内系统表的一致性276
使用DBCCCHECKDB命令检数据库对象的结构和逻辑完整性276
使用DBCCSQLPERF命令监视日志空间使用情况277

第10章常用数据操纵语言（DML）278
10.1基础知识279
10.1.1本章的演示数据279
10.1.2SELECT语句的基本语法279
10.2应用情景281
最简单的SELECT语句281
指定要查询的列281
使用DISTINCT关键字282
使用TOPn[PERCENT]关键字283
改变显示的列标题284
设置查询条件285
在查询条件中使用BETWEEN关键字286
在查询条件中使用IN关键字286
实现模糊查询287
排序结果集289
对多列进行排序290
使用分组统计291
生成汇总行293
连接查询294
在连接查询中对空值的判断298
一个简单的子查询299
在子查询中使用聚合函数返回单值300
IN关键字与返回多值的子查询301
EXISTS关键字与子查询302
使用UNION关键字的合并查询303
使用CAST和CONVERT函数进行类型转换304
保存查询结果306
插入数据语句307
在插入数据时利用默认值308
修改数据语句308
修改数据时不允许设置标识列的值309
修改数据时不允许在惟一性约束列中使用相同的数据309
修改数据时不能违反检查约束310
修改数据时不能与绑定到列的规则冲突310
删除数据语句310

第二篇深入管理313

第11章使用ReportingServices设计报表315
11.1基础知识316
11.1.1安装ReportingServices316
11.1.2检测组件是否安装成功319
11.2应用情景323
创建和设计报表324
在报表中对数据进行排序332
在报表中添加分组333
向报表中添加总计335
使用简单的参数336
设置可用值的参数查询338
在报表中添加饼图341
在报表中添加条形图344

第12章事务与锁347
12.1基础知识348
12.1.1事务的概念和特性348
12.1.2事务的分类349
12.1.3事务的并发349
12.1.4锁定机制349
12.1.5锁的兼容性350
12.1.6事务中不允许使用的SQL语句351
12.1.7锁的粒度和层次结构352
12.1.8产生死锁的原因352
12.1.9尽量减少死锁354
12.2应用情景355
启动事务355
提交事务356
回滚事务356
定义保存点357
使用SETXACT_ABORT语句358
嵌套事务360
使用DBCCOPENTRAN查看当前的活动事务362
显示锁活动情况362
设置事务隔离级别选项365
设置锁超时时间371

第13章存储过程、函数和触发器372
13.1背景知识373
13.1.1什么是存储过程373
13.1.2什么是用户定义函数373
13.1.3触发器的基本概念374
13.1.4inserted和deleted表375
13.2应用情景375
创建存储过程376
执行不带参数的存储过程377
带参数的存储过程378
存储过程的返回值379
修改和重命名存储过程380
删除存储过程382
从INFORMATION_SCHEMA.ROUTINE_COLUMNS中获取存储过程信息382
系统存储过程383
创建标量函数385
创建内联表值函数386
创建多语句表值函数387
修改和重命名用户定义函数389
删除用户定义函数390
在SQLServerManagementStudio中创建触发器390
使用CREATETRIGGER语句创建触发器391
修改触发器394
使用sp_rename重命名触发器396
删除触发器396
从sys.triggers中获取触发器的信息396
使用存储过程sp_helptext查看触发器的定义文本398
使用存储过程sp_helptrigger查看触发器的属性398
禁用和启用触发器399

第14章游标401
14.1基础知识402
14.1.1什么是游标402
14.1.2游标的分类402
14.2应用情景404
声明游标404
打开游标406
读取游标数据406
关闭游标408
获取游标状态409
删除游标410
使用存储过程sp_cursor_lis获取游标属性411
使用存储过程sp_describe_cursor获取游标属性413
使用存储过程sp_describe_cursor_columns获取游标属性414
使用存储过程sp_describe_cursor_tables获取游标的基表416
修改游标结果集中的行418
删除游标结果集中的行418

第15章全文搜索420
15.1基础知识421
15.1.1全文搜索的基本概念421
15.1.2全文搜索的体系结构422
15.1.3使用FILESTREAM技术保存大对象数据423
15.2应用情景423
在SQLServerManagementStudio中创建全文目录423
使用CRAETEFULLTEXTCATALOG语句创建全文目录424
修改全文目录425
删除全文目录426
在表Employees中添加全文搜索列426
使用全文索引向导创建全文索引427
使用CRAETEFULLTEXTINDEX语句创建全文索引432
启用和禁用全文索引432
删除全文索引432
查询全文目录的使用情况433
从sys.fulltext_indexes中查询全文索引的情况433
从sys.fulltext_index_columns中查询全文索引的情况434
设置用于全文搜索的FDHOST启动器服务账户435
使用CONTAINS谓词执行全文搜索436
使用FREETEXT谓词执行全文搜索438
启用FILESTREAM440
创建启用FILESTREAM的数据库440
创建保存FILESTREAM数据的表441
使用Transact-SQL管理FILESTREAM数据442
使用VisualC#语言管理FILESTREAM数据443
在保存文件数据的列中实现全文搜索450

第三篇系统优化451

第16章使用空间数据455
16.1基础知识456
16.1.1空间数据类型456
16.1.2Point类型456
16.1.3MultiPoint类型457
16.1.4LineString类型458
16.1.5MultiLineString类型458
16.1.6Polygon类型459
16.1.7MultiPolygon类型460
16.1.8GeometryCollection类型461
16.2应用情景462
在创建表时定义geometry类型的列462
向表中插入几何数据462
从表中获取几何数据463
获取geometry实例中包含的点464
获取geometry实例的维度信息465
检查是否为空466
判断geometry实例是否简单467
返回geometry实例的边界467
检查geometry实例的闭合性468
确定两个实例是否包含相同的点集469
确定两个实例是否不相接469
确定两个实例是否相交470
确定两个实例是否接触470
确定两个实例是否重叠470
确定两个实例是否交叉471
确定一个实例是否在另外一个实例的内部471
确定一个实例是否完全包含另外一个实例471
确定两个几何图形中点之间的最短距离472

第17章常用性能监测和优化工具473
17.1基础知识474
17.1.1常用性能监测和优化工具474
17.1.2下载和安装RML分析工具474
17.2应用情景476
使用SQLServerProfiler记录数据库的跟踪数据476
筛选跟踪数据480
重播跟踪481
实用SQLServerProfiler定位和分析问题484
使用数据库引擎优化顾问分析数据库性能485
数据库引擎优化顾问中的优化选项491
数据库引擎优化顾问的应用建议492
使用dta实用工具493
ReadTrace实用工具494
OSTRESS实用工具501

第18章对SQL语句进行分析和优化503
18.1基础知识504
18.1.1影响查询语句执行性能的常见因素504
18.1.2如何定位导致查询运行慢的原因504
18.1.3使用SETSTATISTICSIO语句检查查询所产生的I/O操作505
18.1.4使用SETSTATISTICSTIME语句检查查询使用的时间和CPU使用情况506
18.1.5查看执行计划507
18.1.6准备演示数据509
18.2应用情景510
控制SELECT语句中行和列的数量510
慎用DISTINCT关键字513
慎用UNION关键字515
判断表中是否存在记录518
连接查询的优化518
使用INSERTINTO…SELECT语句优化批量插入操作520
优化修改和删除语句522

第19章数据库分区技术523
19.1基础知识524
19.1.1分区技术的分类524
19.1.2分区表525
19.1.3确定分区依据列和分区数527
19.1.4创建文件组528
19.1.5设计分区视图531
19.2应用情景532
使用向导创建分区表532
查询普通表与分区表的比较538
使用CREATEPARTITIONFUNCTION语句创建分区函数539
使用DROPPARTITIONFUNCTION语句删除分区函数541
使用CREATEPARTITIONSCHEME语句创建分区方案541
使用DROPPARTITIONSCHEME语句删除分区方案542
创建分区表542
创建分区索引543
查看分区表明细信息544
拆分和合并分区546
分区中的数据移动546
创建分区视图548
在分区视图中修改数据553
从系统表sys.data_spaces中获取数据空间信息554