sql server

一、事务与锁

　　　　update：如果在事务中用了update了。当该表中没有设置主键，则无论update选中的记录有多少条（至少有一条）那么都会锁住整个表；如果有设置主       键，并且where子句按照主键查询，则各个线程会锁住对应的记录，不会锁整个表。所以如果要在事务中使用update请为该表设置主键。

　　1、ROWLOCK（行锁） TABLOCK（表锁）

　　　   这两个锁是一个层面的，ROWLOCK锁如果单独使用的话，不起任何作用。所有要配合别的锁使用。

　　　　ROWLOCK 字面意思就是在行上应用共享锁，而TABLOCK 则就是在表上应用共享锁。至于要应用什么锁可以这样用  with(ROWLOCK,UPDLOCK)或者with(TABLOCK,UPDLOCK)。前者是在选中的行上应用更新锁；而后者是在表上应用，无论后者影响了多少行那么都会锁住整个表。

UPDLOCK下

　　　　ROWLOCK和TABLOCK会互相阻塞。

　　　　ROWLOCK和ROWLOCK只有相同资源才会互相阻塞。

　　　　TABLOCK和和TABLOCK会互相阻塞。

　　2、HOLDLOCK（保持共享锁） 

　　　　在一个事务中select自动会加共享锁，当这个语句执行完select共享锁会被释放，当加上HOLDLOCK后则共享锁会保持到事务结束。

　　　　在共享锁下只能加共享锁和更新锁，会排斥其他锁。

　　　　该锁会阻塞UPDATE语句。不会阻塞HOLDLOCK锁。所以在用HOLDLOCK锁后要注意不要在继续的update选中的记录了，会发生死锁。

　　3、NOLOCK（不发出共享锁） 

　　　　只能select上才可以用该锁，意思是不发出共享锁。

　　4、TABLOCKX（表排他锁）

　　　　 加上该锁则锁住该表，排斥其他任何锁（NOLOCK不会排斥），直到提交或回滚。

　　5、UPDLOCK（更新锁）

　　　　会阻塞UPDLOCK锁和排它锁，不会阻塞共享锁。

赋：各锁之间的兼容性

　　

二、SQL 递归

　　1. 注意：OPTION (MAXRECURSION 2); MAXRECURSION实例中指定2，则最大只能进行2层递归。即可以设置递归的层数

DECLARE @i INT
SELECT @i=1;

with temp
as
(
select ID,PID,NAME
from WWXX.dbo.DIGUI
where ID =@i
union all
select a.ID, a.PID,a.NAME
from WWXX.dbo.DIGUI a
inner join temp t on a.[PID] = t.id
)
select * from temp
OPTION (MAXRECURSION 2); 

2. 递归获取嵌套深度及路径

with temp as (
select a.GroupId, a.GroupParent,a.GroupName ,a.UserId,a.UserName, 0 as i ,cast(GroupId as nvarchar(4000)) as [path]--记录树的嵌套深度
from #table a
where CHARINDEX(',1,',','+ a.LeaderID +',')>0 
union all
select b.GroupId,b.GroupParent,b.GroupName ,b.UserId,b.UserName, t.i+1 ,t.[path]+ '/'+ b.GroupId  --i 记录树的嵌套深度,path 树结构路径
from #table b
inner join temp t on b.GroupParent=t.GroupId
)
select distinct * from temp;

 

 

三、sql 小语句

　　1.根据某外键取不重复的某一条记录 一般跟排序用

　　

select * from (
 select ROW_NUMBER() over(partition by taid order by OpenDate) as RowNum ,a.*
   from TA_Account a
) as t1  where RowNum = 1

 　  2.定义表变量

declare    @table table(Item1 nvarchar(100))
INSERT @table EXEC('SELECT b.ItemName from  [dbo].[SplitToTable](''2052,2053,2054'','','') a  
left join Common_TypeData b on a.value=b.ItemID')

　　3.将一个表的某一列数据以分隔符串联起来

declare @id varchar(8000) 
set @id = '' 
select @id = @id + ItemName + ',' from (SELECT b.ItemName  from  [dbo].[SplitToTable]('2052,2053,2054',',') a  
left join Common_TypeData b on a.value=b.ItemID) as a 
select @id

 　　4.之前没用过的东西 for xml path('')

 4.关于临时表

　　听说在存储过程中使用临时表不会被预编译，不知真假。

　　临时表可以加索引，加完复合索引检索速度会大幅提高

CREATE INDEX IX_test  ON #table(GroupParentID,ID);
CREATE INDEX IX_test1  ON #table(ID,GroupParentID);

 三、join连接表

　　join时要缩小两个表的数据量

 

四、调试的技巧

　　1.获取扫描计数

        SET STATISTICS TIME ON
        SET STATISTICS IO ON
        SET STATISTICS PROFILE ON
　2.打印变量 
        PRINT
五、数据库发邮件


1。开启

exec sp_configure 'show advanced options',1
RECONFIGURE
exec sp_configure 'Database Mail XPs',1
RECONFIGURE With Override

　六、数据库操作XML文档　　

sp_xml_preparedocument 

OPENXML

这两个方法的使用

DECLARE @ParameterXml as VARCHAR(200)
SET @ParameterXml=' <parameters><parameter id="123058" value="85|90"></parameter><parameter id="123059" value="blue"></parameter></parameters>'
 declare @docid int  
 EXEC sp_xml_preparedocument @docid OUTPUT, @ParameterXml  

SELECT *  
 FROM  
 OPENXML(@docid, '/parameters/parameter', 1)  
 WITH  
 (  
  ParameterID int '@id',  
  ParameterValue NVarchar(512) '@value'  
 ) as a 
EXEC sp_xml_removedocument  @docid --删除xml文档

　　七、动态将表的所有字段值为null的修改为空字符串

declare m_cursor cursor scroll for
select b.name as column_name ,c.name as column_type from sysobjects a,syscolumns b,systypes c where a.id=b.id
and a.name='dbo.车型表' and a.xtype='U'
and b.xtype=c.xtype

for update
-- 打开游标
open m_cursor
declare @column_name varchar(50),@column_type varchar(50)
--填充数据
fetch next from m_cursor into @column_name,@column_type
--假如检索到了数据，才处理
while @@FETCH_STATUS=0 and @column_type='nvarchar'
begin
print @column_name
EXEC('update [dbo.车型表] set '+@column_name+'='''' where '+@column_name+' is null')
    
    --填充下一条数据
    fetch next from m_cursor into @column_name,@column_type
end
-- 关闭游标
close m_cursora
--释放游标
deallocate m_cursor