MS SQL高级——事务与索引

事务与索引

什么是事务？

事务的概念及要求

• 事务（TRANSACTION）是作为单个逻辑工作单元执行的一系列操作。
• 多个操作作为一个整体向系统提交，要么都执行、要么都不执行。
• 事务是一个不可分割的工逻辑单元。

银行转账过程就是一个事务

• 它需要两条UPDATE语句来完成，这两条语句是一个整体。
• 如果其中任一条出现错误，则整个转账业务也应取消，两个账户中的余额应恢复到原来的数据，从而确保转账前和转账后的余额不变。

事务的特性

事务必须具备以下四个属性：

• 原子性（Atomicity）：事务是一个完整的操作，事务的各步操作是不可分的，要么都执行，要么都不执行。
• 一致性（Consistency）：当事务完成时，数据必须处于一致状态。
• 隔离性（Isolation）：并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务。
• 永久性（Durability）：事务完成后，它对数据库的修改被永久保存。

事务分类

显式事务

• 用BEGIN TRANSACTION明确指定事务的开始。
• 最常用的事务类型。

隐性事务

• 通过设置SETIMPLICIT_TRANSACTIONS ON语句，将隐性事务模式设置为打开。
• 其后的T-SQL语句自动启动一个新事务。
• 提交或回滚一个事务后，下一个T-SQL语句又将启动一个新事务。

自动提交事务

• SQLServer的默认模式。
• 每条单独的T-SQL语句视为一个事务。

如何创建事务

使用SQL语句管理事务的基本步骤

开始事务

• BEGIN TRANSACTION

提交事务

• COMMIT TRANSACTION

回滚（撤销）事务

• ROLLBACK TRANSACTION

事务处理中的关键问题

• 对事务中的insert、update、delete语句实时跟踪。

判断某条语句执行是否出错的方法

• 使用全局变量@@ERROR。
• @@ERROR只判断当前一条T-SQL语句执行是否有错。
• 为了判断事务中所有T-SQL语句是否有错，可以对错误进行累计。

SET@errorSum=@errorSum+@@ERROR

事务的应用

应用实践：编写存储讨程，实现学员一卡通转账功能，要求用户输入转入和转出的金额和账户

 测试转账存储过程

索引

索引类型

聚集索引（Clustered）：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个

• 主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。
• 主键索引要求主键中的每个值是唯一的，并且不能为空

非聚集索引（Non-clustered)：非聚集索引指定表的逻辑顺序。

• 数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。
• 可以有多个，小于249个
• 唯一索引：唯一索引不允许两行具有相同的索引值

如何创建索引

 使用SQL Server Management Studio创建索引

使用SQL语句创建索引

 

 在Students表的StudentName列创建非聚集索引

按指定的索引查询数据

索引的优缺点

优点

• 加快访问速度。
• 加强行的唯一性。

缺点

• 带索引的表在数据库中需要更多的存储空间。
• 操纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新。

创建索引的指导原则

按照下列标准选择建立索引的列

• 频繁搜索的列。经常用作查询选择的列。
• 经常排序、分组的列。
• 经常用作联接的列（主键/外键）。

请不要使用下面的列创建索引

• 仅包含几个不同值的列。
• 表中仅包含几行。

使用索引时注意事项

• 查询时减少使用*返回全部列，不要返回不需要的列
• 索引应该尽量小，在字节数小的列上建立索引
• WHERE子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前
• 根据业务数据发生频率，定期重新生成或重新组织索引，进行碎片整理

查看索引

ADO.NET的事务处理

ADO.NET提供了事务处理功能：

ADO.NET通过 SqlTransaction对象执行事务处理

使用ADO.NET事务

编写启用事务的通用数据访问方法

 

 测试ADO.NET事务（事务回滚）

 测试ADO.NET事务（事务提交）

 

示例

示例1：转账中遇到的问题

use StudentManager
go
--从贺小张的账户减掉1000
update CardAccount set CurrentMoney=CurrentMoney-1000
           where StudentId=100001
--给马小李的账户加上1000
update CardAccount set CurrentMoney=CurrentMoney+1000
           where StudentId=100002
--查询余额
select Students.StudentId,StudentName,CurrentMoney from Students
inner join CardAccount on Students.StudentId=CardAccount.StudentId

示例2：使用事务回滚有错误的数据

use StudentManager
go
declare @errorSum int --定义变量，用于累计事务执行过程中的错误
set @errorSum=0        --初始化为0，即无错误
begin transaction
   begin                
    update CardAccount set CurrentMoney=CurrentMoney-1000
        where StudentId=100001
       set @errorSum=@errorSum+@@error --累计是否有错误                
    update CardAccount set CurrentMoney=CurrentMoney+1000
        where StudentId=100002
      set @errorSum=@errorSum+@@error --累计是否有错误
      if(@errorSum>0)
    rollback transaction
      else 
          commit transaction
 end
go
--查询余额
select Students.StudentId,StudentName,CurrentMoney from Students
inner join CardAccount on Students.StudentId=CardAccount.StudentId

示例3：启用事务的转账存储过程

use StudentManager
go
if exists(select * from sysobjects where name='usp_TransferAccounts')
drop procedure usp_TransferAccounts
go
create procedure usp_TransferAccounts
@inputAccount int,--转入账户
@outputAccount int,--转出账户
@transferMoney int --交易金额
as
    declare @errorSum int 
    set @errorSum=0      
    begin transaction
       begin                
        update CardAccount set CurrentMoney=CurrentMoney-@transferMoney
            where StudentId=@outputAccount
        set @errorSum=@errorSum+@@error         
        update CardAccount set CurrentMoney=CurrentMoney+@transferMoney
            where StudentId=@inputAccount
           set @errorSum=@errorSum+@@error 
           if(@errorSum>0)
            rollback transaction
           else 
            commit transaction
     end        
go
--测试失败的转账
exec usp_TransferAccounts 100002,100001,1000
--查询余额
select Students.StudentId,StudentName,CurrentMoney from Students
inner join CardAccount on Students.StudentId=CardAccount.StudentId
--测试成功的转账
exec usp_TransferAccounts 100002,100001,800
--查询余额
select Students.StudentId,StudentName,CurrentMoney from Students
inner join CardAccount on Students.StudentId=CardAccount.StudentId

示例4：使用T-SQL创建索引

use StudentManager
go
/*--检测是否存在该索引(索引存放在系统表sysindexes中)--*/
IF EXISTS (SELECT name FROM sysindexes
          WHERE name = 'IX_Student_StudentName')
DROP INDEX Student.IX_Student_StudentName  --删除索引
GO

/*--学生姓名列创建非聚集索引：填充因子为30％--*/
CREATE NONCLUSTERED INDEX IX_Student_StudentName
   ON Students(StudentName)
       WITH FILLFACTOR = 30
GO

示例5：使用索引查询数据

use StudentManager
go
/*----指定按索引：IX_Student_StudentName查询----*/
SELECT * FROM Students
WITH (INDEX=IX_Student_StudentName)
WHERE StudentName LIKE '刘%'

END