xml地图|网站地图|网站标签 [设为首页] [加入收藏]
SERVER中LIKE使用变量类型不同输出结果不一致解惑
分类:数据库

触发器可以理解为由特定事件触发的存储过程, 和存储过程、函数一样,触发器也支持CLR,目前SQL Server共支持以下几种触发器:

一同事在写脚本时,遇到一个关于LIKE里面使用不同的变量类型导致查询结果不一致的问题,因为这个问题被不同的人问过好几次,索性总结一下,免得每次都要解释一遍,直接丢一篇博客岂不是更方便!其实看似有点让人不解的现象背后实质跟数据类型的实现有关。

数据库是数据的仓库,用于存储数据,而存储数据需要媒介,现在的存储媒介,最常用的是硬盘,土豪一点的服务器使用固态硬盘(SSD),特殊用途的服务器使用内存。数据库最常用的存储文件是数据文件和日志文件,数据文件用于存储数据,由一个主数据文件(.mdf)和若干个辅助数据文件(.ndf)构成;日志文件用于存储事物日志,由.ldf文件构成。不同的文件可以存分布到不同的物理硬盘上,这样便于分散硬盘IO,提高数据的读取速度。

  1. DML触发器, 表/视图级有效,可由DML语句 (INSERT, UPDATE, DELETE) 触发;

  2. DDL 触发器,数据库级有效,可由DDL语句 (CREATE, ALTER, DROP 等) 触发;

  3. LOGON 触发器, 实例级有效,可由用户账号登录(LOGON)数据库实例时触发;

 

数据文件的组合,称作文件组(File Group),数据库不能直接设置存储数据的数据文件,而是通过文件组来指定。

 

下面我们构造这样一个类似的简单案例。如下所,

一,文件组和文件的作用

SQL Server的数据存储在文件中,文件是实际存储数据的物理实体,文件组是逻辑对象,SQL Server通过文件组来管理文件。

图片 1

 

一个数据库有一个或多个文件组,主文件组(Primary File Group)是系统自动创建的,用户可以根据需要添加文件组。每一个文件组管理一个或多个文件,其中主文件组中包含主数据文件(master data file),扩展名是.mdf,这个文件是系统默认生成的,并且在数据库中是唯一的;辅助文件的扩展名是.ndf,是用户根据需要添加的。主文件组中也可以包含辅助文件,除了主文件组之外,其他文件组只能包含辅助文件。

例如,查看示例数据库的文件组,Primary 是住文件组,勾选Default表示住文件组是默认的文件组,这意味着,如果在create table和create index中没有指定FileGroup选项,那么SQL Server将使用默认的文件组来存储数据。

图片 2

文件组是一个逻辑实体,实际上,数据存储在文件中(.mdf和.ndf)中,每一个文件组中都包含文件,因此,在create table和create index命令中指定文件组,那么数据就会存储到文件组包含的文件中。

图片 3

从上图中可以看到,数据库文件的元数据:

  • Logical Name是数据文件的逻辑名称,用于数据压缩 DBCC ShrinkFile等;
  • 数据库文件有两种类型:Rows Data(存储数据)和Log(存储日志),
  • Initial Size是文件的初始大小,
  • Autogrowth表示文件自动增加的大小,Maxsize是文件大小的最大值,
  • Path参数表示文件的路径,
  • File Name是文件的物理名称,逻辑名称和物理名可以是不同的。

用户也可以使用sys.database_filessys.filegroups 查看数据的文件和文件组的元数据。

一. DML触发器

 

二,使用文件组的优势

在实际开发数据库的过程中,通常情况下,用户需要关注文件组,而不用关心文件的物理存储,即使DBA改变文件的物理存储,用户也不会察觉到,也不会影响数据库去执行查询。除了逻辑文件和物理文件的分离之外,SQL Server使用文件组还有一个优势,那就是分散IO负载,其实现的原理是:

  • 对于单分区表,数据只能存到一个文件组中。如果把文件组内的数据文件分布在不同的物理硬盘上,那么SQL Server能同时从不同的物理硬盘上读写数据,把IO负载分散到不同的硬盘上。
  • 对于多分区表,每个分区使用一个文件组,把不同的数据子集存储在不同的磁盘上,SQL Server在读写某一个分组的数据时,能够调用不同的硬盘IO。

这两种方式,其本质上,都是使每个硬盘均摊系统负载,提高IO性能。

创建分区表时,不同的分区可以使用相同的文件组,也可以使用不同的文件组。因此,在设计文件组时,应尽量把包含的文件包含在不同的硬盘上,以实现物理IO的最大分散化。

