—-收缩数据库日志 DBCC ShrinkFile(‘TestLogFull_Log’, 100)

来源： SQLServer数据库中开启CDC导致事务日志空间被占满的原因_woailyoo0000的博客-CSDN博客_数据库

SQLServer中开启CDC之后，在某些情况下会导致事务日志空间被占满的现象为：

在执行增删改语句（产生事务日志）的过程中提示，

The transaction log for database ‘***’ is full due to ‘REPLICATION’

(数据库“***”的事务日志已满，原因为“REPLICATION”).

解决办法：

EXEC SP_Repldone @xactid=NULL ,
@xact_segno = NULL,
@numtrabs = 0 ,
@time = 0
@reset = 1

CDC以及复制的基本原理粗略地讲，对于日志的使用步骤如下：

1，每当基础表（开启了CDC或者replication的表）产生事务性操作（增删改）之后，对应的事务日志写入日志文件，

2，此时的日志被状态被标记为Replication，也即处于待复制状态，这个活动状态跟数据库的还原模式无关，即便是简单还原模式，

3，然后有后台进程来读取这个日志，根据事务日志的内存写入目标表，

这个目标对于cdc来说是记录数据变化的系统表，

对于replication来说是写入distribution这个库

4，步骤3完成之后，事务日志被标记为正常状态，如果是简单还原模式，被后台进程解析过的事务日志被截断，可以重用如果上述中间的第三个步骤出现问题，也即后台进程无法解析日志后释放可用的日志空间，再次往数据库中写入操作，就会出现：数据库“TestDB”的事务日志已满，原因为“REPLICATION”的情况

本文通过通过演示开启CDC的情况下日志空间被占满的现象，以及对应的处理办法

测试环境搭建

首先建立一个测试数据库

1. USE master
2. GO
3. CREATE DATABASE TestLogFull ON PRIMARY
4. (
5. NAME = N‘TestLogFull’,
6. FILENAME = N‘D:\DBFile\TestLogFull\TestLogFull.mdf’ ,
7. SIZE = 500MB ,
8. MAXSIZE = UNLIMITED,
9. FILEGROWTH = 100MB
10. )
11. LOG ON
12. (
13. NAME = N‘TestLogFull_log’,
14. FILENAME = N‘D:\DBFile\TestLogFull\TestLogFull_Log.ldf’ ,
15. SIZE = 1MB ,
16. MAXSIZE = 512MB
17. )

这里指定日志文件的最大为512M，主要是为了演示日志空间被占满的现象

接着开启新建一个表同时开启CDC来测试

1. USE TestLogFull
2. –启用CDC
3. EXECUTE sys.sp_cdc_enable_db;
4. GO
5. –创建一张测试表
6. create table test_cdc
7. (
8. id int identity(1,1) primary key,
9. name nvarchar(50),
10. mail varchar(50),
11. address nvarchar(50),
12. lastupdatetime datetime
13. )
14. –对表启用CDC
15. EXEC sys.sp_cdc_enable_table
16. @source_schema = ‘dbo’,
17. @source_name = ‘test_cdc’,
18. @role_name = ‘cdc_admin’,
19. @capture_instance = DEFAULT,
20. @supports_net_changes = 1,
21. @index_name = NULL,
22. @filegroup_name = DEFAULT
24. –查询数据库是否开启了CDC
25. SELECT name, is_tracked_by_cdc
26. FROM sys.tables
27. WHERE OBJECT_ID = OBJECT_ID(‘dbo.test_cdc’)

这里演示对某些表开启CDC的情况下日志文件文件被占满的情况

1. 代理服务器未启动导致日志空间被占满

文中一开始提到的步骤3，对于CDC，进程就是SQL Server Agent中的cdc.***_capture作业或者复制代理作业来读取日志
如果SQL Server Agent在开启了CDC或者复制之后被关闭，或者重启服务器之后SQL Server Agent没有随机自动启动
就有可能造成步骤2中的日志积压，也就是记录数据变化之后的事务日志处于replication状态，无法重用，导致没有可以使用的日志致使发生操作数据库的时候提示The transaction log for database ‘***’ is full due to ‘REPLICATION’.

这里暂时关闭代理服务（仅仅是为了测试演示这一现象）

增删改都可以产生事务日志，这里就演示insert数据的情况，做一个写数据的SQL，往开启了CDC的表中写数据库
在建库的时候日志文件有限制成了512M，因为这个表上开启了CDC，写数据这个过程会产生事务日志，日志有空空间限制在写入数据的过程中，一开始是没有问题的，随着数据的不断写入（Replication状态的日志不断积压），当日志全部使用之后，下面的报错就会产生了

1. while 1=1
2. begin
3. Insert Into test_cdc values(newid(), newid(), getdate())
4. end

此时观察事务日志的使用情况，发现已经是完全使用了，

1. –查询日志使用率
2. DBCC SQLPERF(LOGSPACE)

因为日志空间被完全使用了，那么观察一下日志的等待状态，是Replication状态

2. —-查询等待日志模式
3. SELECT NAME,DATABASE_ID, LOG_Reuse_Wait, Log_Reuse_Wait_Desc
4. FROM
5. Sys.DATABASES
6. WHERE Name = ‘TestLogFull’

此时尝试收缩也是无效的，因为日志都是出于活动状态，活动状态的日志是无法收缩的

1. —-收缩数据库日志
2. DBCC ShrinkFile(‘TestLogFull_Log’, 100)

可见，因为代理被关闭，读取日志的作业无法执行，造成日志堵塞，那么开启代理来看看到底行不行？
开启代理，查看CDC作业的执行情况，会发现，此时代理作业也不好使了，作业执行的时候并没有成功，一样提示说事务日志已满

