sql server相关

 

 

 

 

 

 sql server nolock

在sqlserver 中with(nolock)详解

 

所有Select加 With (NoLock)解决阻塞死锁

在查询语句中使用 NOLOCK 和 READPAST 

处理一个数据库死锁的异常时候，其中一个建议就是使用 NOLOCK 或者 READPAST 。有关 NOLOCK 和 READPAST的一些技术知识点： 

对于非银行等严格要求事务的行业，搜索记录中出现或者不出现某条记录，都是在可容忍范围内，所以碰到死锁，应该首先考虑，我们业务逻辑是否能容忍出现或者不出现某些记录，而不是寻求对双方都加锁条件下如何解锁的问题。 

NOLOCK 和 READPAST 都是处理查询、插入、删除等操作时候，如何应对锁住的数据记录。但是这时候一定要注意NOLOCK 和 READPAST的局限性，确认你的业务逻辑可以容忍这些记录的出现或者不出现： 


简单来说： 

NOLOCK 可能把没有提交事务的数据也显示出来． 

READPAST 会把被锁住的行不显示出来  

不使用 NOLOCK 和 READPAST ，在 Select 操作时候则有可能报错误：事务(进程 ID **)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。 



下面就来演示这个情况。 

为了演示两个事务死锁的情况，我们下面的测试都需要在SQL Server Management Studio中打开两个查询窗口。保证事务不被干扰。 



演示一 没有提交的事务，NOLOCK 和 READPAST处理的策略： 

查询窗口一请执行如下脚本： 

CREATE TABLE t1 (c1 int IDENTITY(1,1), c2 int) 
go 

BEGIN TRANSACTION 
insert t1(c2) values(1) 



在查询窗口一执行后，查询窗口二执行如下脚本： 

select count(*) from t1 WITH(NOLOCK) 
select count(*) from t1 WITH(READPAST) 



结果与分析： 

查询窗口二依次显示统计结果为： 1、0 

查询窗口一的命令没有提交事务，所以 READPAST 不会计算没有提交事务的这一条记录，这一条被锁住了，READPAST 看不到；而NOLOCK则可以看到被锁住的这一条记录。 



如果这时候我们在查询窗口二中执行： 

select count(*) from t1 就会看到这个执行很久不能执行完毕，因为这个查询遇到了一个死锁。 



清除掉这个测试环境，需要在查询窗口一中再执行如下语句： 

ROLLBACK TRANSACTION 
drop table t1 



演示二：对被锁住的记录，NOLOCK 和 READPAST处理的策略 



这个演示同样需要两个查询窗口。 

请在查询窗口一中执行如下语句： 

CREATE TABLE t2 (UserID int , NickName nvarchar(50)) 
go 
insert t2(UserID,NickName) values(1,'郭红俊') 
insert t2(UserID,NickName) values(2,'蝈蝈俊') 
go 

BEGIN TRANSACTION 
update t2 set NickName = '蝈蝈俊.net' where UserID = 2 



请在查询窗口二中执行如下脚本： 

select * from t2 WITH(NOLOCK) where UserID = 2 
select * from t2 WITH(READPAST) where UserID = 2 



结果与分析： 

查询窗口二中， NOLOCK 对应的查询结果中我们看到了修改后的记录，READPAST对应的查询结果中我们没有看到任何一条记录。 这种情况下就可能发生脏读

 

 

---

WITH (NOLOCK)

 

缺点:

　　1.会产生脏读

　　2.只适用与select查询语句

优点:

　　1.有些文件说，加了WITH (NOLOCK)的SQL查询效率可以增加33%。

　　2.可以用于inner join 语句

脏读: 一个用户对一个资源做了修改，此时另外一个用户正好读取了这条被修改的记录，然后，第一个用户放弃修改，数据回到修改之前，这两个不同的结果就是脏读。

详细内容:

　　要提升SQL的查询效能，一般来说大家会以建立索引(index)为第一考虑。其实除了index的建立之外，当我们在下SQL Command时，在语法中加一段WITH (NOLOCK)可以改善在线大量查询的环境中数据集被LOCK的现象藉此改善查询的效能。

不过有一点千万要注意的就是，WITH (NOLOCK)的SQL SELECT有可能会造成Dirty Read(脏读)。

例如：

