SQL Server

数据库系统，Database System，由数据库和数据库管理系统组成 

  • 数据库，DataBase ，专门管理数据资源的系统，按照数据结构来组织、存储和管理数据的仓库。数据表是最基本的数据库对象，是存储数据的逻辑单元
  • 数据库管理系统，DataBase Management System，DBMS，管理数据库，负责数据的存储、安全、一致性、并发、恢复和访问
数据模型，通常由数据结构、数据操作和完整性约束三部分组成

SQL语言

结构化查询语言，Structured Query Language，数据库查询和程序设计语言，用于管理关系数据库系统，高级的非过程化编程语言。

Transact-SQL：微软对SQL的扩展，具有SQL的主要特点，同时增加了变量、运算符、函数、流程控制和注释等语言元素。 

SQL语言分四类：数据定义语言[DDL]、数据查询语言[DQL]、数据操纵语言[DML]、数据控制语言[DCL]

DDL(Data Defination Language)
   创建和管理数据库中的对象，定义SQL模式以及数据库、表、视图和索引的创建和撤销。不需COMMIT。
   创建CREAT,  修改ALTER,  删除DROP,  删除TRUNCATE
   TRUNCATE,  RENAME

DQL(Data Query Language)
   基本结构: SELECT子句、FROM 子句、WHERE子句组成查询块。
   SELECT<字段名表>,  FROM<表或视图名>,   WHERE<查询条件>

DML(Data Manipulation Language)
   直接操作数据表中的数据，根据需要检索、插入、删除数据以及更新数据库.
   操作的单位是记录。DML需要COMMIT显式提交。
   插入INSERT,   删除DELETE,  更新UPDATE

DCL(Data Control Language)
   用于授予或取消对用户对数据库对象的访问权限，保证数据安全性。
   授权GRANT,  取消授权REVOKE,  显式限制权限集合DENY

事务控制语言 - TCL (Transaction Control Language) 

提交COMMIT，回滚ROLLBACK，设置保存点SAVEPOINT 

  • SQL>COMMIT：显式提交
  • SQL>ROLLBACK：回滚命令使数据库状态回到上次最后提交的状态
  • SQL>SET AUTOCOMMIT ON：自动提交

利用SQL命令间接完成：隐式提交

版本信息 

//查看sqlserver数据库版本
exec xp_msver
SELECT SERVERPROPERTY('servername') AS 实例名, SERVERPROPERTY('ProductVersion') AS 实例版本, 
       SERVERPROPERTY('Edition') AS 产品版本, SERVERPROPERTY('ProductLevel') AS 版本级别, @@VERSION AS 版本信息

数据类型

　1. 字符数据类型
　　a. 字符串：char、varchar、text；
　　b. Unicode字符串：nchar、nvarchar、ntext，用N标识，unicode是统一字符编码标准, 双字节对字符(英文,汉字)编码；
　　　使用Unicode数据类型，可以最大限度地消除字符转换的问题。
　2. 数字数据类型
　　a. 整数型：tinyint（1）、smallint（2）、int（4）、bigint（8）；
　　b. Decimal和numeric：固定精度和小数位数，decimal(p,s)或numeric(p,s)，0≤s≤p；
　　c. 货币类型：smallmoney（4）、money（8）；
　　d. 近似数字：float、real（4）；
　　e. bit类型：0/1序列；
　3. 日期和时间数据类型
　　time、date、smalldatetime、datetime、datetime2、datetimeoffset；
　4. 二进制数据类型
　　binary、varbinary；
　5. 其他数据类型
　　uniqueidentifier：16字节的十六进制数字组成，全局唯一，
　　sql_variant：支持各种数据类型；
　　还有xml、table等，此外还可以自定义数据类型。

[1]. DateTime与DateTime2区别：详情参见

  • Datetime格式yyyy-MM-dd HH:mm:ss.fff，赋值用GETDATE()
  • Datetime格式yyyy-MM-dd HH:mm:ss.fffffff，赋值用SYSDATETIME()，支持Datetime.minvalue，即0001/01/01

函数

内置函数详细介绍参考：行集函数、聚合函数、排名函数、标量函数  
系统函数：允许用户在不直接访问系统表的情况下获取SQL系统表的信息

  • 模块化设计
  • 执行速度快，缓存计划降低编译开销、无需重复解析和优化
  • 减少网络流量

分类

  • 标量型函数：Scalar Function，只能返回标量值；
  • 内联表值型函数：Inline table-valued Function，参数化的视图，只能返回 TABLE 类型；
  • 多声明表值型函数：Multi-Statement Table-Valued Function，标量型函数和内联表值型函数的结合；
  create function 函数名（@参数名 参数类型, [..]）
      returns 返回值类型
   as
   begin
      SQL语句;
      return 返回的对象;
   end