此时观察测试表的cdc目标表没有任何数据，说明此时即便开启了代理，cdc的作业依然没有成功执行
那么这里为什么CDC的代理作业也无法正常执行？

其实也不难理解，cdc的作业也是读取事务日志写数据的，这中间也相当于有事务性操作，必须要借助日志来实现，而此时又没有可用的日志空间，

这个作业当然要失败了。

那么此时怎么办？

既然是日志堵塞了，就想办法清理到这部分活动日志，尝试将事务日志标记为已分发（虽然这里是CDC，但是对于日志的使用应该是跟复制一样的）

1. –将日志中复制的事务标识改为已分发
2. EXEC SP_Repldone @xactid=NULL ,
3. @xact_segno = NULL,
4. @numtrabs = 0 ,
5. @time = 0
6. @reset = 1

据本人的测试，在执行上面的语句，将复制的事物标记为已分发之后，再次查看日志使用率，发现还是100%，但是尝试写入数据的时候是成功的，再次写入数据（一条即可）之后，日志空间开始释放，应该是写入时候的时候触发被标记为已分发的日志截断，也就是将上面占用了100%的日志空间释放出来然后再观察日志的使用率，发现如预期的，这部分日志已被截断，日志空间不再是被完全占用了，日志变成Nothing状态（可重用）

这个测试说明，如果开启了CDC，SQL Server代理没有正常启动或者对应的作业没有正常启动，日志空间会随着不断产生的事物被占满，导致数据库无法进行写入性操作

这里是用过手动标记日志为已分发的方式来释放日志的，这种情况下会导致cdc日志断裂的情况，也就是手动释放的日志无法传递到下游（cdc日志表）

毕竟不是一个太好的办法，下面会说明另外一种办法。

2，短时间内较大的事务性操作导致的日志空间被占满的情况

对去上面所说的代理服务被关闭导致日志堵塞的情况不同，这里直接开启代理服务，依旧拿着下面的脚本往表中写数据（比如实际业务中批量导入数据之类的）

在写入一段时间之后，依然出现了事务日志被填满的情况，这又是为什么？

还要从CDC的代理任务说起，这个代理的JOB虽然是连续执行的，但是因为上面写数据的时候也是连续写入的，也就是日志是连续产生的，

因为限制了日志文件的大小（这里为了方便演示，限制为512M），日志文件有最大使用空间的限制。

这里可以认为是一个Session消耗日志空间（Insert操作），一个进程解析日志之后释放日志空间（代理作业），

但是消耗的速度要高于释放的速度，一旦日志空间被使用完，CDC的代理作业也无法完成，

这样就又造成了上面的情况：日志空间被填满，数据库无法执行任何写入操作，CDC作业也无法执行从而释放可重用的日志空间，

上面是通过手动标记事务日志的状态来解决日志文件被填满的，

直接手动标记日志为已分发的做法是有点不合适的，

一旦标记日志状态为已分发，接下来他就不会传递给CDC的系统表或者订阅端了

这里通过另外一种方法来解决此问题:既然当前日志占满了，就在添加一个日志，注意新加日志初始化的空间不要太小。

（有兴趣测试的盆友，这里添加完日志文件后注意耐心等待一两分钟）然后随后的CDC作业会借助新加的这个日志空间会继续执行

此种情况说明，如果限制了日志的大小（或者存储日志的磁盘空间不足），数据库中开启了CDC或者复制，

一旦数据出现大批量持续性写入操作（增删改），此时会出现SQL Server代理解析并释放日志的速度跟不上，也有可能造成日志被占满的情况

3，不增加日志文件空间或者添加日志文件情况下重启SQLServer服务

这个办法也是本人在重现这一现象并尝试解决的时候试出来的，可行性不是太强，但还是说明一下，那就是重启大法，同时重启之后日志文件也发生了一些有意思的变化

建库的时候日志文件限制为最大512M，同时没有手动标记标记日志为已分发状态，但是重启SQLServer服务之后，如果存放日志的磁盘有空间，这个日志会自动扩充一部分

然后有了这部分扩充出来的日志，代理job就可以解析Replication状态的日志（之后）就可以释放日志空间了（需要一段时间来解析并释放日志，根据待复制的日志量有关）

下图可以明显看到，日志限制为512MB，但是初始化为556MB，明显大过最大日志大小，这个是归功于重启SQLServer服务的结果

一下是在SQL Server 2014 SP2版本下测试的现象，

如果是SQL Server 2014（非SP2补丁版），开启CDC的方式占满日志则不会出现如下的情况，也就是说重启有日志并不会自动扩充一部分，我也是醉了，验证个东西真不容易，这些小细节跟补丁版本也有关系，不过这种偏门的方法不能作为经验！

总结：

当开启了CDC之后，在相关表上的变化会写入事务日志（日志状态为Replication状态），代理任务会解析日志，解析完日之后标记日志为可重建状态（如果是简单还原模式，是可重用，如果是完整还原模式，日志备份也无法截断Replication状态的日志），这种状态下如果限制了日志的最大大小比较小，或者没有限制，存储日志的磁盘空间不足，在大批量写入数据（增删改）的时候，有可能产生的日志占满日志文件的情况，会导致释放日志的代理作业无法进行，代理作业无法进行又无法释放日志，仿佛是死循环。

此时要么新增日志文件或者增加日志文件的最大大小，要么通过执行系统存储过程sp_repldone来标记事务为已分发（标记事务日志可重用）来解决这一问题。

以上所述是小编给大家介绍的SQLServer数据库中开启CDC导致”事务日志空间被占满的原因分析和解决办法(REPLICATION)，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持！

select @@Rowcount

来源： SQL Server中Rowcount与@@Rowcount的用法 – Nina – 博客园

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

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

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

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

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

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

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

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

删除也是一样

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

@@Rowcount的用法

