《SQL Server 2016必知必会》要点记录

Bertha 。 2023-06-28 11:27 2阅读 0赞

> 因为工作中开始用到SQL Server，之前的项目大多都是用Oracle和Mysql，因此对SQL Server也不是太熟悉，正好手边有《SQL Server 2016必知必会》这本书，也就花了一些时间大致读了一遍。总结来说，这本书属于入门级，适合对SQL Server没有或者几乎没有基础的人。

## SQL Server介绍 ##

1. Sql Server的系统数据库分为：master、model、msdb和tempdb。

Master 数据库记录SQLServer系统的所有系统级别信息（表sysobjects）。他记录所有的登录账号（表sysusers）和系统配置。Master数据库是这样一个数据库，他记录所有其他的数据库（表sysdatabases），包括数据库文件的位置。Master数据库记录SQLServer的初始化信息，他始终指向一个可用的最新 Master 数据库备份。

Model 数据库是作为在系统上创建数据库的模板。当系统收到“Create DATABASE” 命令时，新创建的数据库的第一部分内容从Model 数据库复制过来，剩余部分由空页填充，所以SQL Server数据中必须有Mode数据库。

Msdb 数据库供SQLServer 代理程序调度警报和作业以及记录操作员时使用。比如，我们备份了一个数据库，会在表backupfile中插入一条记录，以记录相关的备份信息。

Tempdb 数据库保存系统运行过程中产生的临时表和存储过程。当然，它还满足其他的临时存储要求，比如保存SQL Server生成的存储表等。Tempdb数据库是一个全局咨询，任何连接到系统的用户都可以在该数据库中产生临时表和存储过程。Tempdb 数据库在每次SQL

Server启动的时候，都会清空该数据库中的内容，所以每次启动SQL Server后，该表都是干净的。临时表和存储过程在连接断开后会自动除去，而且当系统关闭后不会有任何活动连接，因此，tempdb 数据库中没有任何内容会从SQL Server的一个会话保存到另外一个会话中。

默认情况下，在 SQL Server 在运行时 tempdb 数据库会根据需要自动增长。不过，与其它数据库不同，每次启动数据库引擎时，它会重置为其初始大小。如果为 tempdb 数据库定义的大小较小，则每次重新启动 SQL Server时，将tempdb 数据库的大小自动增加到支持工作负荷所需的大小这一工作可能会成为系统处理负荷的一部分。为避免这种开销，可以使用 ALTER DATABASE 增加 tempdb 数据库的大小。

2. 常规标识符和分隔标识符

如果是不遵守标识符命名规则的标识符，在T-SQL中必须使用分隔符号(\[\]): select \* from \[my table\];

3.SQL实例就是SQL服务器引擎。在一台计算机上可以安装多个SQL Server 2016，每个SQL Server 2016可以理解成一个实例。

4. 在SQL Server 2016中最多可以指定32767个数据库。

## 数据类型 ##

1. tinyint：占一个字节的空间，存储0~255的整数。

2. bigint：8个字节，存储2^-63 ~ 2^63-1

3. Varchar:与char类型的区别是：存储的长度不是列长，而是数据的长度

4. text：存储大容量文本，理论容量是2^31-1个字节

5. ntext：与text类似，采用unicode字符集，理论容量是2^30-1个字节

6. Rowversion:每一次对数据表的更改，SQL

7. Server都会更新一个内部的序列数，这个序列数保存在Rowversion字段中。所有Rowversion列的值在数据表中是唯一的，并且每张表中只能有一个包含Rowversion字段的列存在。

8. timestamp：一张表中只能有一个timestamp字段。

9. 约束:主键约束，唯一性约束，检查约束，默认约束，外键约束。

## 视图 ##

1. 通过视图可以删除行。但是视图的数据必须来源于一个单表，即视图的select语句必须只引用单个表。

## T-SQL ##

1. T-SQL是微软设计开发的一种结构化查询语言。

2. 全局变量名由@@符号开始。用户不能建立全局变量，也不可能使用set语句去修改全局变量的值。

3. case语句格式：

> Case When <条件表达式> then <运算式>When <条件表达式> then <运算式>Else <运算式> End

## SQL数据查询 ##

1. 用with语句检查一致性：with语句用于指定临时命名的结果集，这些结果集称为公用表达式，其生命周期在该批处理语句执行后结束。