注：begin…end 块中的语句不能有任何副作用。

常用函数方法备忘

修改/删除：alter/drop function 函数名
四舍五入/向下取整/向上取整：round()、floor()、ceiling()

关键字

set ~ select
select支持在一个操作内同时为多个变量赋值，但是为变量赋值和数据检索不能同时进行，参考 两者的区别
cast() ~ convert()： 类型转换函数
cast(源值 as 目标类型)
convert(目标数据类型，源数据[, 格式化代号])，可以格式化日期和数值
delete ~ drop ~ truncate
delete：DML，删除数据表中的行(一行或所有行)/记录，自动隐式commit，无法回滚： delete from 表名 where 条件 
drop：DDL，显式手动commit，可以回滚，删除数据库、数据表或删除数据表的字段： drop table 表名 
Truncate：快速、无日志记录，删除数据表中的数据、不删除表，不可恢复： truncate table 表名 
从删除速度来说， drop> truncate > delete ，其他区别详细参考 delete ~ drop ~ Truncate
insert
注意区别下面2个insert语句的区别，第一种Product格式，values中必须给出相应的值，其中日期系统默认1900-01-01；第二种，values中采用default约束

insert into Product(productName,productPrice,productStorage,productDate,productClass) 
       values('电冰箱', null, 0, '', 1)
insert into Product(productName,productClass) values('电冰箱',1)　　
批量插入数据
[1]. insert into 目标表表名或列视图 select 检索语句 from 源表名
[2]. select 列列表 into 目标表表名 from 源表表名　
waitfor
定时、延时或阻止执行批处理、存储过程或事务。　　

数据库表设计问题

常用表操作格式 
[a]. 创建数据库与表 
 create database/table 数据库名/表名 
[b]. 查看表信息 
 exec sp_help 表名　　 
[c]. 添加新列、修改列名与类型 
 alter table 表名 
　 add 列名 列类型 
 exec sp_rename ‘表名.列名’, ‘新列名’ (注意必须加引号) 
 alter table 表名 
　 alter column 列名 新的列数据类型

E-R模型图
实体-联系(Entities-Relationships)模型，描述概念数据模型的方法之一，软件生命周期的设计阶段，提供实体、属性、联系的面向用户的表达方法，实体之间存在一对一、一对多、多对多的联系。
关系规范化 - 数据库完整性  ~ 三大范式：
　·  第一范式 1NF：所有属性(值)是不可分割的原子值；
　·  第二范式 2NF：所有属性数据必须依赖主键；
　·  第三范式 3NF：数据库表中不能包含已在其他表中包含的非主键信息；
关系型数据库三大完整性：
　·  实体完整性：主键约束 primary key，唯一且非空；
　·  参照完整性：引用完整性，外键约束 foreign key 等关联约束；
　·  用户自定义完整性：域完整性，字段类型等；
分区表
按照数据水平方式分区，将数据分布于数据库的多个不同的文件组中：
　- 改善大型表以及具有各种访问模式的表的可伸缩性和可管理性；
　- 对于多CPU系统，支持并行方式对表操作；
分区函数~分区方案：

create partition function 函数名(分区条件) 
　as range left/right for values() 
create partition scheme 方案名 
　as partition 函数名　　
一个分区方案只能用一个分区函数，一个分区函数可以被多个分区方案共用。
文件组　
在数据库中对文件进行分组的一种管理机制，一个文件不能是多个文件组的成员。文件组只能包含数据文件，事务日志文件不能是文件组的一部分。使用文件组可以隔离用户对文件的依赖，通过文件组间接管理文件，可以使得同一文件组内的文件分布在不同的硬盘中，能提高IO性能。具体地可参考 文件和文件组。
标识符
每一行数据必须都有一个唯一的可区分的属性作为标识符。
　· identity：本地(表内)唯一，使用方法 identity(初始种子值，增量)；
　　 select @@identity：查看新插入行数据的标识符(的序号)  
　　 select $identity from 表名：引用(显示)表的唯一标识符列  
　· uniqueidentifier：全局唯一，应用rowguidcol属性作为标识符指示新列为guid列，默认定义使用newid或newsequentialid()函数生成全局唯一值；同理，使用$rowguid引用唯一标识符列。
　　 ID uniqueidentifier default newsequentialid() rowguidcol  　

主键 PK ~ 外键 FK

