SQL学习问题记录

1、SQLServer 打开的时候显示评估已过期

网上很多经验都是打开安装文件--维护--版本升级。

我的不行，版本升级以后还是显示评估已过期。后来直接卸载了，重新装的。

卸载的时候必须得卸载干净，我直接把那天装的所有的东西全部卸载了， 然后重新安装了一遍。

2、SQLServer中创建的报表无法修改字段信息时

工具---选项---设计器，将阻止保存要求重新创建表的更改前面的对勾去掉即可更改。

此时可以更改表中字段的主键、是否为空值等内容。字段属性有的不能更改，但是可以删除字段，然后再添加新的字段。

3、SQL Server中设置的那个ID字段，自动增长时值跳跃问题

介绍

从 SQL Server 2012 版本开始， 当SQL Server 实例重启之后，表格的自动增长列的值会发生跳跃，而具体的跳跃值的大小是根据增长列的数据类型而定的。如果数据类型是 整型(int)，那么跳跃值为 1000；如果数据类型为 长整型(bigint)，那么跳跃值为 10000。从我们的项目来看，这种跳跃问题是不能被接受的，尤其是展示在客户端的时候。这个奇怪的问题只在 SQL Server 2012 及更高的版本中存在，SQL Server 2012之前版本不存在此问题。

背景

几天前，我们QA组的同事提出： 我们表格的自增列的值莫名奇妙的跳跃了 10000。也就是说，我们之前表格自增列的最后一个值为 2200，而现在新增一条记录，自增列的值却直接变成了 12200。在我们的业务逻辑中像这样的情况是不允许展现在客户端的，因此我们要解决此难题。

代码使用

刚开始我们都很奇怪，这是怎么发生的？我们通常不会手动向自增列插入任何值（向自增列手动插入值是可以的），自增列的值是由数据库自行维护的。我们核心团队的一位成员开始研究这个问题并找到了答案。现在，我想详细讲解下这个问题，以及我同事找到的解决方案。

如何重现此bug

你需要安装SQL Server 2012 然后创建一个测试数据库。之后再创建一个带有自增列的表格：

create table MyTestTable(Id int Identity(1,1), Name varchar(255));

现在插入两条数据：

insert into MyTestTable(Name) values ('Mr.Tom');

insert into MyTestTable(Name) values ('Mr.Jackson');

查看结果：

SELECT Id, Name FROM MyTestTable;

此时结果和我们预期的一样。 现在重启你的 SQL Server Service。重启SQL服务有多种方法，我们这里通过 SQL Server 管理器来重启：

重启之后，我们向刚才的表格再插入2条数据：

insert into MyTestTable(Name) values ('Mr.Tom2');

insert into MyTestTable(Name) values ('Mr.Jackson2');

查看结果：

SELECT Id, Name FROM MyTestTable;

在你看到重启SQL Server 2012 之后的结果，它的自增列的值从1002开始了。 也就是跳跃了 1000。之前说过，如果我们自增列的数据类型是 长整型（bigint）的话，它的跳跃值就将会是 10000。

它真的是个BUG吗？

微软声明这是一个功能而并非bug, 在很多场景下是很有用处的。 但是在我们的案例中，我们并不需要这样的一个功能，因为这个自增数据是要展示给客户的，客户如果看到这样跳跃性的数据，他们会感到很奇怪。并且跳跃值是根据你重启SQL Server的次数决定的。如果此数据不向客户展示，或许还可以接受。因此此功能通常只适合在内部使用。

解决方案

如果我们对微软提供的这个 “功能” 不感兴趣，我们可以通过两种途径来关闭它。

1. 使用序列 (Sequence)

2. 为SQL Server 注册启动参数 -t272

使用序列

首先，我们需要移除表格的自增列。然后创建一个不带缓存功能的序列，根据此序列插入数值。 下面是示例代码：
CREATE SEQUENCE Id_Sequence AS INT START WITH 1 INCREMENT BY 1 MINVALUE 0 NO MAXVALUE NO CACHE insert into MyTestTable values(NEXT VALUE FOR Id_Sequence, 'Mr.Tom'); insert into MyTestTable values(NEXT VALUE FOR Id_Sequence, 'Mr.Jackson');

注册启动参数 -t272

打开SQL Server配置管理器。 选择 SQL Server 2012 实例，右键， 选择属性菜单。在弹出的窗口中找到启动参数，然后注册 -t272。 完成之后重启下图中的SQL Server(SQLSERVER2012)， 之后进行bug重现的操作，验证问题是否已解决。

额外说明

如果在你的数据库中有很多自增列的表，并且这些表都存在数值跳跃问题，那么采用第2种方案更好一些。因为它非常简单，并且作用域是服务器级别的。采用第2种解决方案将会影响此服务实例上的所有数据库。

4、SQLserver中触发器常用语句

①创建触发器

create trigger 触发器名称

on 表名

for delete,update,insert --触发条件可多选或单选

as

T-SQL语句

②删除触发器

drop trigger 触发器名称[,...]

③重命名触发器

用查询分析器重命名

exec sp_rename 原名称,新名称

sp_rename 是 SQL Server自带的一个存储过程，用于更改当前数据库中用户创建的对象名称，如表名、列表、索引名等。