@@Rowcount与Rowcount看起来很像，只相差了两个@,但它们的功能是不一样的，@@Rowcount主要是返回上次sql语句所影响的数据行数，比如：

如果表A中的数据量大于或等于2，那么select @@Rowcount就会返回2，如果只有1条或0条数据，那么select @@Rowcount就会返回1或者0。

注意，不要把@@Rowcount理解为只返回查询的结果数量，删除，修改，新增等语句，也会正确的返回@@Rowcount值。比如：

如果表A中存在gid=’a’的数据，那么select @@Rowcount就会返回它所修改数据的行数，如果不存在gid=’a’的数据，那么select @@Rowcount就会返回0,删除与新增都是同样。

那么，哪些地方我们会用到@@Rowcount呢？
一、可能我们见到@@Rowcount身影最多的地方是触发器中，好的触发器，一般都会在最前面加上if @@rowcount=0 return语句，比如：

这样，如果tablea被修改的数据行数为0，那么触发器ti_tablea就会直接退出，而不用执行后面的代码了。

二、第二个可能用到的地方就是我们可以使用@@rowcount来作递归或循环。比如下面示例：

这个示例是先查询client_goods中是否有id=1的数据，如果有，再查询是否有id=2的数据，一直查下去，直到id没有连续为止。当然大家在看这个示例的时候不要考虑这个示例的意义，它只是说明了@@rowcount可以作为循环条件来用。

rowcount与@@rowcount的其它用法，欢迎大家在评论中补充

http://www.lmwlove.com/ac/ID943

来源： SqlServer 实现rownum 的功能_数据库_dragoo1的专栏-CSDN博客

SQLServer 实现rownum 的功能：：

SQL Server 的语法：

SELECT TOP number|percent column_name(s)
FROM table_name

方法1:

with temp as

( select row_number() over(order by cityID) as rownum,cityNamefrom city )

select * from temp where rownum between 10 and 20

go

解释:

1 此方法把括号里的查询结果放到变量:temp 里面( 我也不确定是不是变量), 并用row_number()函数进行一个行号跟踪, 再用over 函数进行一个列的排序规则( 是这必须的), 并指定列名为’rownum’

2 紧接着在下面的语句可以对 ‘rownum’ 进行一个指定行号的查询

3 此批语句执行完毕后, 变量:temp 释放

方法2:

select identity(int,1,1) as rownum, cityName into #temp fromcity

select * from #temp where rownum between 10 and 20

go

解释:

此方法跟上面的差不多的意思, 只不过把 row_number() 函数换成了 identity() 函数

并把结果集放在一个临时表里面, 当批语句执行完毕, 此临时表还可以使用

—————————————

如何让SELECT 查询结果额外增加自动递增序号_SQL技巧

如果数据表本身并不内含自动地增编号的字段时，要怎么做才能够让SELECT查询结果如图表1所示，额外增加自动递增序号呢？我们提供下列五种方法供您参考：

USE北风贸易;
GO

软件开发网

SELECT序号= (SELECT COUNT(客户编号) FROM 客户 AS LiMing
WHERE LiMing.客户编号<= Chang.客户编号),
客户编号,公司名称
FROM客户 AS Chang ORDER BY 1;
GO

SELECT RANK() OVER (ORDER BY 客户编号 DESC) AS 序号,
客户编号,公司名称
FROM客户;
GO

SELECT序号= COUNT(*), LiMing.客户编号, LiMing.公司名称
FROM 客户 AS LiMing, 客户AS Chang
WHERE LiMing.客户编号>= Chang.客户编号
GROUP BY LiMing.客户编号, LiMing.公司名称
ORDER BY 序号;
GO

SELECT序号= IDENTITY(INT,1,1),管道,程序语言,讲师,资历
INTO #LiMing
FROM问券调查一;
GO
SELECT * FROM #LiMing;
GO
DROP TABLE #LiMing;
GO

WITH排序后的图书 AS
(SELECT ROW_NUMBER() OVER (ORDER BY 客户编号 DESC) AS 序号,
客户编号,公司名称
FROM 客户)
SELECT * FROM 排序后的图书
WHERE序号 BETWEEN 2 AND 4;
GO

——————————

例如：
在ORACLE中：
“select   *   from  ”   &  m_tablename   &  ”   WHERE  ROWNUM <=300 ”        取前三百行记录

“SELECT   ROWNUM   AS  序号,A.*   FROM  控制点查询信息   A    WHERE   A.查询编码= ‘888888 ‘  ORDER   BY  序号 ”
增加序号字段

但在SQL   SERVER  中不能运行，好像不识别ROWNUM。如果要替换（最好能直接替换的，因为本系统是ORACEL与SQL  SERVER都可以运行的），应该用什么啊？

释：

1.–rownum在oracle里面就是物理顺序，那么

在SQLServer就直接select   top  300,不用管用什么排序

2.–查询后排序,例如:
select   序号=(select   count(1)  from   TableName  where   id<=a.id),*   from  TableName   a

3.select * from 表 where id >= ALL (select id from表);
select * from 表 where id <= ALL (select id from表);
select * from 表 where id = (select max(distinct id) from 表);
select * from 表 where id = (select min(distinct id) from 表);

转自：http://blog.sina.com.cn/s/blog_670dd1b10100kj5e.html

来源： SQL优化案例—— RowNumber分页 – czperfectaction – 博客园

将业务语句翻译成SQL语句不仅是一门技术，还是一门艺术。

下面拿我们程序开发工程师最常用的ROW_NUMBER()分页作为一个典型案例来说明。

先来看看我们最常见的分页的样子：