主键保证全局唯一性， 外键建立和加强两个表数据之间链接的一列或多列，强制引用完整性，可以有效防止误删；
主键约束 ~ 外键约束 ~ 唯一约束
主键约束：用于实现实体完整性，每个表的主键有且只能有一个，主键列不能包含null值。声明联合主键采用第2、3种方法。创建PK约束，具体参见大话数据库或 三种方法创建主键约束
系统默认生成的主键约束名为： PK_表名_序列串号 
外键约束：用于实现参照完整性，一个表A:foreign key指向另一个表B:primary key，表B是主表，表A是从表。外键约束创建三种方法，参见大话数据库或者 三种方法创建外键约束
系统默认生成的外键约束名为： FK_表名_字段名_序列串号  
示例主/外键的三种创建方法：
1. 创建table时，直接在字段后面声明为 primary key 或者 foreign key
create table orders(
       orderID varchar(10) not null primary key,
       orderProduct varchar(30) not null,
       personID varchar(20) foreign key references persons(personID));　
2. 创建table时，全部字段声明之后，添加主键和外键的约束语句
create table orders(
       orderID varchar(10) not null,
       orderProduct varchar(30) not null,
       personID varchar(20) not null,
       constraint PK_orders primary key(orderID),
       constraint FK_orders_personID foreign key(personID) references persons(personID));　

3. 在table已创建后，为表添加主外键约束

alter table orders
       add constraint PK_orders primary key(orderID),
           　constraint FK_orders_personID foreign key(personID) references persons(personID)　

not null 约束：强制列不接受null值，具体使用参考上述代码。
default 约束：用于向列中插入默认值，default只能用于insert语句且不能与identity同时用，具体使用参考如下示例代码：
1. 创建table时，直接在字段后面声明为 default

create table Certifications(
   　certID int not null primary key identity(1001,1),
   　certName varchar(20) not null,
   　certPassword varchar(20) default('12345678'),
   　certTime varchar(30) default(getdate()) );　　

2. 注意，default约束不存在此种方法；
3. 在table已创建后，为表添加默认约束

alter table Certifications
   　add constraint DF_Certifications_certPassword default '123456' for certPassword,
       　　constraint DF_Certifications_certTime default getdate() for certTime　　

check 约束：用于限制列中的数据的范围，为多个列定义check约束采用第2、3种方法，具体方法如下：
1. 创建table时，直接在字段后面添加：check(条件表达式)
2. 创建table时，全部字段声明之后添加：constraint CHK_表名_字段名 check(条件表达式)
3. 在table已创建后，为表添加check约束

alter table 表名
　add constraint CHK_表名_字段名 check(条件表达式)　　

unique 唯一约束：用于唯一标识表中的每条记录，通过唯一性性索引强制实体完整性，unique算是对primary key的补充，但是每个表可有多个unique约束且允许null值，创建unique约束的3种方法可参考上述方法：

[1].unique
[2].constraint UNQ_表名_字段名 unique(字段名)
[3].alter table 表名
 　　　add constraint UNQ_表名_字段名 unique(字段名)　　
总结：
  • 获取table的约束信息：exec sp_helpconstraint 表名
  • 撤销上述各种约束：alter table 表名 drop constraint 主/外键约束名　
  • 关闭/开启约束检测：nocheck/check constraint 约束名/all
  • 若表中已存在数据，在添加约束之前先使用with nocheck可以禁止对已有数据的检测。
  • 级联更新/删除：on update/delete cascade

某些高级检索技术

where ... union ... group by ... having ... order by ... (limit) ...　　

分组技术  SQL Server 之几种分组技术介绍
group by：在select中作为分组条件的列名一定要是在group by子句中使用的列列表中。优先级：C > B > A 

select 作为分组条件的列名 聚合统计函数(被统计字段列) from 表名 group by 用于分组的列列表(A,B,C)

having：having 与 where 语句类似，where 是在分类之前过滤，having 是在分类之后过滤，且having条件中经常包含聚合函数。

 group by … having … order by …

rollup ~ cube：rollup显示所选列的值得某一层次结构的聚合，cube显示所选列的值得所有组合的聚合，且更加细化；两者均需要和group by一起用。
具体区别详解见：rollup ~ cube，rollup ~ cube - 2
联合查询
union：并集，用于整合2个以上的结果集，默认去重，union all不去重。但是有列类型和列数量是否对应一致的限制。　
连接查询
连接是关系型数据库模型的主要特点，通过连接运算符来实现多个表的联表查询，灵活，语句格式：

select 表名.列名[列列表...] from table_A 连接运算符 table_B [on 联表查询的匹配条件]　