在创建文件时,服务器CPU核的数量,决定最大的并发IO度,应该根据CPU 核的数量创建多个文件。通常情况下,文件的数量和CPU核的数量一致,是最优化的设计。

还有,应该根据硬盘的性能来创建文件组,日志文件存储到性能最好的硬盘上,而查询延迟要求高的数据,也需要存储到性能最好的硬盘上。

不是所有的数据都是同等重要的,应该根据业务需求和查询延迟,对数据分级,因此,在设计文件组时,应该把级别高的数据分散,而把那些基本用不到的数据存储到性能差的,用于存储归档数据的硬盘上,以实现服务器性能的合理配置。

1. 语句级触发器/行级触发器

CREATE TABLE TEST

(ID        INT IDENTITY(1,1),

 NAME    VARCHAR(32)

)

 

INSERT INTO dbo.test

SELECT 'abc32'

 

INSERT INTO dbo.test

SELECT 'abd32'

 

INSERT INTO dbo.test

SELECT 'abe32' 

 

 

 

 

DECLARE @name  VARCHAR(32);

SET @name='ab%';

SELECT * FROM TEST WHERE NAME LIKE @name;

 

 

DECLARE @name1 CHAR(32);

SET @name1='ab%';

SELECT * FROM dbo.TEST WHERE NAME LIKE @name1;

三,指定文件组

在创建表时,在on子句中指定文件组,那么数据将存储在该文件组包含的文件中:

CREATE TABLE [dbo].[student](
...
) ON [PRIMARY]

在创建索引时,在on子句指定文件组选项,那么该表的索引结构将存储在文件组包含的文件中:

CREATE NONCLUSTERED INDEX [idx_student_stuname] 
ON [dbo].[student]( [stuname] ASC ) 
ON [PRIMARY]

在创建分区时,在TO自居中指定文件组,每一个分区将存储到文件组中:

REATE PARTITION SCHEME scheme_name
AS PARTITION function_name 
TO ([fg_name1], <....>, [fp_nameN])

在SQL Server中,从定义来说只有语句级触发器,但如果有行级的逻辑要处理,有两个仅在触发器内有效的表 (inserted, deleted), 存放着受影响的行,可以从这两个表里取出特定的行并自行定义脚本处理;

 

四,数据文件大小增长导致的问题

当数据文件爆满,没有空间存储数据时,此时执行insert命令,这会导致数据文件的增长。如果filegrowth选项设置的过大,会导致SQL Server耗费较长时间来实现文件的增长,在数据文件增长时,该文件是不能访问的,因此,即使用户仅插入一条数据,也要等待很长时间才能完成查询,对用户来说,体验不友好。

数据文件增长是非常耗费系统资源和影响性能,如果设置SQL Server 自动增长,可能会导致系统性能不够稳定,所以,应该预测可能的空间使用需求,并提前做好规划。尽量避免空间用尽而使得SQL Server不得不自动增长的现象发生。同时也要确保每一次自动增长都能够在可接受的时间内完成,及时满足客户端应用的需求。

在ORACLE中, 对表做一次DML操作产生一次触发,叫语句级触发器,另外还可以通过指定[FOR EACH ROW]子句,对于表中受影响的每行数据均触发,叫行级触发器,原有行用:OLD表示,新行用:NEW表示;

 

五,仅使用MDF和LDF文件还原数据库

在测试服务器上,有一个DB无法访问,原因是服务器上安装的是SQL Server的评估版本,在试用期过期之后,SQL Server实例无法打开,导致DB无法访问。该数据,只有MDF和LDF文件,没有做数据库的还原和分离操作,要还原数据库必须使用特殊的方法:重建一个名字相同DB,然后替换数据库的文件。

  • Step1,新建一个同名的数据库,该DB和过期的DB有相同文件;
  • Step2,将新DB脱机,删除其MDF和LDF文件;
  • Step3,将过期DB的MDF和LDF文件复制到相应的路径下;
  • Step4,将新DB联机,查看DB的数据,及时备份和还原。

 

 

推荐文档:

文件自动增长和自动收缩sql server

 

图片 4

2. BEFORE/AFTER/INSTEAD OF

 

在SQL Server中,从定义来说只有AFTER/INSTEAD OF触发器,在表上支持AFTER触发器,在表/视图上支持INSTEAD OF触发器,对于BEFORE触发器的需求可以尝试通过INSEAD OF触发器来实现;

 

SQL Server DML Trigger

BEFORE

AFTER

INSTEAD OF

TABLE

N/A

VIEW

N/A

N/A