> With expression\_name \[(cokumn\_name\[,….n\]\]As (CTE\_query\_definition)
> 
> Ex:With cte\_count(班级，性别，人数)AS (select 班级，性别，人数 from XS);Select \* from cte\_count;

2. 使用into子句可以将查询结果生成放入一个新表或存放在临时表中。如果要将查询结果放入临时表，在临时表名前面加上‘\#’号。

> Select \* into \#temptableFrom XSWhere age < 30;Select \* from \#temptable;

3.where子句是在分组之前对数据进行筛选，having是对分组进行筛选

4. union中order by子句只能位于最后一个查询语句后；

5. union中列的数据类型不必完全相同，但数据类型间必须可以自动转换

6. Order by不能用于子查询，但是指定了top时则可以

7.子查询最多可以嵌套32层

8.含exists的子查询不产生任何数据，只用来判断子查询中是否有结果返回

## SQL数据操作 ##

1. Primary key默认约束是clustered，unique约束默认为non clustered。

2. 修改视图数据时一次只能对一个表中的数据进行操作，不能对表中的计算字段进行修改。

3. 视图虽然是单表或者多表的动态数据集合，但如果用户删除视图中的数据来源于单表，并且没有触发器等约束，那么删除视图中的数据也将会影响到数据表中的数据。

## 存储过程 ##

1. 存储过程分为：系统存储过程，用户存储过程和扩展存储过程。

2.存储过程工作流程

3. sp\_help和sp\_helptext可以用于查询存储过程的结构信息。

4. 使用alter修改存储过程时会将之前的过程内容进行覆盖。

## 触发器 ##

1. 当执行删除操作时，DELETE触发器被激活，用于控制用户删除的数据。当执行DELETE触发器时，被删除的数据存放在DELETED表中，操作表中的记录被删除。

2. UPDATE触发器的操作类型分为两步：一是将更新前的记录存储在DELETED表中，二是将更新后的记录存储在INSERTED表中。

3. AFTER触发器首先会建立INSERTED和DELETED表，然后执行SQL语句中的数据操作，最后才会执行触发器中的代码。而SQL SERVER 2016支持INSTEAD OF 触发器，用于INSTEAD OF 触发器则是在建立INSERTED和DELETED表后直接执行触发器。

4. 触发器功能：DDL触发器，登录触发器

5. 禁用触发器：DISABLE TRIGGER

## 索引 ##

1. 种类：聚集索引，非聚集索引，全文索引，XML索引，空间索引，唯一索引，包含列索引，索引视图，筛选索引。

2. 聚集索引：聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序排序。

3. 创建聚集索引应当先于创建非聚集索引，因为聚集索引改变了表中行的物理顺序。

4. 非聚集索引具有独立于数据行的结构，其包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针。从非聚集索引中的索引指向数据行的指针称为行定位器。

5. 每张表只能有一个聚集索引，但允许最多有249个非聚集索引。

6. 主键是聚集索引。

7. Exec sp\_helpindex <index\_name>可以查看索引信息。

8. Set showplan\_all <on|off>可以查看索引的分析。

9. 数据操作过程中会产生索引碎片。碎片整理的方法通过重新组织索引或者重新生成索引来完成。

10. 在sys.dm\_db\_index\_physical\_stats的返回值中，avg\_fragmentation\_in\_percent反应的是逻辑碎片的百分比，当该返回值小于30%时，可以使用alter index reorganize重新组织索引，如果大于30%，使用alter index rebuild重新生成索引。

11. 每个表或者索引视图只允许有一个全文索引。

## 游标 ##

1. 游标是指向 查询结果集的一个指针。

2. 游标的实质是一种从包含多条数据记录的结果集中每次提取一条的记录的机制。

## 事务 ##

1. 脏读：当一个事务读取到另一个事务未提交的更新数据时称为脏读。

2. 幻读：一个事务读取到另一个事务已提交的新插入的数据。例如A和B事务并发执行，A查询数据，B插入或者删除数据。当A查询一个结果集时B正好插入一条记录，这时A再次查询会出现以前没有或者已删除掉的记录。

3. 五种锁：更新锁，排它锁，共享锁，键范围锁，架构锁。

## 优化 ##

1. 尽量使用简单的数据类型，例如能使用整型就不用字符型。

2. 尽量避免null。

3. 避免负逻辑。例如 !=,<>或not in。