注意，在连接表查询中学会使用别名。以下可参考 连接查询简例，连接关系示意图。
内连接：inner join，也即普通连接，包括等值连接、自然连接、不等连接。返回的查询结果集合仅仅是select的列列表以及符合查询条件和连接条件的行。其中，自然连接会去掉重复的属性列。　　
外连接：outer join，包括左外连接、右外连接和完全连接。返回的查询结果集合不仅包含select的列列表以及符合查询条件和连接条件的行，还包括左表(左连接)、右表(右连接)或两个连接表(完全连接)中的所有数据行。

 A left join B == B right join A;　 　
交叉连接：cross join，连接表中所有数据的笛卡尔积，结果集的数据行数 = 第一个表中符合查询条件的数据行数 * 第二个表中符合查询条件的数据行数。cross join后加条件只能用where，不能用on。　　
自连接：连接关键字的两边都是同一个表，将自身表的一个镜像当作另一个表来对待。自连接可以将需要两次查询的语句综合成一条语句一次执行成功。参考示例：自连接查询，也可参见大话数据库中关于自连接的例子。
子查询
即内部查询(inner query)，子查询就是位于select、update或delete语句中内部的查询。子查询在主查询执行之前执行一次，主查询使用子查询的结果。参考示例：子查询，各种查询总结.　

select select_list from table1 where expression operator(select select_list from table2);　

单行子查询：返回零行或一行。单行比较运算符：= ,>, >= ,< , <= ,<>。
多行子查询：返回一行或多行。多行比较运算符：IN/NOT IN，ANY/ALL，EXISTS。

  • ANY：匹配子查询得到的结果集中的任意一条数据；
  • ALL：匹配子查询得到的结果集中的全部数据；
  • EXISTS：返回bool值，只检查行的存在性，而IN检查实际值的存在性(一般情况EXISTS性能高于IN)。

索引

索引是对数据库表中一列或多列的值进行排序的一种结构，快速有效查找与键值关联的行，加快对表中记录的查找过滤或排序。索引采用 B树 结构。
　（1）快速检索读取数据 
　（2）保证数据记录的唯一性 
　（3）实现表与表之间的参照完整性，加速表和表之间的连接 
　（4）在使用order by、group by子句进行数据检索时，利用索引可以减少排序分组时间；
　（5）通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能 
缺点：
　（1）增加了数据库的存储空间；
　（2）创建索引和维护索引要耗费时间，插入和修改数据时要花费较多时间更新索引；
　（3）修改性能和检索性能是相互矛盾的；
分类：根据索引的顺序与数据表的物理顺序是否相同
· 聚集索引：索引的顺序与数据表的物理顺序相同，提高多行检索速度。一个表只能包含一个聚集索引。聚集索引的叶级是数据页，数据值的顺序总是按照升序排列。在创建任何非聚集索引之前先创建聚集索引。聚集索引的平均大小约为数据表的5%。
· 非聚集索引：索引的顺序与数据表的物理顺序不同，单行检索快。一个表最多249个非聚集索引。非聚集索引的叶级是索引页。索引页中的行标识符(或聚集键)指向数据页中的记录(或表的聚集索引，再通过聚集索引检索数据)，行标识符由文件ID、页号和行ID组成，并且是唯一的。数据堆通过使用索引分配图(IAM)页来维护。
特征：
· 唯一性索引：保证索引列中的全部数据是唯一的。只能在可以保证实体完整性的列上创建唯一性索引。
· 复合索引：一个索引创建在2个或多个列上。不能跨表建立复合列。注意列的排列顺序。复合索引可以提高查询性能，减少在一个表中所创建的索引数量。复合索引键中最多可以组合16列。
创建索引：
· 直接创建：索引创建向导或create index
基本方法，灵活易扩展、优化索引。语法：

create [unique][clustered|nonclustered] index 索引名  
　on {表|视图}(列 [asc|desc][,...n])

· 间接创建：利用约束间接创建
主键约束 - -> 唯一性聚集索引，唯一性约束 - ->唯一性非聚集索引。利用约束创建索引的优先级高于create index语句创建的索引。
维护索引

//查看索引
[1]. exec sp_helpindex 表名
[2]. select * from sysindexes [where name = "索引名"]
//修改索引
[1]. 修改索引名：exec sp_rename ‘表名.索引名’, ‘新索引名’
[2]. 重新生成索引：alter index 索引名/all on 表名
　　　　　　　　　rebuild;
重新生成索引会先删除再重建索引。可以不用rebuild，直接用set设置索引选项。
//删除索引
drop index 索引名 on 表名  //最好在删除之前，利用exists判断索引名的存在性；