SELECT COUNT(UserID) 
FROM EMPLOYEE WITH (NOLOCK) 
JOIN WORKING_GROUP WITH (NOLOCK) 
ON EMPLOYEE.UserID = WORKING_GROUP.UserID

除了简单的SELECT之外，有JOIN的SELECT语法也是可以使用的。但是DELETE、INSERT、UPDATE这些需要transaction的指令就不行了…

有些文件说，加了WITH (NOLOCK)的SQL查询效率可以增加33%。
加了WITH (NOLOCK)即告诉SQL Server，我们的这段SELECT指令无需去考虑目前table的transaction lock状态，因此效能上会有明显的提升，而且数据库系统的Lock现象会有明显的减少(包含Dead Lock)。

有 一点要特别注意，因为WITH (NOLOCK)不考虑目前table的transaction lock，因此当有某些资料正处于多个phase交易(例如跨多个table的transaction交易-->如提款系统)，WITH (NOLOCK)会让目前处理交易process的数据被忽略…

讲白话一点，也就是说当使用NoLock时，它允许阅读那些已经修改但是还没有交易完成的数据。因此如果有需要考虑transaction事务数据的实时完整性时，使用WITH (NOLOCK)就要好好考虑一下。

如果不需考虑transaction，WITH (NOLOCK)或许是个好用的参考。

注1：WITH ( < table_hint > )
指定由查询优化器使用的表扫描、一或多个索引，
或由查询优化器利用此数据表以及为此语句使用锁定模式。

注2：WITH (NOLOCK)相当于READ UNCOMMITTED

 

 

主键自增列重新设置种子值

CHECKIDENT ([tabelcheckindent], RESEED, 51)

DBCC CHECKIDENT ('table_name', RESEED,new_reseed_value)

SQLServer中GO的注意点

如果只是执行一条语句，有没有GO都一样 

如果多条语句之间用GO分隔开就不一样了

每个被GO分隔的语句都是一个单独的事务，一个语句执行失败不会影响其它语句执行。

例如：

首先同时执行下边的语句

select * from sysobjects where id=a

select getdate()

 

你会发现会报错，并且不会显示任何结果集

而你再执行

select * from sysobjects where id=a

go

select getdate()

go

你会发现尽管同样会报错，但结果集中包含select getdate()的结果。

 

SQL Server中row_number的用法

ROW_NUMBER()函数将针对SELECT语句返回的每一行，从1开始编号，赋予其连续的编号。在查询时应用了一个排序标准后，只有通过编号才能够保证其顺序是一致的，当使用ROW_NUMBER函数时，也需要专门一列用于预先排序以便于进行编号。

ROW_NUMBER()

说明：返回结果集分区内行的序列号，每个分区的第一行从1开始。
语法：ROW_NUMBER () OVER  ([ <partition_by_clause> ] <order_by_clause>) 。
备注：ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。
参数：<partition_by_clause> ：将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。
      <order_by_clause>：确定将 ROW_NUMBER 值分配给分区中的行的顺序。
返回类型：bigint 。

ROW_NUMBER()常用的几种情况

1.使用row_number（）函数进行编号，如

select email,customerID, ROW_NUMBER() over(order by psd) as rows from QT_Customer

原理：先按psd进行排序，排序完后，给每条数据进行编号。

 

2.在订单中按价格的升序进行排序，并给每条记录进行排序代码如下：

select DID,customerID,totalPrice,ROW_NUMBER() over(order by totalPrice) as rows from OP_Order

 

3.统计出每一个各户的所有订单并按每一个客户下的订单的金额 升序排序，同时给每一个客户的订单进行编号。这样就知道每个客户下几单了。

如图：

代码如下：

select ROW_NUMBER() over(partition by customerID  order by totalPrice) as rows,customerID,totalPrice, DID from OP_Order

 

4.统计每一个客户最近下的订单是第几次下的订单。 

 

 

删除日志：

收缩日志文件大小(单位是M)

DBCC SHRINKFILE (N'RYRecordDB_Log' , 11, TRUNCATEONLY)

 

https://www.cnblogs.com/ljhdo/p/5748922.html

 

sql DEFAULT 和 NULL 不允许作为显式标识值

SQL  INSERT INTO错误： DEFAULT 和 NULL 不允许作为显式标识值

错误原因：插入值时自增列插入了值，我的表主键设置为自增列，当插入数据时不允许将数值插入自增列。

