SqlServer日常积累（一）

启用Ad Hoc Distributed Queries：

exec sp_configure 'show advanced options',1   

reconfigure   

exec sp_configure 'Ad Hoc Distributed Queries',1   

reconfigure       

关闭Ad Hoc Distributed Queries：

exec sp_configure 'Ad Hoc Distributed Queries',0   

reconfigure   

exec sp_configure 'show advanced options',0   

reconfigure

5. 用第二个表数据更新主表字段

方式一（适用于多字段）：

UPDATE tableA
SET name = B.name, age = B.age
FROM tableA A,tableB B WHERE A.Id = B.Id

方式二（适用于单字段）：

UPDATE tableA 
SET name = (SELECT B.name FROM tableB B
            WHERE B.Id = Id)

 

6. 添加行号列（row_number()此函数2005后数据库可用）

select row_number()over(order by userid )as RowNum,  *  from  tableName

7.动态语句中添加变量

（1）非字符串类型变量

declare 
    @Number int,@sql nvarchar(1000)
set @Number = 19;
set @sql = 'select Time, Humidity, EquipmentNum
from DataMonitoring 
where Id = '+convert(nvarchar(10),@Number);
exec(@sql);

（2）字符串类型变量

declare 
    @Char nvarchar(20),@sql nvarchar(1000)
set @Char = '00:00';
set @sql = 'select Time, Humidity, EquipmentNum
from DataMonitoring 
where Time = '''+@Char+'''';
exec(@sql);

8.选取数据插入临时表并添加自增行号

declare 
    @maxRowNum int, @sql nvarchar(1000)
select Time, Humidity, EquipmentNum, RowNum = identity(int,1,1)
into  #temp0 from DataMonitoring  where Time = '00:00'

select @maxRowNum = max(RowNum) from #temp0;

set @sql = 'select Time, Humidity, EquipmentNum, RowNum = identity(int,'+convert(nvarchar(10),@maxRowNum+1)+',1)
into ##temp0 from DataMonitoring where Time = ''01:00''';
exec(@sql);
select * from ##temp0 union select * from #temp0 order by RowNum;
drop table #temp0; drop table ##temp0;

 9. @Result = @@ROWCOUNT

返回受上一语句影响的行数。 如果行数大于 20 亿，请使用 @@ROWCOUNT_BIG。

10. 在sql存储过程中，不能使用表明变量（如：@tableName），若想使用表明变量只可使用动态Sql语句。

Create Procedure  GetList
@tablename  varchar(20)
AS
BEGIN
declare @sqlcommand varchar(max)     --sql名称串变量
        set @sqlcommand ='select * from '+@tablename  --构造这个字符串     
        exec(@sqlcommand )    --执行sql命令
END
GO