统计信息：存储在Sql Server中列数据的样本，Sql Server维护某一索引关键值的分布统计信息

[1]. exec sp_updatestats
[2]. update statistics 表名 [索引名]
//其他
dbcc showcontig：显示表的数据和索引的碎片信息。
dbcc dbreindex(表名, 索引名)：重建表的一个或多个索引。
showplan_all 和 statistics io：分析索引，查询性能，更好的调整查询和索引。
set showplan_all on/off
set statistics io on/off　

[1]. 数据库索引的实现原理，索引由浅入深
[2]. 表和索引数据结构体系结构，SQL索引学习-索引结构

视图

视图是一种逻辑对象，是由基本表导出的虚拟表，不占用任何数据空间、不存储数据，仅封装预定义的查询语句，其内容由查询定义。视图是查看数据库表数据的一种方式，提供了存储预定义的查询语句作为数据库中的对象以备后用的作用，但视图不能索引。被查询的表称为基表，对视图的数据操作(增、删、改)，系统根据视图的定义去操作与视图相关联的基本表。
　（1）保证数据的逻辑独立性，数据保密 
　（2）隐藏复杂的SQL，SQL语句复用，数据简化操作逻辑，易于检索数据 
　（3）重新格式化检索出来的数据
创建视图　
  create view 视图名 [with schemabinding/encryption] as 查询语句  
　（1）对于视图和基表必须紧密结合的情况，利用with schemabinding将视图定义为索引视图；
　（2）对创建视图的SQL语句加密，利用with encryption；
维护视图
//查看视图
[1]. exec sp_helptext 视图名
[2]. select definition from sys.sql_modules where object_id=object_id(‘视图名’)
//修改视图 
alter view 视图名 as 查询语句  
//重命名视图
exec sp_rename 旧视图名, 新视图名  
//删除视图  
drop view 视图名1 [, 视图名2, …]　

游标

游标是一种只和一组数据中某一个记录进行交互的方法，是对(select)结果集的一种扩展。将面向集合的数据库管理系统和面向行的程序设计结合，主要用于交互式应用。
Transact-SQL 游标
存储过程、触发器和 T-SQL脚本，服务器端(后台)游标，仅支持单行数据提取，分为;

  • 静态游标：快照游标，在 tempdb 中创建游标；需要临时表保存结果集；
  • 动态游标：打开速度快、不需生成临时内部工作表，但连接速度慢，不支持绝对提取；
  • 只进游标：默认值，顺序提取、不支持滚动，最节省资源；
  • 键集驱动游标：键集唯一标识行，键集是打开游标时在 tempdb 中生成并内置在表 keyset 中；需要临时表保存键集；

注：客户端(前台)游标，仅支持静态游标，默认在客户机上缓存整个结果集、需维护游标位置信息。服务器(后台)游标性能更佳、更精确的定位更新，允许多个基于游标的活动语句。
使用游标的典型过程，分为：

  • 声明游标：declare 游标名称 + SQL检索语句
declare 游标名称 cursor 
    [local|global] [forward_only|scroll] [static|dynamic] ..
 for SQL(select)检索语句
  • 打开游标： open [golbal] 游标名称 | 游标变量  ，游标打开的同时检索数据并存储。
  • 提取数据
fetch [next|prior|first|last | absolute|relative] 
      from [global] 游标名称 | 游标变量
      into 结果变量[..]　　

定位修改和删除数据：前提是用  for update of 列列表； 设置可编辑的列。

update 表名 set 列名=新值[..] where current of 游标名
  delete from 表名 where current of 游标名
  • 关闭游标： close [golbal] 游标名称 | 游标变量  
  • 删除游标： deallocate [golbal] 游标名称 | 游标变量  

注：游标变量指引用了游标的变量。其他操作：

select @@CURSOR_ROWS;    // 游标中的数据行数
select @@FETCH_STATUS;   // fetch执行状态(-2,-1,0)

存储过程

存储过程(Stored Procedure)，数据库架构作用域内的重要对象，是存储在大型数据库系统中一段为了完成特定功能的可复用的代码块，是SQL语句和可选控制流语句的 预编译 集合，经过第一次编译后再次调用不必重新编译。存储过程主要用于返回数据。
.vs 函数

  • 函数不能修改数据库表数据，存储过程可以；
  • 存储过程必须 execute 执行，函数调用更灵活；

简单、安全、高性能

  • 允许标准组件式编程，可移植性、可复用；
  • 简单易用，预编译、执行速度快、效率高；
  • 改善安全机制、保证数据的安全；
  • 节约网络流量、降低网络负载；

