SQL之乱笔--事务、性能、系统、加密

SET XACT_ABORT ON --时,在事务中,若出现错误,系统即默认回滚事务,但只对非自定义错误有效 

SET Transaction Isolation Level Read UNCOMMITTED

--READ   COMMITTED
--指定在读取数据时控制共享锁以避免脏读，SQLSERVER默认

 --READ   UNCOMMITTED 
 --执行脏读或0级隔离锁定，这表示不发出共享锁，也不接受排它锁。类似NOLOCK

SET NOCOUNT OFF; --返回语句受影响的计数ON时不返回
---------------------------------------------------------------------------------------------------
 EXEC sp_executesql @sqlquery --执行字符串查询语句，效率比execute (@sql)好,
								--但@sqlquery字符串类型必须是N的，即NCHAR,NVARCHAR,通常使用NVARCHAR(MAX)

 BEGIN TRY
 
	BEGIN TRANSACTION --标记事务开始
	COMMIT TRANSACTION --提交事务
 
 END TRY
 BEGIN CATCH
	ROLLBACK TRAN --事务回滚
 END CATCH
 ----------------------------------------------------------------------
	EXISTS  --验证时通常使用EXISTS，比NOT的效率高IN和NOT IN 同理
	NOT　EXISTS 
	
	
----0-0-0-0-0-0-0-0---------------

CROSS APPLY --与表值函数JOIN时用到，表值函数用到的参数是与之JOIN的表表中的某个字段，如:
			--功能类似游标
DECLARE @UserID varchar(20)='tester11'
SELECT B.*
FROM ComplexShopMapping AS A
CROSS APPLY udf_AuthShopAll(A.BrandCode,'',@UserID,'') AS B
JOIN Branch AS C
ON C.BranchCode=B.BranchCode
WHERE ComplexShopChk=1

SELECT A.name FROM sys.procedures AS A ---找到父sp活查询在sp中使用的语句
JOIN sys.sql_modules AS B ON A.object_id = B.object_id
WHERE B.definition like '%ShopStatusCode in %'


--找到表的创建时间和修改时间 
SELECT * 
FROM sys.objects  
WHERE object_id = OBJECT_ID(N'[dbo].[TbArea]') AND type in (N'U') 


SELECT obj.Name ---找到都有那些sp使用了某个字段
FROM syscomments sc  
INNER JOIN sysobjects obj ON sc.Id = obj.ID  
WHERE sc.TEXT LIKE '%' + 'ShopStatusCode' + '%'  
AND TYPE = 'P'

-----------查看锁表问题------------

--死锁检测
use master
Select * from sysprocesses where blocked<>0
--找到SPID  
exec sp_lock
--根据SPID找到OBJID
select object_name(85575343)
--根据OBJID找到表名
------------------------------------------------
SET NOCOUNT ON<span style="white-space:pre">		</span>
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --允许语句查询其他地方修改但未提交的数据
SET XACT_ABORT ON --时，在事务中，若出现错误，系统即默认回滚事务，但只对非自定义错误有效
----查询数据库中所有不为空的表，并返回相应表名称、行数。 

SELECT 
    A.NAME,
    MaxRows = MAX(B.rows)
FROM sys.tables A
    INNER JOIN sys.partitions B
        ON A.object_id = B.object_id
WHERE B.rows > 0
GROUP BY A.name
ORDER BY MAX(B.rows) DESC
----------------------------------------
---性能查询
SET STATISTICS PROFILE ON;
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

--可以对非主键列建条件索引,即索引中加where条件..

create nonclustered index [索引名] on [表名]([字段名]) where [字段名] is not null 

SELECT  ROUND(250.66,1)


sp_refreshview ---刷新视图


-------查找需要刷新的视图-------
SELECT DISTINCT
        'EXEC sp_refreshview ''' + name + ''''
FROM    sys.objects AS so
        INNER JOIN sys.sql_expression_dependencies AS sed ON so.object_id = sed.referencing_id
WHERE   so.type = 'V'
        AND sed.referenced_id = OBJECT_ID('testTB') ;

--如果一个表有identity属性列，那么要插入identity属性列，
--必须要打开设置： set IDENTITY_INSERT 表 on 
set IDENTITY_INSERT STAFF_LOGIN ON 


--必须要有col1,col2,col3... 否则会报错
insert into STAFF_LOGIN(col1,col2,col3...)
values(要插入的列1,要插入的列2...)


--插入操作作完成会，恢复设置为off，也就是关闭设置
set IDENTITY_INSERT STAFF_LOGIN OFF

-------------------------------数据加密------------------------------------------------
--数据加密有很多种方式---
--1.可解密的加密


--利用对称密钥：
EncryptByKey--加密
DecryptByKey--解密
--这种方式比较适合大数据量。因为对称密钥的过程好用资源较少。
--利用非对称密钥
EncryptByAsymKey--加密
DecryptByAsymKey--解密。
--用于更高安全级别的加解密数据。因为耗用资源叫多。
--利用凭证的方式：
EncryptByCert--加密
DecryptByCert--解密。比较类似非对称密钥
--利用密码短语方式：
EncryptBypassPhrase--加密
DecryptByPassPhrase--解密。
--可以使用有意义的短语或其他数据行，当成加密、解密的关键字，比较适合一般的数据加解密。


--2.不可解密的加密
<span style="white-space:pre">	</span>Pwdencrypt --加密
<span style="white-space:pre">	</span>Pwdcompare --比较
 --原来的值永远无法找回（如：登录密码只能修改新密码）
 
 ----------------------------清除缓存--------------------------------------------------------------------
 
  --清除存储过程缓存   
  
DBCC FREEPROCCACHE    
  
--注：方便记住关键字 FREEPROCCACHE 可以拆解成 FREE(割舍，清除) PROC（存储过程关键字简写），CACHE（缓存）  
 
--清除会话缓存  
  
 DBCC FREESESSIONCACHE   
  
--注： FREE(割舍，清除) SESSION（会话） CACHE（缓存）   
 
 
--清除系统缓存  
  
 DBCC FREESYSTEMCACHE('All')  
  
--注：FREE  SYSTE MCACHE  


--清除所有缓存  
  
 DBCC DROPCLEANBUFFERS  
  
--注： DROP CLEAN BUFFERS  

----汉字的unicode从19968到40869 


--sp中适用---
 --SET @ParmDefinition = N'@P_StartDate VARCHAR(6),          
 --        @P_EndDate VARCHAR(6),      
 --        @P_CompanyCode CHAR(4),        
 --        @P_BrandCode Varchar(4) '              
 --   EXECUTE sp_executesql @sql, @ParmDefinition,          
 --       @P_StartDate = @StartDate,            
 --       @P_EndDate = @EndDate,          
 --       @P_CompanyCode=@CompanyCode,          
 --       @P_BrandCode=@BrandCode