如下：

insert into table(id,name,address) ---错误：id为自增列

values(1,'lipang','shandong')

改为：

insert into table(name,address) ---正确

values('lipang','shandong')

select @@identify as id  ----返回当前插入数据的标识（或主键）

sql server: identity

列名  数据类型  约束  identity(m,n)

m表示的是初始值，n表示的是每次自动增加的值

如果m和n的值都没有指定，默认为（1,1）

 

 

sql server 2012导出结构和数据

 

数据库名-->右键 任务-->生存脚本

 

在2012版本中将要编写的脚本数据类型改为架构和数据，就可以把表结构和表数据一起导出了

 

[MSSQL]帐户当前被锁定,所以用户 sa 登录失败。系统管理员无法将该帐户解锁 解决方法

帐户当前被锁定,所以用户 ‘sa’ 登录失败。系统管理员无法将该帐户解锁’解决方法 
如果短时间内不停连接，就会被SQL SERVER误认为是这是攻击，会将此账号锁定。 
要用windows身份验证登录，在查询分析器里输入：

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = 'password' unlock, check_policy = off,
check_expiration = off ;
GO

• 1
• 2
• 3
• 4
• 5

其中password中修改成sa的登陆密码。 

 

 

sqlserver

将某种数据类型的表达式显式转换为另一种数据类型。CAST 和 CONVERT 提供相似的功能。

语法

使用 CAST：

CAST ( expression AS data_type )

使用 CONVERT：

CONVERT (data_type[(length)], expression [, style])

参数

expression

是任何有效的 Microsoft® SQL Server™ 表达式。有关更多信息，请参见表达式。

data_type

目标系统所提供的数据类型，包括 bigint 和 sql_variant。不能使用用户定义的数据类型。有关可用的数据类型的更多信息，请参见数据类型。

length

nchar、nvarchar、char、varchar、binary 或 varbinary 数据类型的可选参数。

 

 

sqlserver 作业

首先SqlServer的作业(job)是定期执行某个语句或者存储过程的任务,类似于windows里面的执行计划。 
作业是在SqlServer代理里面,如下面的截图: 

 

作业是依赖于sql server agent（代理的），一定要开启这个。

 以使用企业管理器创建作业，但此处讲的是用SQL语句。 创建作业

1. 执行 sp_add_job 创建作业。
2. 执行 sp_add_jobstep 创建一个或多个作业步骤。
3. 执行 sp_add_jobschedule 创建作业调度。

 说明  建议在执行完 sp_add_jobstep 后再执行 sp_add_jobserver，以便最高效率地将作业更改传送给所有涉及的服务器。

因为本地 SQL Server 代理缓存本地作业，所以任何修改都会隐性地强制 SQL Server 代理重新缓存该作业。因为直到调用 sp_add_jobserver 时，SQL Server 代理才缓存作业，所以最后调用 sp_add_jobserver 更为高效。

 

sql server if end :

1、IF ELSE

不能用在SELECT中，只能是块，比如:

IF ...

　　BEGIN

　　　　...

　　END

ELSE （注意这里没有ELSE IF，要实现只能在下面的块中用IF判断）

　　BEGIN

　　　　...

　　END

 

 

下表列出 SET 与 SELECT 的区别。请特别注意红色部分。

	set	select
同时对多个变量同时赋值	不支持	支持
表达式返回多个值时	出错	将返回的最后一个值赋给变量
表达式未返回值	变量被赋null值	变量保持原值

 

 

SELECT @Index=COUNT(id) FROM #tmpGold

 

SELECT INTO……

这种方式的语句可以在Table2不存在的时候进行表数据复制，编译器会根据Table1的表结构自动创建Table2，Table2和Table1的结构基本上是一致的，但是如果已经存在Table2，则编译器会报错.

这种方式的语句在Oracle中和MS SqlServer中是有点差别的，，如下：

语句格式：

　　Oracle:Create Table2 as Select column1,column2……From Table1 或 Create Table2 as Select * From Table1

　　MS SqlServer:Select column1,column2…… into Table2 From Table1 或 Select * into Table2 From Table1

例：

 

 

insert into 表

. 使用insert into，需要先手动创建临时表

1.1 保存从select语句中返回的结果集

create table test_getdate(c1 datetime)

insert into test_getdate select GETDATE()

select * from test_getdate

 

 