WITH CTE AS(
    SELECT    ROW_NUMBER() OVER ( ORDER BY (A.CreateTime ) AS OrderNo ,
        Table_A.ID ,    --主键
        Table_A.其它字段
    FROM      Table_A WITH ( NOLOCK )
    WHERE     RecID = 220051
) 
SELECT * FROM CTE
WHERE   OrderNo BETWEEN 1 AND 50;

的确，这样的写法很符合我们的思维逻辑，并且我们在RecID上建立非聚集索引，那么它的效率看上去也是不错的。当然根据这条SQL，最佳索引实践应该是:

CREATE INDEX IX_Table_A_RecID_CreateTime_Inc
ON Table_A(RecID,CreateTime)
INCLUDE(Table_A.其它字段)

但是，这真的是最佳的了吗？当SQL的Where条件变多，Table_A.其它字段变得越来越多，OVER()子句中的OrderBy字段越来越多或者变成Order By ColumnA/ColumnB这样的计算表达式，这条语句变得越来越不堪重负，最终性能问题凸现出来，另外，作为DBA，我们总是尽量维持索引的简单性、可重用度，而不想建立成为某个语句专用的索引。举例来说，在Include中，我们总不能把Table_A.其它字段中的所有字段都放进去吧，个数少还行，如果遇上几十个字段或者有大容量字符字段，维护成本将大大增加，那将是我们不愿意看到的。

这个时候就要求我们看看是否能对语句做出一些优化了。

在上面的SQL中，我们看它的执行计划，我已经建立了索引，该索引并未Include SELECT列表中的其它字段：

CREATE INDEX IX_Table_A_RecID_CreateTime_Inc
ON Table_A(RecID,CreateTime)

根据上图的执行计划，可以看到，WHERE条件走的是我刚刚建立的索引，下面的键查找与其并行，我们先不讨论该执行计划的具体细节，下面我们来设想几个问题：

在WHERE条件简单，并且索引合适，统计信息正确的前提下，SQL Server可以很容易获得那50行，并且回到聚集索引中找到属于它的其它字段的数据，这是SQL Server的智能编译的结果，也是我们希望看到的返回方式。

但是，在WHERE条件较为复杂，多个WHERE条件均为范围字段或者状态字段时，执行计划也许并没有我们想象的那么智能了，比如它可能采用这样的方式：

当SQL Server无法准确的取出你要的那些行时，那么它便会取回全部的行数后，再去聚集索引中找回属于它的其它字段的数据，当where条件可以返回几十万数据时，你可以想象它的效率有多低，它会仍然使用上文中类似的执行计划，这显然不是我们希望看到的。

我们想看到的是什么？

1、根据WHERE条件和排序规则，先取出那50条数据所属的主键。

2、利用上个步骤中返回的主键，去原始表取回这50条记录的其它字段数据。

SELECT B.*,A.其它字段 FROM Table_A A WITH ( NOLOCK )
    INNER JOIN #1 B ON A.ID=B.ID
WHERE   B.OrderNo BETWEEN 1 AND 50;

那么，上面两个步骤合在一起：

WITH CTE AS(
    SELECT    ROW_NUMBER() OVER ( ORDER BY A.CreateTime ) AS OrderNo ,
        Table_A.ID    --主键
    FROM      Table_A WITH ( NOLOCK )
    WHERE     RecID = 220051
) 
SELECT CTE.*,A.其它字段 FROM Table_A A WITH ( NOLOCK )
    INNER JOIN CTE ON A.ID=CTE.ID
WHERE   CTE.OrderNo BETWEEN 1 AND 50;

很好，现在我们再来看一下这个SQL的执行计划：

Binggo!这才是我们理想中的样子！

针对这个SQL，我们只需要建立一个合适的索引，而不用顾忌SELECT列表中那些烦人的其它列，因为他们回聚集索引取数据，也不过几百个IO而已（需要返回的行数*Index_Level）。它不需要再为过期的统计信息或者错误的执行计划而付出沉重的代价！

总结：SQL优化，是一门艺术。

来源： WITH查询（公共表表达式）

7.8. WITH查询（公共表表达式）

WITH提供了一种方式来书写在一个大型查询中使用的辅助语句。这些语句通常被称为公共表表达式或CTE，它们可以被看成是定义只在一个查询中存在的临时表。在WITH子句中的每一个辅助语句可以是一个SELECT、INSERT、UPDATE或DELETE，并且WITH子句本身也可以被附加到一个主语句，主语句也可以是SELECT、INSERT、UPDATE或DELETE。

WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
    SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part, p.quantity
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
  )
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part

WITH RECURSIVE search_graph(id, link, data, depth) AS (
        SELECT g.id, g.link, g.data, 1
        FROM graph g
      UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1
        FROM graph g, search_graph sg
        WHERE g.id = sg.link
)
SELECT * FROM search_graph;

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
        SELECT g.id, g.link, g.data, 1,
          ARRAY[g.id],
          false
        FROM graph g
      UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1,
          path || g.id,
          g.id = ANY(path)
        FROM graph g, search_graph sg
        WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
        SELECT g.id, g.link, g.data, 1,
          ARRAY[ROW(g.f1, g.f2)],
          false
        FROM graph g
      UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1,
          path || ROW(g.f1, g.f2),
          ROW(g.f1, g.f2) = ANY(path)
        FROM graph g, search_graph sg
        WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

WITH RECURSIVE t(n) AS (
    SELECT 1
  UNION ALL
    SELECT n+1 FROM t
)
SELECT n FROM t LIMIT 100;

WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
    RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;

WITH t AS (
    DELETE FROM foo
)
DELETE FROM bar;

WITH RECURSIVE included_parts(sub_part, part) AS (
    SELECT sub_part, part FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
  )
DELETE FROM parts
  WHERE part IN (SELECT part FROM included_parts);

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM products;

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM t;

来源： sql server 2008 rownumber 分页sql语句 – zyf – 博客园