如上截图所示,当变量使用VARCHAR类型与CHAR类型时,两种的输出结果完全不一样。如果对SQL SERVER数据类型了解不透彻的话,估计真的对这个问题感到相当困惑。但是对SQL Server数据类型了解比较深入的人来说,这真的是一个简单到不能再简单的问题。

在ORACLE中,在表上支持BEFORE/AFTER触发器,在视图上支持INSTEAD OF触发器,比如ORACLE中无法直接对视图做DML操作,可以通过INSTEAD OF触发器来变样完成;

 

ORACLE DML Trigger

BEFORE

AFTER

INSTEAD OF

TABLE

N/A

VIEW

N/A

N/A

如下所示,我们在SQL语句中加入两句SQL,用DATALENGTH返回任何表达式的字节数,你会发现VARCHAR类型的变量返回的字节数为3,但是CHAR类型的变量的字节数为32,其实原因就在于CHAR类型是定长的,也就是当你输入的字符小于你指定的数目时,例如char(32),你输入的字符小于32时,它会在后面补空值。当你输入的字符大于指定的数时,它会截取超出的字符. 所以下面两种LIKE的逻辑意义不一样。LIKE 'ab%'  与 LIKE 'abc%       '的逻辑完全不同。

 

 

3. 触发条件

 

(1) 不能触发的情况

图片 5

对于UPDATE,DELETE操作而言,均会触发触发器;而对于INSERT或者说IMPORT的情况,是可以控制不去触发的。

 

  • 大批量导入操作,如:BULK INSERT, bcp/INSERT... SELECT * FROM OPENROWSET,都有FIRE_TRIGGERS/IGNORE_TRIGGERS选项,可以设置是否触发触发器;
  • 导入导出向导/SSIS,如果目标是表,也有FIRE_TRIGGERS的设置选项;
  • 另外truncate操作也不会触发;

 

(2) 嵌套触发器 (Nested Triggers), 循环/递归触发器 (Recursive Triggers)

其实你想从侧面印证一下也很简单,如下脚本对比所示,仔细理解一下,也许你就想明白了!

嵌套触发器,就是一次操作触发了一个触发器,然后触发器里的语句继续触发其他触发器,如果继续回头触发了自己,那么就是递归触发器。

 

对于AFTER触发器有个两个开关分别控制嵌套触发和递归触发:

 

exec sp_configure 'nested triggers'

DECLARE @name  CHAR(32);

这个参数默认值为1, 也就是说允许AFTER触发器嵌套,最多嵌套32层,设为0就是不允许AFTER触发器嵌套,如下:

SET @name='ab%';

exec sp_configure 'nested triggers',0
RECONFIGURE

SELECT * FROM TEST WHERE NAME LIKE @name;

但这个参数有两个另外:

 

  • INSTEAD OF触发器,可以嵌套,不受这个参数开关与否影响;
  • AFTER触发器,即使打开该选项,也不会自己嵌套自己(即递归),除非打开了RECURSIVE_TRIGGERS选项,也就是循环/递归触发器;

    --create table, sql server 2016 & higher drop table if exists A GO create table A(id int) GO

    --create DML trigger drop trigger if exists tri_01 GO create TRIGGER tri_01 ON A AFTER INSERT, UPDATE, DELETE as begin

    if @@NESTLEVEL = 32
    begin
        return
    end 
    insert A values(0)
    

    end GO

    --check nested triggers server option exec sp_configure 'nested triggers' --name minimum maximum config_value run_value --nested triggers 0 1 1 1

    --test with RECURSIVE_TRIGGERS off ALTER DATABASE dba set RECURSIVE_TRIGGERS off select is_recursive_triggers_on, from sys.databases GO insert A values(1) select from A --id --1 --0

    --test with RECURSIVE_TRIGGERS on ALTER DATABASE dba set RECURSIVE_TRIGGERS on select is_recursive_triggers_on, * from sys.databases GO

    truncate table A insert A values(1) select * from A --32 rows

    --如果没有加@@NESTLEVEL判断并退出,会出现32层限制的报错,并且表里不会插入任何数据 /* Msg 217, Level 16, State 1, Procedure tri_01, Line 10 Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

    select from A --0 rows/

    --删表会级联删除触发器,就像索引 drop table A

 

 

DECLARE @name1 CHAR(3);

循环/递归触发器的前提就是嵌套触发器,只有允许嵌套了才可以递归(递归也就是嵌套并触发自己),递归有直接和间接两种情况:

SET @name1='ab%';

本文由澳门新葡亰手机版发布于数据库,转载请注明出处:SERVER中LIKE使用变量类型不同输出结果不一致解惑

上一篇:开窗函数Over,数据库还原的问题 下一篇:高效查詢的範例,操作数据表语句模板
猜你喜欢
热门排行
精彩图文