SQL Server：SELECT INTO 和 INSERT INTO SELECT

本文来自：http://www.cnblogs.com/NaughtyBoy/p/3153082.html

做数据库开发的过程中难免会遇到有表数据备份的，而SELECT INTO……和INSERT INTO SELECT…… 这两种语句就是用来进行表数据复制，下面简单的介绍下：

1、INSERT INTO SELECT

语句格式：Insert Into Table2(column1,column2……) Select value1,value2,value3,value4 From Table1 或

　　　　   Insert Into Table2 Select * From Table1

说明：这种方式的表复制必须要求Table2是事先创建好的

例：

--1.创建表
create TABLE Table1
(
    a varchar(10),
    b varchar(10),
    c varchar(10)
) ;

create TABLE Table2
(
    a varchar(10),
    c varchar(10),
    d varchar(10)
);
commit;
--2.创建测试数据
Insert into Table1 values('赵','asds','90');
Insert into Table1 values('钱','asds','100');
Insert into Table1 values('孙','asds','80');
Insert into Table1 values('李','asds',null);
commit;
--3.复制table1数据到table2中
Insert into Table2(a, c, d) select a,b,c from Table1;
commit;
--或，此种方式必须要求table2和table1的列数相等，而且类型兼容
Insert into Table2 select * from table1;
commit;

 以上这些sql在oracle和MS SqlServer中的语法是一样的，可以通用.

2、SELECT INTO……

这种方式的语句可以在Table2不存在的时候进行表数据复制，编译器会根据Table1的表结构自动创建Table2，Table2和Table1的结构基本上是一致的，但是如果已经存在Table2，则编译器会报错.

这种方式的语句在Oracle中和MS SqlServer中是有点差别的，，如下：

语句格式：

　　Oracle:Create Table2 as Select column1,column2……From Table1 或 Create Table2 as Select * From Table1

　　MS SqlServer:Select column1,column2…… into Table2 From Table1 或 Select * into Table2 From Table1

例：

--Oracle
--1.创建表
create TABLE Table1
(
    a varchar(10),
    b varchar(10),
    c varchar(10)
) ;

commit;
--2.创建测试数据
Insert into Table1 values('赵','asds','90');
Insert into Table1 values('钱','asds','100');
Insert into Table1 values('孙','asds','80');
Insert into Table1 values('李','asds',null);
commit;
--3.复制table1数据到table2中
Create Table Table2 as select a,b,c From table1;
Commit;
--或（这两种方式的sql只能应用一次）
Create table table2 as select * From Table1;
Commit;
--删除表
drop table table1;
drop table table2;
commit;

--MS SqlServer
--1.创建表
create TABLE Table1
(
    a varchar(10),
    b varchar(10),
    c varchar(10)
) ;

commit;
--2.创建测试数据
Insert into Table1 values('赵','asds','90');
Insert into Table1 values('钱','asds','100');
Insert into Table1 values('孙','asds','80');
Insert into Table1 values('李','asds',null);
commit;
--3.复制table1数据到table2中
Select a,b,c into Table2 From table1;
Commit;
--或（这两种方式的sql只能应用一次）
Select * into table2 From Table1;
Commit;
--删除表
drop table table1;
drop table table2;
commit

 

 

变量的声明:
声明变量时必须在变量前加@符号 
DECLARE @I INT

变量的赋值:
变量赋值时变量前必须加set
SET @I = 30

 

isnull:

 SQL Serve中的isnull()函数：

         isnull(value1,value2)

        1、value1与value2的数据类型必须一致。

        2、如果value1的值不为null，结果返回value1。

        3、如果value1为null，结果返回vaule2的值。vaule2是你设定的值。

 

       如果在select中就是isnull可以判断是否是null，如果是给个默认值，isnull("字段名"，"设定默认的数据")
      

is NULL 

 Address IS NOT NULL

-- 查询用户
SELECT @UserID=UserID,@InsurePass=InsurePass, @LogonPass=LogonPass, @Nullity=Nullity, @StunDown=StunDown
FROM AccountsInfo(NOLOCK) WHERE UserID=@dwUserID

-- 查询用户
IF @UserID IS NULL
BEGIN
SET @strErrorDescribe=N'您的帐号不存在或者密码输入有误，请查证后再次尝试！'
RETURN 1
END

is null用来设置默认值