SELECT
ID,TSRID, plid,CustomerName,SellCode,Mobile ,ReserveTime,CrtTime,hjtime,jieshuma ,
InsureBillCode, tname,bname,shoucang,xingbie,ActivityName ,sydate,AddInsuranceID
FROM
———————————————–
(

SELECT *,ROW_NUMBER() OVER (ORDER BY hjtime desc) AS RowNo

FROM [C_V_SellData]

where
CrtUser=358
and StoreType=1
and EndCode <> 1
and EndCode <> 5
and hjtime  >= cast(convert(varchar,’2014/1/18′,111) as datetime)
and hjtime <  cast(convert(varchar,’2014/2/18′,111) as datetime) + 1
and isnull(AddInsuranceID,”)<>’N’  ) AS A

————————————————–
WHERE   RowNo>=1  and RowNo<=50

来源： Sql Server RowNumber和表变量分页性能优化小计 – 寻自己 – 博客园

直接让代码了，对比看看就了解了

当然，这种情况比较适合提取字段较多的情况，要酌情而定

性能较差的：

优化后，性能较好的

相关文章：Sql Server 2012 分页方法分析(offset and fetch)
原文地址：Sql Server RowNumber和表变量分页性能优化小计

来源： C# 数据库并发的解决方案（通用版、EF版） – 天才卧龙 – 博客园

还是那句老话：十年河东，十年河西，莫欺骚年穷！~_~ 打错个字，应该是莫欺少年穷！

学历代表你的过去，能力代表你的现在，学习代表你的将来。

学无止境，精益求精。

自ASP.NET诞生以来，微软提供了不少控制并发的方法，在了解这些控制并发的方法前，我们先来简单介绍下并发！

并发：同一时间或者同一时刻多个访问者同时访问某一更新操作时，会产生并发！

针对并发的处理，又分为悲观并发处理和乐观并发处理

所谓悲观/乐观并发处理，可以这样理解：

悲观者认为：在程序的运行过程中，并发很容易发生滴，因此，悲观者提出了他们的处理模式：在我执行一个方法时，不允许其他访问者介入这个方法。（悲观者经常认为某件坏事会发生在自己身上）

乐观者认为：在程序的运行过程中，并发是很少发生滴，因此，乐观者提出了他们的处理模式：在我执行一个方法时，允许其他访问者介入这个方法。（乐观者经常认为某件坏事不会发生在自己身上）

那么在C#语言中，那些属于悲观者呢？

在C#中诸如：LOCK、Monitor、Interlocked 等锁定数据的方式，属于悲观并发处理范畴！数据一旦被锁定，其他访问者均无权访问。有兴趣的可以参考：锁、C#中Monitor和Lock以及区别

但是，悲观者处理并发的模式有一个通病，那就是可能会造成非常低下的执行效率。

在此：举个简单例子：

售票系统，小明去买票，要买北京到上海的D110次列车，如果采用悲观者处理并发的模式，那么售票员会将D110次列车的票锁定，然后再作出票操作。但是，在D110次列车车票被锁定期间，售票员去了趟厕所，或者喝了杯咖啡，其他窗口售票员是不能进行售票滴！如果采用这种处理方式的话，中国14亿人口都不用出行了，原因是买不到票 ~_~

因此：在处理数据库并发时，悲观锁还是要谨慎使用！具体还要看数据库并发量大不大，如果比较大，建议使用乐观者处理模式，如果比较小，可以适当采用悲观者处理模式！

OK。说了这么多，也就是做个铺垫，本节内容标题叫数据库并发的解决方案，我们最终还得返璞归真，从数据库并发的解决说起！

那么问题来了？

数据库并发的处理方式有哪些呢？

其实数据库的并发处理也是分为乐观锁和悲观锁，只不过是基于数据库层面而言的！关于数据库层面的并发处理大家可参考我的博客：乐观锁悲观锁应用

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。[1]

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。[1] 乐观锁不能解决脏读的问题。

最常用的处理多用户并发访问的方法是加锁。当一个用户锁住数据库中的某个对象时，其他用户就不能再访问该对象。加锁对并发访问的影响体现在锁的粒度上。比如，放在一个表上的锁限制对整个表的并发访问；放在数据页上的锁限制了对整个数据页的访问；放在行上的锁只限制对该行的并发访问。可见行锁粒度最小，并发访问最好，页锁粒度最大，并发访问性能就会越低。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。[1] 悲观锁假定其他用户企图访问或者改变你正在访问、更改的对象的概率是很高的，因此在悲观锁的环境中，在你开始改变此对象之前就将该对象锁住，并且直到你提交了所作的更改之后才释放锁。悲观的缺陷是不论是页锁还是行锁，加锁的时间可能会很长，这样可能会长时间的锁定一个对象，限制其他用户的访问，也就是说悲观锁的并发访问性不好。

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。[1] 乐观锁不能解决脏读的问题。 乐观锁则认为其他用户企图改变你正在更改的对象的概率是很小的，因此乐观锁直到你准备提交所作的更改时才将对象锁住，当你读取以及改变该对象时并不加锁。可见乐观锁加锁的时间要比悲观锁短，乐观锁可以用较大的锁粒度获得较好的并发访问性能。但是如果第二个用户恰好在第一个用户提交更改之前读取了该对象，那么当他完成了自己的更改进行提交时，数据库就会发现该对象已经变化了，这样，第二个用户不得不重新读取该对象并作出更改。这说明在乐观锁环境中，会增加并发用户读取对象的次数。

本篇的主旨是讲解基于C#的数据库并发解决方案（通用版、EF版），因此我们要从C#方面入手，最好是结合一个小项目

项目已为大家准备好了，如下：

首先我们需要创建一个小型数据库：