分类

  • 系统存储过程：存储在 master 数据库中，以 "sp_"为前缀，用于从系统表中获取信息。
  • 用户自定义存储过程：T-SQL存储过程、CLR存储过程、临时存储过程。不能将CLR存储过程创建为临时存储过程。
 create proc|procedure 存储过程名
       (@parameter 参数数据类型 [,...])
 as
 begin
   < SQL语句代码块 
   return >
 end

返回值

  • 利用 return 返回一个值；
  • 利用 output 定义返回参数来返回多个值；　

维护
· 查看：
　 [1]. exec sp_helptext 存储过程名；
　 [2]. sys.sql_modules目录视图；
　 [3]. object_definition元数据函数；　
· 加密：with encryption
· 修改：直接将 create 替换为 alter；
· 删除：drop proc 存储过程名；
执行

  • 语法分析阶段
  • 解析阶段
  • 编译阶段：分析存储过程、生成存储过程执行计划。执行计划存储在过程高速缓存区（专门用于存储已经编译过的查询规划的缓冲区）。
    • 重新编译：[1].sp_recompile；[2]. 执行时在 exec 语句中选项 with recompile；
  • 执行阶段

触发器

Trigger，触发器是特殊的存储过程，由 事件 自动触发，不能显式调用，主要用于维护和加强数据的（一致/引用）完整性约束和业务规则（[1]. 约束；[2]. 触发器）。触发器可以级联嵌套。常用的 inserted 和 deleted 表是针对当前触发器的局部表，在高速缓存中存储新插入或删除的行数据的副本。可以理解为委托事件。通常触发器只与单个表关联。 
约束 vs 触发器 vs 存储过程
约束主要被用于强制数据的完整性，能提供比触发器更好的性能；触发器常用于验证业务规则或是复杂的数据验证。触发器可以实现约束的一切功能，但优先通过约束实现。

  • 错误信息管理：约束只能使用标准化的系统错误信息，触发器可以自定义错误信息；
  • 性能差异；
  • 管理维护的工作量；　

参考：约束与数据库对象规则、默认值+数据库设计中约束、触发器和存储过程；
事件 - -> 触发器 - -> 存储过程
·  DML 触发器：响应数据操作语言事件，将触发器和触发它的语句作为可在触发器内回滚的单个事务；常用、性能开销小，可以实现相关表数据的级联更改、评估数据修改前后表的状态。
   ζ  AFTER 触发器：在 IUD 操作、INSTEAD OF 触发器和约束处理之后被激发；推荐且只能在表上指定；　
   ζ  INSTEAD OF 触发器：在约束处理之前被激发(执行预处理补充约束操作)，指定执行DML触发器以代替通常的触发动作，优先级高于触发语句的操作；
注：每个表或试图针对每个 DML 触发操作 IUD，有且只能有一个相应的 INSTEAD OF 触发器，可以有多个相应的 AFTER 触发器。
   ζ  CLR 触发器：执行在托管代码中的方法；
·  DDL 触发器：响应数据定义语言事件，用于在数据库中执行管理任务；
·  登录触发器：响应 logon 事件，用于审核和控制服务器会话；
优缺点

  • 预编译、已优化，执行效率高；
  • 已封装，安全、易维护，可重复使用；
  • 占用服务器资源多；
  • 后置触发（事后诸葛亮）；

创建与维护
·  DDL

  create/alter trigger 触发器名称
        on 作用域（DDL：数据库名database/服务器名all server）
        FOR create|alter|drop|grant 等DDL触发器
   as SQL处理语句

　 删除： drop trigger 触发器名；  修改： create - -> alter  
·  DML

  create trigger 触发器名称
       on 作用域（DML：表名/视图名）
       [FOR|AFTER|INSTEAD OF] {[insert [,] update [,] delete]}
   as SQL处理语句

　 嵌套：级联触发，递归触发
　  ·  直接递归：更新T，触发Trig，Trig更新T，再次触发Trig；
　  ·  间接递归：更新T1，触发Trig1，Trig1更新T2，T2触发Trig2，Trig2更新T1；
　 参考：如何控制触发器递归；

事务 - 锁 具体参考 事务和锁 - sqh；

全文索引