IF @ScoreAmount IS NULL SET @ScoreAmount=0

 

不要把数值跟null比较，这样也会为null.

 

执行存储过程:

USE [RYAccountsDB]
GO

DECLARE @return_value int,
@strErrorDescribe nvarchar(127)

EXEC @return_value = [dbo].[GSP_GR_TASKLOAD]
@dwUserID = xxxx,
@strErrorDescribe = @strErrorDescribe OUTPUT

SELECT @strErrorDescribe as N'@strErrorDescribe'

SELECT 'Return Value' = @return_value

GO

 

 

 当 IDENTITY_INSERT 设置为 OFF 时，不能为表 'xxx' 中的标识列插入显式值。

set identity_insert 表名  ON  -- 

 允许将显式值插入表的标识列中



Sql server事务

ALTER PROCEDURE [dbo].[Proc_Test_commit1] 
    @result int output, --成功 1; 失败 0
    @message nvarchar(20) output
AS
BEGIN
    SET NOCOUNT ON
     
begin try 
      begin tran yy 
        insert into Test_Commit (b) values (3)
 
        insert into Test_Commit (a,b) values (1,3)
        set @result=1
      commit tran yy            
 end try   
begin catch
        set @message= ERROR_MESSAGE() 
        set @result=0
        rollback tran yy        
        --insert into SYS_Proc_Message([Message]) values(ERROR_MESSAGE())
        print ERROR_MESSAGE()
end catch 
     
    SET NOCOUNT OFF;  
     
     
END

 

1. begin try
2.  
   --SQL
3.  
   end try
4.  
   begin catch
5.  
   --sql (处理出错动作)
6.  
   end catch

Sql server begin try end try ,begin catch,endcatch 异常捕获机制:

我们将可能会出错的sql 写在begin try...end try 之间，若出错，刚程序就跳到紧接着的begin try...end try 的beign catch...end catch中，执行beign catch...end catch错误处理SQL。try..catch 是可以嵌套的。在begin catch ...end catch中我们可以利用系统提供的下面四个函数得到出错信息：
error_number 返回错误代码
error_serverity 返回错误的严重级别
error_state 返回错误状态代码
error_message 返回完整的错误信息
上面的四个函数在同一个begin catch ...end catch可以在多次使用，值是不变的。

下面是一个简单的小例子。

 

1. begin try
2.  
   --SQL
3.  
   end try
4.  
   begin catch
5.  
   --sql (处理出错动作)
6.  
   end catch

结果：

 

-----
error_number error_message error_state error_severity
8134 遇到以零作除数错误。 1 16
-------------------------------------------------------
不受 TRY…CATCH 构造影响的错误 
TRY…CATCH 构造在下列情况下不捕获错误： 
严重级别为 10 或更低的警告或信息性消息。 
严重级别为 20 或更高且终止会话的 SQL Server 数据库引擎任务处理的错误。 如果所发生错误的严重级别为 20 或更高，而数据库连接未中断，则 TRY…CATCH 将处理该错误。 
需要关注的消息，如客户端中断请求或客户端连接中断。

sqlserver开启事务并打印错误信息

 BEGIN  
        SET NOCOUNT ON;
        BEGIN TRY
            BEGIN TRANSACTION train;
            
            COMMIT TRANSACTION train;
        END TRY
        BEGIN CATCH    ;
            PRINT ERROR_MESSAGE()        
            ROLLBACK TRANSACTION train;            
        END CATCH;
    END;
---------------------  

 

在SQL Server 2018 Management Studio中修改表字段顺序

 

有时我们可能需要为一个已存在的数据库表添加字段，并且想让这个字段默认排的靠前一些，这时就需要为表字段重新进行排序，默认情况下在Management Studio中调整顺序并保存时会提示“不允许保存更改。您所做的更改要求删除并重新创建以下表。您对无法重新创建的标进行了更改或者启用了“阻止保存要求重新创建表的更改”选项”，这是因为有些对表的修改要求重新创建表，而 SQL Server 默认阻止这样的修改，所以保存不下来。 

可以通过取消选项“阻止保存要求重新创建表的更改”即可：

 

在 Microsoft SQL Server Management Studio 中：
1. 工具->选项->Designers->表设计器和数据库设计器->将“阻止保存要求重新创建表的更改”的选项的勾去掉。
2. 右键单击你要更改的数据表，点选“设计”，然后在表设计器中用鼠标拖动各列的位置，最后保存即可。

 

 