create database  BingFaTest
go
use BingFaTest
go 
create table Product--商品表
(
ProductId int identity(1,1) primary key,--商品ID 主键
ProductName nvarchar(50),--商品名称
ProductPrice money,--单价
ProductUnit nvarchar(10) default('元/斤'),
AddTime datetime default(getdate())--添加时间

)


create table Inventory--库存表
(
InventoryId int identity(1,1) primary key,
ProductId int FOREIGN KEY REFERENCES Product(ProductId), --外键
ProductCount int,--库存数量
VersionNum TimeStamp not null,
InventoryTime datetime default(getdate()),--时间
)

create table InventoryLog
(
Id int identity(1,1) primary key,
Title nvarchar(50),
)


--测试数据：
insert into Product values('苹果',1,'元/斤',GETDATE())


insert into Inventory(ProductId,ProductCount,InventoryTime) values(1,100,GETDATE())

创建的数据库很简单，三张表：商品表，库存表，日志表

有了数据库，我们就创建C#项目，本项目采用C# DataBaseFirst 模式，结构如下：

项目很简单，采用EF DataBaseFirst 模式很好构建。

项目构建好了，下面我们模拟并发的发生？

主要代码如下（减少库存、插入日志）：

#region 未做并发处理
        /// <summary>
        /// 模仿一个减少库存操作  不加并发控制
        /// </summary>
        public void SubMitOrder_3()
        {
            int productId = 1;

            using (BingFaTestEntities context = new BingFaTestEntities())
            {
                var InventoryLogDbSet = context.InventoryLog;
                var InventoryDbSet = context.Inventory;//库存表

                using (var Transaction = context.Database.BeginTransaction())
                {
                    //减少库存操作
                    var Inventory_Mol = InventoryDbSet.Where(A => A.ProductId == productId).FirstOrDefault();//库存对象
                    Inventory_Mol.ProductCount = Inventory_Mol.ProductCount - 1;
                    int A4 = context.SaveChanges();
                    //插入日志
                    InventoryLog LogModel = new InventoryLog()
                    {
                        Title = "插入一条数据，用于计算是否发生并发",

                    };
                    InventoryLogDbSet.Add(LogModel);
                    context.SaveChanges();
                    //1.5  模拟耗时
                    Thread.Sleep(500); //消耗半秒钟
                    Transaction.Commit();
                }

            }
        }
        #endregion

此时我们 int productId=1 处加上断点，并运行程序(打开四个浏览器同时执行)，如下：

由上图可知，四个访问者同时访问这个未采用并发控制的方法，得到的结果如下：

结果显示：日志生成四条数据，而库存量缺只减少1个。这个结果显然是不正确的，原因是因为发生了并发，其本质原因是脏读，误读，不可重读造成的。

那么，问题既然发生了，我们就想办法法解决，办法有两种，分别为：悲观锁方法、乐观锁方法。

悲观者方法：

悲观者方法（加了uodlock锁，锁定了更新操作，也就是说，一旦被锁定，其他访问者不允许访问此操作）类似这种方法，可以通过存储过程实现，在此不作解释了

乐观者方法（通用版/存储过程实现）：

在上述数据库脚本中，有字段叫做：VersionNum，类型为：TimeStamp。

字段 VersionNum 大家可以理解为版本号，版本号的作用是一旦有访问者修改数据，版本号的值就会相应发生改变。当然，版本号的同步更改是和数据库相关的，在SQLServer中会随着数据的修改同步更新版本号，但是在MySQL里就不会随着数据的修改而更改。因此，如果你采用的是MYSQL数据库，就需要写一个触发器，如下：

OK，了解了类型为Timestamp的字段，下面我们结合上述的小型数据库创建一个处理并发的存储过程，如下

create proc LockProc --乐观锁控制并发
(
@ProductId int, 
@IsSuccess bit=0 output
)
as
declare @count as int
declare @flag as TimeStamp
declare @rowcount As int 
begin tran
select @count=ProductCount,@flag=VersionNum from Inventory where ProductId=@ProductId
 
update Inventory set ProductCount=@count-1 where VersionNum=@flag and ProductId=@ProductId
insert into InventoryLog values('插入一条数据，用于计算是否发生并发')
set @rowcount=@@ROWCOUNT
if @rowcount>0
set @IsSuccess=1
else
set @IsSuccess=0
commit tran

这个存储过程很简单，执行两个操作：减少库存和插入一条数据。有一个输入参数：productId ，一个输出参数，IsSuccess。如果发生并发，IsSuccess的值为False，如果执行成功，IsSuccess值为True。

在这里，向大家说明一点：程序采用悲观锁，是串行的，采用乐观锁，是并行的。

也就是说：采用悲观锁，一次仅执行一个访问者的请求，待前一个访问者访问完成并释放锁时，下一个访问者会依次进入锁定的程序并执行，直到所有访问者执行结束。因此，悲观锁严格按照次序执行的模式能保证所有访问者执行成功。

采用乐观锁时，访问者是并行执行的，大家同时访问一个方法，只不过同一时刻只会有一个访问者操作成功，其他访问者执行失败。那么，针对这些执行失败的访问者怎么处理呢？直接返回失败信息是不合理的，用户体验不好，因此，需要定制一个规则，让执行失败的访问者重新执行之前的请求即可。

时间有限，就不多写了…因为并发的控制是在数据库端存储过程，所以，C#代码也很简单。如下：