全文索引是一种特殊类型的基于标记的功能性索引，用于提高在大数据文本中检索指定关键字的速度，由 全文索引引擎服务 (SQL Server FullText Search)创建和维护。全文索引创建和维护的过程称为填充：完全填充、基于时间戳的增量式填充、基于更改追踪的填充。全文索引只能在数据表上创建。
全文索引 .vs. 普通索引

  • 普通索引采用B-Tree结构，全文索引基于标记生成倒排、堆积且压缩的索引；
  • 普通索引适于字符/字段/短文本查询，全文索引是基于关键字查询的索引，针对语言词语/长文本搜索；
  • 每个表允许有若干个普通索引，全文索引只能有一个；
  • 普通索引自动更新、实时性强，全文索引需要定期维护；

全文目录 - 全文索引
存储全文索引，是创建全文索引的前提。全文目录是虚拟对象，是表示全文索引的逻辑概念。全文目录和全文索引都是为全文搜索查询服务。

  • rebuild：重新生成全文目录；
  • reorganize：优化全文目录；
create fulltext catalog 全文目录名
create fulltext index on 全文索引基于的表名[索引包含的列列表]　

原理：两步走
对文本进行分词，并为每一个出现的单词记录一个索引项以保存出现过该单词的所有记录的信息。全文索引引擎对加入到全文索引的列中的内容按字/词建立索引条目，即先定义一个词库，然后在文章中查找每个词条(term)出现的频率和位置，把这些频率位置信息按词库顺序归纳，完成对文件建立一个以词库为目录的索引。
  · 创建基于关键字查询的索引
    - 如何对文本进行分词：二元分词法、最大匹配法和统计方法
    - 建立索引的数据结构：采用倒排索引的结构
  · 在索引中搜索定位
   全文谓词：在 select 的 where/having 子句中指定
    - contains：精确。简单词、派生词、加权词、前缀词、邻近词；
    - freetext：模糊。文本拆分，分别搜索；
   行集函数：在 from 子句中指定
    - containstable：
    - freetexttable：

参考：全文索引原理介绍；全文索引原理及范例；

SQL-Server Helper

Sqlserver数据库命令执行方式样例 

//  [1]. 执行SQL，无返回值
public static bool ExecuteSqlNoResult(string sql) {
    using(SqlConnection conn = new SqlConnection()) {
        try {
            conn.ConnectionString = RVCConnectingString;
            conn.Open();
            SqlCommand command = new SqlCommand(sql, conn);
            command.ExecuteNonQuery();
            return true;
        }
        catch(Exception ex) {
            // 
            return false;
        }
    }
}

其中，SqlCommand表示要对SQL Server数据库执行的一个Transact-SQL语句或存储过程。无法继承此类。

// [2]. 执行SQL，返回结果
public static bool ExecuteSqlWithResult(string sql, out DataTable dtResult) {
    using(SqlConnection conn = new SqlConnection()) {    
        dtResult = new DataTable(); 
        try {
            conn.ConnectionString = DatabaseConnectingString;
            conn.Open();
            SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
            sda.Fill(dtResult);
            return true;
        }
        catch(Exception ex) {
            // 
            return false;
        }
    }
}

其中，SqlDataAdapter表示用于填充System.Data.DataSet和更新SQL Server数据库的一组数据命令和一个数据库连接。无法继承此类。

// [3]. 批量执行SQL，以事务方式
public static bool ExecuteSqlTrans(List<string> sqlList) {
    using(SqlConnection conn = new SqlConnection()) {
        SqlTransaction sqlTrans = null;
        try {
            conn.ConnectionString = DatabaseConnectingString;
            conn.Open();
            
            sqlTrans = conn.BeginTransaction();
            SqlCommand command = new SqlCommand();    
            command.Transaction = sqlTrans;
            command.Connection = conn;
            
            string sql = null;
            foreach(string sqlTmp in sqlList) {
                sql = sqlTmp;
                command.CommandText = sql;
                command.ExecuteNonQuery();
            }
            
            sqlTrans.Commit();//提交事务(前面执行无误的情况下)
            return true;
        }
        catch(Exception ex) {
            if(sqlTrans != null) {
                sqlTrans.RollBack();//执行出错，事务回滚
            }
            retrun false;
        }
    }
}

其中，SqlTransaction表示要在 SQL Server 数据库中处理的 Transact-SQL 事务。无法继承此类。

表|存储过程存在性 

// 判断普通表
IF NOT EXISTS( SELECT  * FROM dbo.SysObjects WHERE ID = object_id(N'TableName') AND OBJECTPROPERTY(ID, 'IsTable')=1 )
BEGIN
CREATE TABLE TableName（
    ... ...
）
END

// 判断存储过程
IF exists(select 1 from sysobjects where id=object_id('ProcName') and xtype='P')
    DROP PROC ProcName
GO

