- MSSQLServer中从INSERT中返回IDENTITY
遇到这样一个问题,一张表只有一个PK且是IDENTITY,在向表中插入数据后需要得到插入记录的PK值。刚开始使用锁表的方法,比较繁琐,后来发现下面的方法: SET NOCOUNT ON; INSERT INTO USERMST (Name) VALUES (‘username‘); SELECT @@IDENTITY 关键在第一行上,设置不要返回受操作影响的行数。执行上面的SQL后,可以从ResultSet中得到ID的值,也就是SELECT @@IDENTITY取得的最新的IDENTITY值。 参考:http://www.microsoft.com/china/MSDN/library/data/sqlserver/FiveWaystoRevupYourSQLPerformanCE.mspx
在ORACLE中也有类似方法:(当然,也可以从Sequence中得到) Declare userrow USERMST%rowtype; Begin INSERT INTO USERMST (Id,Name) VALUES (idseq.nextval,‘username‘) RETURNING Id,Name INTO userrow.Id, userrow.Name; End; Oracle Release 2之后可以简化为 INSERT INTO USERMST (Id,Name) VALUES (idseq.nextval,‘username‘) RETURNING Id,Name INTO userrow; 参考:http://www.cnblogs.com/sunsonbaby/archive/2005/02/01/100547.html
- MSSQLServer中的事务锁
还是同一个项目,遇到一个事务处理的问题。预约者通过网站预约参加会议,管理者可以变更会议参加的人数额度及删除预约者,这时候,就产生了数据同步的问题:预约者和管理者同时变更数据,可能造成一方拿到“脏数据”,而使得系统产生差错。下面就是加锁方法: SELECT [columns] FROM [table] WITH (tablehints) tablehints的可用参数如下: HOLDLOCK 持有共享锁,直到整个事务完成,应该在被锁对象不需要时立即释放,等于SERIALIZABLE事务隔离级别 NOLOCK 语句执行时不发出共享锁,允许脏读,等于READ UNCOMMITTED事务隔离级别 PAGLOCK 在使用一个表锁的地方用多个页锁 READPAST 让sql server跳过任何锁定行,执行事务,适用于READ UNCOMMITTED事务隔离级别只跳过RID锁,不跳过页,区域和表锁 ROWLOCK 强制使用行锁 TABLOCKX 强制使用独占表级锁,这个锁在事务期间阻止任何其他事务使用这个表 UPLOCK 强制在读表时使用更新而不用共享锁 同样也可以通过T-SQL的事务语句或者ADO的控件参数设置事务级别,详细请看下面链接。 参考:http://www./database/mssql/jqapp/159.htm 还有一篇http://bbs.ustc.edu.cn/cgi/bbscon?bn=DataBase&fn=M41f36292&num=3378
对应的,Oracle中比较常用的方法: SELECT [columns] FROM [table] FOR UPDATE [NOWAIT]
- MSSQLServer中的集计运算
接触了一个新的系统,中间运用了很多集计运算,却没有用平常使用的SUM,COUNT函数,而是用了一个GROUP BY的关键字WITH ROLLUP/CUBE,加上这个关键字,将会对分组后的数据按照层次进行集计运算,并将数据直接付在所集计数据之后,对于报表数据的处理有很大的帮助。对于ROOLUP和CUBE的区别在于,ROLLUP按分组层次来运算,而CUBE是按所有可能的组合来运算。下面给个例子,数据如下: Item Color Quantity -------------------- -------------------- -------------------------- Table Blue 124 Table Red 223 Chair Blue 101 Chair Red 210 使用集计运算SQL,中间的CASE语句是为了去掉集计产生的NULL值,可以去掉看一下有什么区别。 SELECT CASE WHEN (GROUPING(Item) = 1) THEN ‘ALL‘ ELSE ISNULL(Item, ‘UNKNOWN‘) END AS Item, CASE WHEN (GROUPING(Color) = 1) THEN ‘ALL‘ ELSE ISNULL(Color, ‘UNKNOWN‘) END AS Color, SUM(Quantity) AS QtySum FROM Inventory GROUP BY Item, Color WITH ROLLUP 产生结果集: Item Color QtySum -------------------- -------------------- -------------------------- Chair Blue 101.00 Chair Red 210.00 Chair ALL 311.00 Table Blue 124.00 Table Red 223.00 Table ALL 347.00 ALL ALL 658.00 如果使用WITH CUBE,那么结果集还会多出下面两行记录 ALL Blue 225.00 ALL Red 433.00 注意观察,红色的部分,是ROLLUP对Color分组层进行运算的结果,绿色部分则是对Item层的,而CUBE会将Color与Item的任意一种组合都进行运算。是不是很方便呢。
- 数据库中的Collate
Collate关键字指定了数据库使用的效验方式。简单来说,就是数据的比较规则,比如大小写敏感,长度敏感等等,Collate可以在建数据库、表、甚至在SQL实行时指定。这次遇到的问题是因为比较2个数据库中表字段,但2个数据库使用的默认Collate不一样,导致类型转换错误,比较失败,SQL报错。通常不太会注意这类问题,但是遇到导入或者分布式的数据库时或许会出现问题。 下面附带查询默认数据库以外的SQL语句 SELECT 字段名 FROM [数据库名].[表所有者名].表名 比如:SELECT TA.C1, TB.C2 FROM TableA TA , otherdb.dbo.TableB TB 其中,表所有者名可以省略,变为otherdb..TableB,当然,当前连接用户要有访问otherdb数据库的权限。
|