SCOPE_IDENTITY的用法

SCOPE_IDENTITY   和   @@IDENTITY   的作用都是取得返回在当前会话中的任何表内所生成的最后一个标识值，简单的说就是在执行一条插入语句之后使用@@IDENTITY的全局变量，取得插入记录的ID号但是有个问题就是，@@IDENTITY是全局的，所以在他的功能会体现在所有作用域，一个操作，一个触发器，一个存储过程叫做一个作用域，这时候如果出现多个作用域的情况的时候，@@IDENTITY所取得的ID号就是最后一个作用域产生的结果。这时候我们要使用SCOPE_IDENTITY方法来作了。SCOPE_IDENTITY   只返回插入到当前作用域中的值；@@IDENTITY   不受限于特定的作用域。   
  使用方法：select   SCOPE_IDENTITY()   as   ID   from   [table]select   @@IDENTITY   as   ID   from   [table]   
  实例：   
        sql="SET   NOCOUNT   ON;insert   into   [Table](Item)   values('"&Item&"')"sql=sql&";select   @@IDENTITY   as   ID   from   [Table];SET   NOCOUNT   OFF;"   

在插入某些自增表的时候，需要将其打开：
 语法：SET   IDENTITY_INSERT   TableName   ON 

存储过程：

CREATE PROCEDURE [dbo].[sp_User](@F_Name int) AS
begin tran Insertinto_T_User
Insert into  dbo.T_User(F_Name) values(@F_Name)
Select SCOPE_IDENTITY()
-- 

 

变量声明带默认值：

 

变量的声明:
声明变量时必须在变量前加@符号 
DECLARE @I INT

变量的赋值:
变量赋值时变量前必须加set
SET @I = 30

声明多个变量:
DECLARE @s varchar(10),@a INT

 

还可以这样：

  DECLARE @xxx DECIMAL(18,2)=0,@yyy DECIMAL(18,2)=0

这样声明就不用 if xx isnull 判断了。

 

 SQL SERVER中正确的下划线总是出现红色波浪线的办法：

新加了表，但是写存储过程总是提示红色：

 Microsoft sql server management studio 里点击“编辑”——“IntelliSense”——“刷新本地缓存” 就会发现红色波浪线没了（前提是你的代码没错）。

 

原因解释：这个表的字段列表，让你选择。但是，当你新建了一个对象的时候， 例如表， 或者你的那个例子，是新建存储过程abc这个时候，数据库那里，已经有存储过程abc 了。但是客户端的缓存里面， 并没有 存储过程 abc 的信息。 因为内存里面的信息，没有更新。因此，在客户端那里。输入EXEC abc，abc下有红线。将 客户端关闭后，重新打开， 由于客户端 重新加载了 数据库的基础信息。知道了 当前数据库里面，有 一个名字叫 abc 的存储过程因此，就不出红线了。

 

 

SQl SERVER复制表的时候要注意，也会复制该表的trigger触发器，如果不需要移除。

 

 

SQL Server 中输出字符串并换行的方法

在SQL Server中想输出字符串并换行的方法，示例如下：

print '中国'+char(13)+char(10)+'人民'

declare   @a   varchar(20)
set   @a= 'aaa '+char(13)+char(10)+ 'bbbb '
print   @a
---------------------  

首先在字符串的前后加单引号；

字符串中的变量以'''+@para+'''在字符串中表示；

若在执行时存在类型转换错误，则应用相应的类型转换函数，对变量进行类型转换(如cast()函数)。

如：

在将 varchar 值 '@xxx' 转换成数据类型 int 时失败。

 

print abc

这个abc必须是一个字符串类型的变量，不能是其他类型。

所以必须要转化：