// 判断临时表
IF object_id('tempdb..#TAB_Tmp_Name') is not null 
BEGIN
    DROP table #TAB_Tmp_Name
END;
CREATE table #TAB_Tmp_Name(
  ... ...  
);

 Scheme赋权 

具体参见：用户架构分离 | MicroSoft 

实际应用

分组统计  

以工作中生产数据，按业务分组并取前5笔数据为例

-- 方法1
select * from ( 
 select ROW_NUMBER() over(PARTITION by ProductDomain order by ProductDomain desc) as num,
        ProductDomain, TelNum
 from [Business].[RVC].[TableName]
 where VerifyTime>'2019-06-26' and VerifyTime<'2019-06-27' and VerifyType='I'
)TmpTab where TmpTab.num <= 10 order by ProductDomain 

-- 方法2
SELECT T.rowNumber, T.ProductDomain, T.TelNum 
FROM (
  SELECT * ,row_number() over (partition by ProductDomain order by ProductDomain) as rowNumber 
  FROM [Business].[RVC].[TableName]
  where VerifyTime>'2019-06-26' and VerifyTime<'2019-06-27' and VerifyType='I'
) T
GROUP BY T.ProductDomain, T.TelNum, T.rowNumber
HAVING rowNumber<=10
order by T.ProductDomain, T.rowNumber

按年月分组统计：参考 

//datename
SELECT (datename(YEAR,[VerifyTime])+'-'+datename(MONTH,[VerifyTime])) as 年月, count(*) as CNT
FROM [dbo].[sqh].[TabName]
group by datename(YEAR,[VerifyTime]), datename(MONTH,[VerifyTime])
order by 年月 asc
//datepart
SELECT CONVERT(varchar(5),datepart(YEAR,[VerifyTime]))+'-'+RIGHT('0'+convert(varchar(5),datepart(MONTH,[VerifyTime])),2) as 年月, count(*) as CNT
FROM [dbo].[sqh].[TabName]
group by datepart(YEAR,[VerifyTime]), datepart(MONTH,[VerifyTime])
order by 年月 asc
//year+month函数
SELECT CONVERT(varchar(5),YEAR([VerifyTime]))+'-'+RIGHT('0'+convert(varchar(5),MONTH([VerifyTime])),2) as 年月, count(*) as CNT
FROM [dbo].[sqh].[TabName] 
group by datepart(YEAR,[VerifyTime]), datepart(MONTH,[VerifyTime])
order by 年月 asc

引用表的存储过程或视图

select distinct object_name(id) from syscomments 
where id in(select id from sysobjects where typein('V','P')) and text like’%TableName%’

相关参见：Sqlserver表引用 　

SQL Server中查询执行计划的脚本

DBCC FreeProccache //清除已缓存的执行计划
select total_elapsed_time / execution_count 平均时间,total_logical_reads/execution_count 逻辑读,   
usecounts 重用次数,SUBSTRING(d.text, (statement_start_offset/2) + 1,   
    ((CASE statement_end_offset    
    WHEN -1 THEN DATALENGTH(text)   
    ELSE statement_end_offset END    
    - statement_start_offset)/2) + 1) 语句执行 from sys.dm_exec_cached_plans a
cross apply sys.dm_exec_query_plan(a.plan_handle) c   
,sys.dm_exec_query_stats b   
cross apply sys.dm_exec_sql_text(b.sql_handle) d   
--where a.plan_handle=b.plan_handle and total_logical_reads/execution_count>4000   
ORDER BY total_elapsed_time / execution_count DESC;

去重统计

  • distinct：字段去重，必须放在select搜索字段的最前面
  • row_number() over() 函数：对结果集的输出编号。返回结果集分区内行的序列号，每个分区的第一行从 1 开始
//单字段去重
SELECT count(distinct [TelNum]) from [Business].[RVC].[FaceIndentityVerify] WHERE xxx
//多字段协同显示-单字段去重
SELECT * FROM (
	SELECT TelNum, Score, ROW_NUMBER() OVER(PARTITION BY TelNum ORDER BY Score desc) AS ROW 
	FROM [Business].[RVC].[FaceIndentityVerify] WHERE xxx) tmp
WHERE tmp.ROW=1

语法： ROW_NUMBER () OVER([PARTITION BY value_expression, ... [n]] ORDER BY column_name) ，PARTITION BY作用相似Group By分组的效果

基本参见：Distinct 和 row_number() over()

--- 　纵使山重水复，亦会柳暗花明 　　sunqh1991@163.com 　　欢迎关注，互相交流
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/wjcx-sqh/p/5929915.html