#region 通用并发处理模式 存储过程实现
        /// <summary>
        /// 存储过程实现
        /// </summary>
        public void SubMitOrder_2()
        {
            int productId = 1;
            bool bol = LockForPorcduce(productId);
            //1.5  模拟耗时
            Thread.Sleep(500); //消耗半秒钟
            int retry = 10;
            while (!bol && retry > 0)
            {
                retry--;
                LockForPorcduce(productId);
            }
        }


        private bool LockForPorcduce(int ProductId)
        {
            using (BingFaTestEntities context = new BingFaTestEntities())
            {
                SqlParameter[] parameters = {
                    new SqlParameter("@ProductId", SqlDbType.Int),
                    new SqlParameter("@IsSuccess", SqlDbType.Bit)
                    };
                parameters[0].Value = ProductId;
                parameters[1].Direction = ParameterDirection.Output;
                var data = context.Database.ExecuteSqlCommand("exec LockProc @ProductId,@IsSuccess output", parameters);
                string n2 = parameters[1].Value.ToString();
                if (n2 == "True")
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }
        #endregion

在此，需要说明如下：

当IsSuccess的值为False时，应该重复执行该方法，我定的规则是重复请求十次，这样就很好的解决了直接反馈给用户失败的消息。提高了用户体验。

下面着重说下EF框架如何避免数据库并发，在讲解之前，先允许我引用下别人博客中的几段话：

在软件开发过程中，并发控制是确保及时纠正由并发操作导致的错误的一种机制。从 ADO.NET 到 LINQ to SQL 再到如今的 ADO.NET Entity Framework，.NET 都为并发控制提供好良好的支持方案。

相对于数据库中的并发处理方式，Entity Framework 中的并发处理方式实现了不少的简化。

在System.Data.Metadata.Edm 命名空间中，存在ConcurencyMode 枚举，用于指定概念模型中的属性的并发选项。
ConcurencyMode 有两个成员:

当模型属性为默认值 None 时，系统不会对此模型属性进行检测，当同一个时间对此属性进行修改时，系统会以数据合并方式处理输入的属性值。
当模型属性为Fixed 时，系统会对此模型属性进行检测，当同一个时间对属性进行修改时，系统就会激发OptimisticConcurrencyException 异常。

开发人员可以为对象的每个属性定义不同的 ConcurencyMode 选项，选项可以在*.Edmx找看到：

Edmx文件用记事本打开如下：

 View Code

其实，在EF DataBaseFirst中，我们只需设置下类型为 TimeStamp 版本号的属性即可，如下：

设置好了版本号属性后，你就可以进行并发测试了，当系统发生并发时，程序会抛出异常，而我们要做的就是要捕获这个异常，而后就是按照自己的规则，重复执行请求的方法，直至返回成功为止。

那么如何捕获并发异常呢？

在C#代码中需要使用异常类：DbUpdateConcurrencyException 来捕获，EF中具体用法如下：

public class SaveChangesForBF : BingFaTestEntities
    {
        public override int SaveChanges()
        {
            try
            {
                return base.SaveChanges();
            }
            catch (DbUpdateConcurrencyException ex)//（OptimisticConcurrencyException）
            {
                //并发保存错误
                return -1;
            }
        }
    }

设置好属性后，EF会帮我们自动检测并发并抛出异常，我们用上述方法捕获异常后，就可以执行我们重复执行的规则了，具体代码如下：

 View Code

至此，C#并发处理就讲解完了，是不是很简单呢？

项目源码地址：http://download.csdn.net/download/wolongbb/9977216

来源： c#开发地磅称重软件 – s1ihome – 博客园

2012年时即做过一个地磅称重软件，最近公司又接了一个地磅过磅软件的项目，把遇到的问题总结一下以备后用。

1.接线问题

因为客户方原来单独使用仪表，仪表未有接线和电脑连接，为此颇费周折才做好了接线。接线方式为仪表端所接阵脚为7、8,电脑端为2、5

2.读取仪表称重数

代码基本沿袭2012年为另一客户所开发的称重软件的代码。

注：本次客户所使用地磅为泰山衡器厂出的XK3200，但说明书上说明和耀华系列地磅兼容，而上一客户所使用地磅正是耀华XK3190，大概因此代码基本可直接通用。

下面贴代码了，使用的serialPort控件，命名为port

称重窗体设计器代码页 FrmWeigh.designer.cs中

称重窗体FrmWeigh.cs获取地磅仪表数核心代码部分：

 1         /// <summary>
 2         /// 串口读取数据
 3         /// </summary>
 4         private void port_DataReceived(object sender, System.IO.Ports.SerialDataReceivedEventArgs e)
 5         {
 6             Thread.Sleep(100);            
 7             if (false == this.port.IsOpen) return;
 8             byte firstByte = Convert.ToByte(port.ReadByte());
 9             if (firstByte == 0x02)
10             {
11                 int bytesRead = port.ReadBufferSize;
12                 byte[] bytesData = new byte[bytesRead];
13                 byte byteData;
14 
15                 for (int i = 0; i < bytesRead - 1; i++)
16                 {
17                     byteData = Convert.ToByte(port.ReadByte());
18                     if (byteData == 0x03)//结束
19                     {
20                         break;
21                     }
22                     bytesData[i] = byteData;
23                 }
24                 strReceive = Encoding.Default.GetString(bytesData);
25             }
26             tbWeight.Invoke(new EventHandler(delegate { tbWeight.Text = GetWeightOfPort(strReceive); }));
27         }
28 
29         /// <summary>
30         /// 返回串口读取的重量
31         /// </summary>
32         /// <param name="?"></param>
33         /// <returns></returns>
34         private string GetWeightOfPort(string weight)
35         {
36             if (string.IsNullOrEmpty(weight) || weight.IndexOf("+") < 0 || weight.Length < 6)
37             {
38                 return "0.000";
39             }
40             weight = weight.Replace("+", "");
41             weight = int.Parse(weight.Substring(0, 3)).ToString() + "." + weight.Substring(3, 3);
42             return weight;
43         }

来源： C# 地磅串口编程 – 沙茶叶 – 博客园

现实生活中，我们会经常遇到一些串口的设备，例如：IC卡、RFID等；

然后最近有一个项目用到了地磅，这里也是通过串口通讯方式进行数据交互，说实话，地磅这东西，实在有点不方便。

然而，串口的编程，不得不说下串口的DCB（Device Control Block）结构，做过串口编程的人应该都知道，而我这里也只是记录下自己学过的东西，高手路过的请勿吐槽。

一般串口编程都是通过C/C++ 来通信，然后.Net 也封装了SerialPort的控件，但是这里还是简单介绍下：

首先，看看DCB的结构：

 1         //Device Control Block
 2         [StructLayout(LayoutKind.Sequential)]
 3         private struct DCB
 4         {
 5             //taken from c struct in platform sdk 
 6             public int DCBlength;           // DCB结构的长度（以字节为单位）
 7             public int BaudRate;            // 波特率设置
 8             public int fBinary;             // 二进制模式。(必须为1 )
 9             public int fParity;             // TRUE时， 支持奇偶检验
10             public int fOutxCtsFlow;        // TRUE时，支持CTS流控制。 当CTS为OFF时，停止发送。
11             public int fOutxDsrFlow;        // TRUE时，支持DSR流控制。 当DSR为OFF时，停止发送。
12             public int fDtrControl;         // DTR设置。 （置高/置低...)   
13             public int fDsrSensitivity;     // TRUE时，当DSR为OFF，则接收端忽略所有字符 
14             public int fTXContinueOnXoff;   // TRUE时，不管接收端是否Xoff， 本方发送端持续发送。为False 时，则当接收端buffer 达到XoffLim时，发送端发送完Xoff字符后，就停止发送。
15             public int fOutX;               // 发送端支持Xon/Xoff 
16             public int fInX;                // 接收端支持Xon/Xoff
17             public int fErrorChar;          // TRUE时，若fParity为TRUE， 则用ErrorChar替换Parity Check错误的字符。
18             public int fNull;               // TRUE时，接收时去掉空字节（0x0) 
19             public int fRtsControl;         // RTS设置。 （置高/置低...)   
20             public int fAbortOnError;       // TRUE时，发生错误时停止读写操作。
21             public int fDummy2;             // 保留 
22             public ushort wReserved;        // 保留
23             public ushort XonLim;           // 当接收Buffer中的字符减少小XonLim规定的字符数， 就发送Xon字符，让对方继续发送。
24             public ushort XoffLim;          // 接收Buffer达到XoffLim规定的字符数， 就发送Xoff字符， 让对方停止发送。
25             public byte ByteSize;           // 数据位设置
26             public byte Parity;             // 奇偶检验位的设置：0-4=no,odd,even,mark,space
27             public byte StopBits;           // 停止位的设置:0,1,2 = 1, 1.5, 2
28             public char XonChar;            // Xon 字符
29             public char XoffChar;           // Xoff 字符 
30             public char ErrorChar;          // Parity Check 错误时，替换的字符 
31             public char EofChar;            // EOF替代字符 
32             public char EvtChar;            // 事件触发字符
33             public ushort wReserved1;       // 保留
34         }

对于串口的封装，这里有个串口通信类可以用：

http://www.cnblogs.com/tuyile006/archive/2006/09/25/514327.html

然后在打开串口时，需要设置相关的波特率、数据位与校验位：

注意：这里的数据需要通过与 地磅的生产商 取得相应的规格。

然后在串口的选择这里，可以通过程序读取计算机上的硬件设备：

 1 //需要引用组件：Microsoft.VisualBasic.Devices;
 2 private void ParameterConfig_Load(object sender, EventArgs e)
 3 {
 4     cbbCom.Items.Clear();
 5     Microsoft.VisualBasic.Devices.Computer pc = new Microsoft.VisualBasic.Devices.Computer();
 6     foreach (string s in pc.Ports.SerialPortNames)  //遍历本机所有串口
 7     {
 8         this.cbbCom.Items.Add(s); 
 9     }
10     SetValue();
11 }

通过通信类mycom对串口、波特率、数据位、校验位的赋值：

1 ComHelper mycom = new ComHelper();
2 mycom.PortNum = config.Port; //串口;
3 mycom.BaudRate = config.BaudRate; // 波特率;
4 mycom.ByteSize = Convert.ToByte(config.ByteSize); //数据位;
5 mycom.Parity = Convert.ToByte(config.Parity); //校验位;

再读取串口返回的数据：

 1 //1.读取串口数据
 2 byte[] getBytes = mycom.Read(NumsBytes);
 3 
 4 //2。获取16进制字符串
 5 receData = HexConvert.ByteToString(getBytes);
 6 
 7 //3.处理串口连续输出字符串
 8 if (receData.Length > 0)
 9 {
10     OutPutHelper helper = new OutPutHelper();
11     result = helper.getWeight(receData).ToString();
12 
13     //4.其他处理...
14 
15 }

这里获取到的十六进制数：02 72 60 20 30 30 30 36 37 30 30 30 30 30 30 30 0D 4E

说明下，这里的地磅串口输出格式是：

其中：
1.<STX>ASCⅡ起始符．(0 2H)
2.状态字　A、B、C.
3.显示重量，可能是毛重也可能是净重，6位不带符号和小数点的数字．
4.皮重值，6位不带字符和小数点的数字．
5.<CR>ASCⅡ字符（0 DH)．

所以，我们只需要从 第5位 开始到 第10位的 数据，即：30 30 30 36 37 30

通过解释后，得到的重量为：670.

如果没有东西过磅的情况下，取到的数据是：30 30 30 30 30 30

即是：0.

由于地磅是大磅，不计小数点，所以可以忽略小数点的情况。

————————————————————————————————————————————

其实这里通过SerialPort控件来实现串口编程会快捷点，而相关的使用方法，网上很多地方可以找到。

只是首次遇到串口编程的问题，想了解相关内容……