exec 是 EXECUTE的缩写。功能是执行一个存储过程，或者是执行一个动态SQL

④禁用、启用触发器

禁用：alter table 表名 disable trigger 触发器名称

启用：alter table 表名 enable trigger 触发器名称

如果有多个触发器，则各个触发器名称之间用英文逗号隔开。

如果把“触发器名称”换成“ALL”，则表示禁用或启用该表的全部触发器。

⑤instead of 触发条件

INSTEAD OF

执行触发器语句，但不执行触发触发器的SQL语句，比如试图删除一条记录时，将执行触发器指定的语句，此时不再执行delete语句。例如：

create trigger f

on table1

instead of delete

as

T-SQL语句

⑥查看数据库中所有触发器

use 数据库名

go

select * from sysobjects where xtype='TR'

sysbojects 保存着数据库的对象，其中xtype为TR的记录即为触发器对象。在name一列，我们可以看到触发器名称。

⑦查看某触发器内容

use 数据库名

go

exec sp_helptext '触发器名称'

⑧查看触发器属性

use 数据库名

go

exec sp_helptrigger tb1

sp_helptrigger 有两个参数：第一个参数为表名；第二个为触发器类型，为char(6)类型，可以是INSERT、UPDATE、DELETE，如果省略则显示指定表中所有类型触发器的属性。

⑨递归、嵌套

递归分两种，间接递归和直接递归。我们举例解释如下，假如有表1、表2名称分别为T1、T2，在T1、T2上分别有触发器G1、G2。

间接递归：对T1操作从而触发G1,G1对T2操作从而触发G2,G2对T1操作从而再次触发G1....

直接递归：对T1操作从而触发G1，G1对T1操作从而再次触发G1...

嵌套触发器

类似于间接递归，间接递归必然要形成一个环，而嵌套触发器不一定要形成一个环，他可以T1->T2->T3...这样一直触发下去，最多允许嵌套32层。

设置直接递归

默认情况下是禁止直接递归的，要设置为允许有两种方法：

T-SQL：exac sp_dboption 'dbName','recursive triggers',true

EM:数据库上点右键->属性->选项。

设置间接递归、嵌套

默认情况下是允许间接递归、嵌套的，要设置为禁止有两种方法：

T-SQL：exec sp_configure 'nested triggers',0--第二个参数为1则为允许

EM:注册上点右键->属性->服务器设置。

5、解决数据库SUM查询结果为NULL的问题

sum函数求和时会对null进行过滤，不计算；

如果没有返回结果，则sum函数的返回值为null,不是0.

解决返回值为null的方法：

COALESCE函数 ：返回传入的参数第一个非null的值，如果参数都是null则返回null

例如：select coalesce(null,null,null,3);返回结果为3

或者 select coalesce(sum(credit),3) as value from course where courseno='b80171'

IFNULL函数进行查询，判断第一个参数是否为null，如果是，则返回结果为第二个参数（数值自定义）：

select ifnull(sum(credit),0) as value from course where courseno='b08171'

6、通过判断函数判断以后，在进行求和计算

select sum(CASE

WHEN cVouchType=49 THEN iAmount*-1

ELSE iAmount

END )as iAmount

from Ap_CloseBill

where year(dVouchDate)=year(getdate()) and cFlag='AR'and datepart(mm,getdate())=datepart(mm,dVouchDate)

7、取每个物料的最新单价

每月平均价

select year(SaleBillVouch.dDate) as 年份,month(SaleBillVouch.dDate) as 月份,SaleBillVouchs.cInvCode as 物料编码,

sum(SaleBillVouchs.iQuantity) as 总数量,sum(SaleBillVouchs.iMoney) as 总无税金额,

sum(SaleBillVouchs.iMoney)/nullif(sum(SaleBillVouchs.iQuantity),0) as 月平均售价

from SaleBillVouch

left join SaleBillVouchs on SaleBillVouch.SBVID=SaleBillVouchs.SBVID

where SaleBillVouch.cSTCode='01' or SaleBillVouch.cSTCode='02' or SaleBillVouch.cSTCode='05' or SaleBillVouch.cSTCode='08'

group by year(SaleBillVouch.dDate),month(SaleBillVouch.dDate),SaleBillVouchs.cInvCode

order by 年份,月份

每个物料的最新单价

select c.*,(select top 1 总无税金额/nullif(总数量,0) from 平均单价 d where d.物料编码=c.物料编码 and d.年份=c.年份 and d.月份=c.月份) 月平均单价 from

(select a.*,(select top 1 max(月份) from 平均单价 b where a.物料编码=b.物料编码 and a.年份=b.年份) 月份

from (select 物料编码,max(年份) 年份 from 平均单价 group by 物料编码) a)c

order by 物料编码

8、SQL语句数据库Id修改为自增列

第一步：

先把主键去掉，然后再把涉及这列的索引删掉在进行操作。否则会报错

alter table 表名 drop column 列名

第二步

alter table 表名 add 列名 int identity(1,1)

9、根据一个表中的字段，更新另一个表中对应的字段

update table1

set field1=table2.field1,

field2=table2.field2

from table2

where table1.id=table2.id