0
Posted on Wednesday, August 31, 2016 by 醉·醉·鱼 and labeled under
时间长了,总能够碰见一些奇葩的case。比如有一个字段是ID,但是数据类型是VARCHAR。这里就潜在问题了。当数据比较小的时候都不会有问题,一旦数据比较大的时候,就会出现下面这个错误。

Msg 248, Level 16, State 1, Line 1
The conversion of the varchar value '10000000000000' overflowed an int column.

可以通过下面一段代码来重现这个问题。


CREATE TABLE #TMP(id varchar(50))

INSERT INTO #TMP (ID) VALUES (10), (10000000000000)

SELECT * FROM #TMP WHERE ID = 10
SELECT * FROM #TMP WHERE ID = 10000000000000

错误就在第一个SELECT的WHERE ID = 10上。默认情况下,SQL SERVER会把varchar转换成为右边的数据类型INT,当试图转换10000000000000就溢出了。所以,你要么显示转换,要么老老实实用正确的数据类型。
0
Posted on Wednesday, August 31, 2016 by 醉·醉·鱼 and labeled under
碰巧遇到一个案例,是需要把一个表的部分数据复制到另外一张表,同时把新生成的ID和原来表的ID都要记录下来。

一种方法是用游标,遍历第一个表,逐条插入。5000条记录还是算快的。但是如果数据量特别大的时候,游标就真心慢了。
另外一种方法就是INSERT INTO...SELECT,再用OUTPUT输出两个ID。 我尝试如下方法:



CREATE TABLE #CUSTOMERS (
ID INT IDENTITY(1,1),
NAME VARCHAR(50),
MEMBERSHIP_ID INT
)

CREATE TABLE #MEMBERSHIPS (
ID INT IDENTITY(1,1),
NAME VARCHAR(50)
)

CREATE TABLE #TMP(
CUSTOMER_ID INT,
MEMBERSHIP_ID INT
)

INSERT INTO #CUSTOMERS(NAME) VALUES ('pHoEnIx'), ('ErIk')

INSERT INTO #MEMBERSHIPS(NAME)
OUTPUT c.ID, INSERTED.ID
INTO #TMP
SELECT c.NAME
FROM #CUSTOMERS c
 
----------------------------------------------
--你会得到下面的错误信息
--Msg 4104, Level 16, State 1, Line 5
--The multi-part identifier "c.ID" could not be bound.
--通过http://sqlblog.com/blogs/jamie_thomson/archive/2010/01/06/merge-and-output-the-swiss-army-knife-of-t-sql.aspx
--你可以通过使用MERGE避免这个问题
----------------------------------------------

MERGE #MEMBERSHIPS
USING (SELECT ID, NAME FROM #CUSTOMERS) AS src
ON (1 = 0) --随意不匹配
WHEN NOT MATCHED THEN
    INSERT (NAME)
    VALUES (src.NAME)
    OUTPUT src.ID, INSERTED.ID
    INTO #TMP;



0
Posted on Tuesday, August 30, 2016 by 醉·醉·鱼 and labeled under ,
SQL SERVER 2016出了一个新功能,就是COMPRESS/DECOMPRESS。运用的是GZIP算法,针对LOB这种类型的数据很有效果。

如下图,创建3个表并插入数据。


直接查看占用的空间,可以看到有无DATA_COMPRESS并没有本质区别。因为DATA_COMPRESSION本来就不支持LOB数据。相反,运用了COMPRESS的压缩率达到了可怕的1.7%。当然实际中压缩率会比这个高。


至于性能上,由于数据压缩了,IO上面的开销很显然会更少,相应地会增加CPU的开销。总体而言,使用了压缩以后读取速度会慢一些(可能是我的CPU太差了)。