Set @s='Select * From dbo.Categories where CategoryID='''+cast(@TestID as varchar)+''''
print @s

用 cast(@TestID as varchar) 的形式进行转化。

 

 set  @def=  CAST(@abc as int )  

 

Sql Server RowCount

rowcount的作用就是用来限定后面的sql在返回指定的行数之后便停止处理，比如下面的示例,

set rowcount 10
select * from 表A

这样的查询只会返回表A中的前10条数据。它和 "select top 10 * from 表A" 的作用一样。注意一点，set rowcount 的设置会在整个会话中有效。比如下面的sql示例：

set rowcount 10
select * from 表A
go
select * from 表B

表A和表B都只会返回前10条数据。
要取消set rowcount的限定,只要设置 set rowcount 0 就可以了。

从上面的示例来看，好像rowcount没有多大的用处，限制查询结果的数据，我们使用top就可以了，而且还不用担心如果忘记取消rowcount的设置而对后面的sql的影响。 但在下面的情况下，rowcount的设置就会给我们带来很大的方便哦。

我们都知道select top 后面不能加参数，只能使用一个具体的int类型的数字。如果我们想实现top后面跟参数的功能，就只有构造sql字符串，然后使用exec来执行了。比如：

declare @n int
declare @sql nvarchar(1000)
set @n=10
set @sql='select top '+cast(@n as varchar(10))+' * from 表A'
exec(@sql)

先不说上面语句中exec的性能，单从sql的可读性上来看就很不友好。但如果我们使用rowcount来解决，就显的很优雅了，因为set rowcount后面是可以使用参数的。示例如下:

declare @n int
set @n=10
set rowcount @n
select * from 表A

注意：set rowcount的限定对修改，删除一样有效。比如下面的示例：

set rowcount 10
update 表a set qty=10 where id<100

这样，上面语句最多只会修改表a中id<100的前10条数据(假设id<100的数据数量大于10)

删除也是一样

set rowcount 10
delete from 表a

这样，上面的语句最多只会删除表a中前10条数据。

 

 

SQL server 的exist：https://docs.microsoft.com/en-us/sql/t-sql/language-elements/exists-transact-sql?view=sql-server-2017
--声明变量
WHILE EXISTS(SELECT UserID FROM @tempAllUser)
BEGIN
-- 也可以使用top 1
SET ROWCOUNT 1

 

Exist:

Specifies a subquery to test for the existence of rows.

 EXISTS ( subquery )

 

 

执行带有参数的存储过程：

执行带有OUTPUT的参数存储过程，必须这样调用: (参数后面必须带有OUTPUT参数）

DECLARE @testvalue int 

EXEC sp_test 1,@testvalue output

SELECT @testvalue

https://www.cnblogs.com/zhangchenliang/archive/2013/04/07/3006433.html

 

 

SQL SERVER存储过程循环某个表

第一种方法是使用游标：

AS
declare @result VARCHAR(30)--用来处理结果的变量
begin
--声明一个游标
Declare curStudentFee Cursor for
SELECT NAME FROM SYSOBJECTS WHERE XTYPE='P';---查询语句（查询所有用户存储过程名称）

--打开游标
Open curStudentFee
--循环并提取记录
Fetch Next From curStudentFee Into @result--取第一条记录存入@result中
While ( @@Fetch_Status=0 )
begin
print ''''+@result+''''+',';---处理结果
Fetch Next From curStudentFee into @result----下一条
end
--关闭游标
Close curStudentFee
--释放游标
Deallocate curStudentFee
end

exec findName

第二种使用临时表;

/*------------------------编辑区域开始----------------------------------------*/

SET @CountBegin = 1 ----生成账号的开始数
SET @CountEnd = 5000 /*8500*/ ----生成账号的结束数

/*------------------------编辑区域结束----------------------------------------*/
DECLARE @dwUserID INT

DECLARE @tCanStudent TABLE
(
UserID int
,FlagID TINYINT
)

insert @tCanStudent select UserID,0 from AccountsInfo WHERE IsAndroid=1 and userid>=11643

WHILE(@CountBegin <= @CountEnd)
BEGIN
SELECT @dwUserID=0
SELECT TOP 1 @dwUserID=UserID FROM @tCanStudent WHERE flagID=0

IF @@error=0
BEGIN
UPDATE @tCanStudent SET flagID=1 WHERE UserID = @dwUserID

IF NOT EXISTS(SELECT UserID From AndroidLockInfo WHERE UserID=@dwUserID)
INSERT INTO AndroidLockInfo(UserID,AndroidStatus,ServerID,BatchID,LockDateTime) VALUES(@dwUserID,0,0,0,GETDATE());
END
SET @CountBegin = @CountBegin + 1
END

 

 

SQL server isnull使用方法

1、isnull(参数1，参数2)，判断参数1是否为NULL，如果是，返回参数2，否则返回参数1。

 在select的时候可能需要用到，如果一个字段返回null有可能导致程序报错，在这里我们可以用isnull来避免返回null.

 

 

SQL SERVER TOP 

 

TOP 子句

TOP 子句用于规定要返回的记录的数目。

对于拥有数千条记录的大型表来说，TOP 子句是非常有用的。

现在，我们希望从上面的 "Persons" 表中选取头两条记录。

我们可以使用下面的 SELECT 语句：

SELECT TOP 2 * FROM Persons

 

/*********************************************************************************
* Function: WEB_PageView2 *
* Description: *
* Sql2005分页存储过程 *
* Finish DateTime: *
* 2009/1/3 *
* Example: *
* WEB_PageView @Tablename = 'Table1', @Returnfields = '*', *
* @PageSize = 2, @PageIndex = 1, @Where = '', *
* @OrderBy=N'ORDER BY id desc' *
*********************************************************************************/

CREATE PROCEDURE dbo.WEB_PageView
@TableName NVARCHAR(2000), -- 表名
@ReturnFields NVARCHAR(1000) = '*', -- 查询列数
@PageSize INT = 10, -- 每页数目
@PageIndex INT = 1, -- 当前页码
@Where NVARCHAR(1000) = '', -- 查询条件
@OrderBy NVARCHAR(1000), -- 排序字段
@PageCount INT OUTPUT, -- 页码总数
@RecordCount INT OUTPUT -- 记录总数
WITH ENCRYPTION AS

--设置属性
SET NOCOUNT ON

-- 变量定义
DECLARE @TotalRecord INT
DECLARE @TotalPage INT
DECLARE @CurrentPageSize INT
DECLARE @TotalRecordForPageIndex INT

BEGIN
IF @Where IS NULL SET @Where=N''

-- 记录总数
DECLARE @countSql NVARCHAR(4000)

IF @RecordCount IS NULL
BEGIN
SET @countSql='SELECT @TotalRecord=Count(*) From '+@TableName+' '+@Where
EXECUTE sp_executesql @countSql,N'@TotalRecord int out',@TotalRecord OUT
END
ELSE
BEGIN
SET @TotalRecord=@RecordCount
END

SET @RecordCount=@TotalRecord
SET @TotalPage=(@TotalRecord-1)/@PageSize+1
SET @CurrentPageSize=(@PageIndex-1)*@PageSize

-- 返回总页数和总记录数
SET @PageCount=@TotalPage
SET @RecordCount=@TotalRecord
IF @PageCount IS NULL SET @PageCount = 0
IF @RecordCount IS NULL SET @RecordCount = 0

-- 返回记录
SET @TotalRecordForPageIndex=@PageIndex*@PageSize

EXEC ('SELECT *
FROM (SELECT TOP '+@TotalRecordForPageIndex+' '+@ReturnFields+', ROW_NUMBER() OVER ('+@OrderBy+') AS PageView_RowNo
FROM '+@TableName+ ' ' + @Where +' ) AS TempPageViewTable
WHERE TempPageViewTable.PageView_RowNo >
'+@CurrentPageSize)

END
RETURN 0
GO

 

CREATE PROCEDURE PR_Sum
@a int,
@b int,
@sum int output
AS
BEGIN
set @sum=@a+@b
END
--调用存储过程
declare @sum2 int
exec PR_Sum 1,2,@sum2 output
print @sum2
 

 

SQL server允许插入主键

 

set IDENTITY_INSERT student ON

set IDENTITY_INSERT student OFF

 

SQL server 清空数据库所有表

利用sysobjects:
sysobjects系统对象表。 保存当前数据库的对象。

use 数据库名(是要删除表的所在的那个数据库的名称)
GO
declare @sql varchar(8000)
while (select count(*) from sysobjects where type='U')>0
begin
SELECT @sql='drop table ' + name
FROM sysobjects
WHERE (type = 'U')
ORDER BY 'drop table ' + name
exec(@sql) 
end

declare @trun_name varchar(50)
declare name_cursor cursor for
select ''+name from sysobjects where xtype='U' ----and status > 0
open name_cursor
fetch next from name_cursor into @trun_name
while @@FETCH_STATUS = 0
begin
 
exec ( 'truncate table '+@trun_name)
  
fetch next from name_cursor into @trun_name
end
close name_cursor
deallocate name_cursor