0

The conversion of the varchar value '10000000000000' overflowed an int column.

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

INSERT and OUTPUT 避免could not be bound错误

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

SQL SERVER 2016 COMPRESS/DECOMPRESS feature

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太差了）。

0

嘟嘟，粑粑想你了

Posted on Wednesday, July 27, 2016 by 醉·醉·鱼 and labeled under
嘟嘟，粑粑想你了。

没有想到，这一别，你就已经10个月没有回成都了。心里一酸，不知道你还能不能够回成都了。

0

Spring annotatoin injection

Posted on Monday, June 27, 2016 by 醉·醉·鱼 and labeled under


@Autowired annotation is picked up only if you use  or

http://stackoverflow.com/questions/7414794/difference-between-contextannotation-config-vs-contextcomponent-scan

is used to activate annotations in beans already registered in the application context (no matter if they were defined with XML or by package scanning).
can also do what does but also scans packages to find and register beans within the application context.
I'll use some examples to show the differences/similarities.
Lets start with a basic setup of three beans of type A, B and C, with B and C being injected into A. 
package com.xxx;
public class B {
  public B() {
    System.out.println("creating bean B: " + this);
  }
}

package com.xxx;
public class C {
  public C() {
    System.out.println("creating bean C: " + this);
  }
}

package com.yyy;
import com.xxx.B;
import com.xxx.C;
public class A { 
  private B bbb;
  private C ccc;
  public A() {
    System.out.println("creating bean A: " + this);
  }
  public void setBbb(B bbb) {
    System.out.println("setting A.bbb with " + bbb);
    this.bbb = bbb;
  }
  public void setCcc(C ccc) {
    System.out.println("setting A.ccc with " + ccc);
    this.ccc = ccc; 
  }
}
With the following XML configuration : 
<bean id="bBean" class="com.xxx.B" />
<bean id="cBean" class="com.xxx.C" />
<bean id="aBean" class="com.yyy.A">
  <property name="bbb" ref="bBean" />
  <property name="ccc" ref="cBean" />
</bean>
Loading the context produces the following output: 
creating bean B: com.xxx.B@c2ff5
creating bean C: com.xxx.C@1e8a1f6
creating bean A: com.yyy.A@1e152c5
setting A.bbb with com.xxx.B@c2ff5
setting A.ccc with com.xxx.C@1e8a1f6
OK, this is the expected output. But this is "old style" Spring. Now we have annotations so lets use those to simplify the XML.
First, lets autowire the bbb and ccc properties on bean A like so: 
package com.yyy;
import org.springframework.beans.factory.annotation.Autowired;
import com.xxx.B;
import com.xxx.C;
public class A { 
  private B bbb;
  private C ccc;
  public A() {
    System.out.println("creating bean A: " + this);
  }
  @Autowired
  public void setBbb(B bbb) {
    System.out.println("setting A.bbb with " + bbb);
    this.bbb = bbb;
  }
  @Autowired
  public void setCcc(C ccc) {
    System.out.println("setting A.ccc with " + ccc);
    this.ccc = ccc;
  }
}
This allows me to remove the following rows from the XML: 
<property name="bbb" ref="bBean" />
<property name="ccc" ref="cBean" />
My XML is now simplified to this: 
<bean id="bBean" class="com.xxx.B" />
<bean id="cBean" class="com.xxx.C" />
<bean id="aBean" class="com.yyy.A" />
When I load the context I get the following output: 
creating bean B: com.xxx.B@5e5a50
creating bean C: com.xxx.C@54a328
creating bean A: com.yyy.A@a3d4cf
OK, this is wrong! What happened? Why aren't my properties autowired?
Well, annotations are a nice feature but by themselves they do nothing whatsoever. They just annotate stuff. You need a processing tool to find the annotations and do something with them.
to the rescue. This activates the actions for the annotations that it finds on the beans defined in the same application context where itself is defined.
If I change my XML to this: 
<context:annotation-config />
<bean id="bBean" class="com.xxx.B" />
<bean id="cBean" class="com.xxx.C" />
<bean id="aBean" class="com.yyy.A" />
when I load the application context I get the proper result: 
creating bean B: com.xxx.B@15663a2
creating bean C: com.xxx.C@cd5f8b
creating bean A: com.yyy.A@157aa53
setting A.bbb with com.xxx.B@15663a2
setting A.ccc with com.xxx.C@cd5f8b
OK, this is nice, but I've removed two rows from the XML and added one. That's not a very big difference. The idea with annotations is that it's supposed to remove the XML.
So let's remove the XML definitions and replace them all with annotations: 
package com.xxx;
import org.springframework.stereotype.Component;
@Component
public class B {
  public B() {
    System.out.println("creating bean B: " + this);
  }
}

package com.xxx;
import org.springframework.stereotype.Component;
@Component
public class C {
  public C() {
    System.out.println("creating bean C: " + this);
  }
}

package com.yyy;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import com.xxx.B;
import com.xxx.C;
@Component
public class A { 
  private B bbb;
  private C ccc;
  public A() {
    System.out.println("creating bean A: " + this);
  }
  @Autowired
  public void setBbb(B bbb) {
    System.out.println("setting A.bbb with " + bbb);
    this.bbb = bbb;
  }
  @Autowired
  public void setCcc(C ccc) {
    System.out.println("setting A.ccc with " + ccc);
    this.ccc = ccc;
  }
}
While in the XML we only keep this: 
<context:annotation-config />
We load the context and the result is... Nothing. No beans are created, no beans are autowired. Nothing!
That's because, as I said in the first paragraph, the only works on beans registered within the application context. Because I removed the XML configuration for the three beans there is no bean created and has no "targets" to work on.
But that won't be a problem for which can scan a package for "targets" to work on. Let's change the content of the XML config into the following entry: 
<context:component-scan base-package="com.xxx" />
When I load the context I get the following output: 
creating bean B: com.xxx.B@1be0f0a
creating bean C: com.xxx.C@80d1ff
Hmmmm... something is missing. Why?
If you look closelly at the classes, class A has package com.yyy but I've specified in the to use package com.xxx so this completely missed my A class and only picked up B and C which are on the com.xxx package.
To fix this, I add this other package also: 
<context:component-scan base-package="com.xxx,com.yyy" />
and now we get the expected result: 
creating bean B: com.xxx.B@cd5f8b
creating bean C: com.xxx.C@15ac3c9
creating bean A: com.yyy.A@ec4a87
setting A.bbb with com.xxx.B@cd5f8b
setting A.ccc with com.xxx.C@15ac3c9
And that's it! Now you don't have XML definitions anymore, you have annotations.
As a final example, keeping the annotated classes A, B and C and adding the following to the XML, what will we get after loading the context? 
<context:component-scan base-package="com.xxx" />
<bean id="aBean" class="com.yyy.A" />
We still get the correct result: 
creating bean B: com.xxx.B@157aa53
creating bean C: com.xxx.C@ec4a87
creating bean A: com.yyy.A@1d64c37
setting A.bbb with com.xxx.B@157aa53
setting A.ccc with com.xxx.C@ec4a87
Even if the bean for class A isn't obtained by scanning, the processing tools are still applied by on all beans registered in the application context, even for A which was manually registered in the XML.
But what if we have the following XML, will we get duplicated beans because we've specified both and ? 
<context:annotation-config />
<context:component-scan base-package="com.xxx" />
<bean id="aBean" class="com.yyy.A" />
No, no duplications, We again get the expected result: 
creating bean B: com.xxx.B@157aa53
creating bean C: com.xxx.C@ec4a87
creating bean A: com.yyy.A@1d64c37
setting A.bbb with com.xxx.B@157aa53
setting A.ccc with com.xxx.C@ec4a87
That's because both tags register the same processing tools ( can be omitted if is specified) but Spring takes care of running them only once.
Even if you register the processing tools yourself multiple times, Spring will still make sure they do their magic only once; this XML: 
<context:annotation-config />
<context:component-scan base-package="com.xxx" />
<bean id="aBean" class="com.yyy.A" />
<bean id="bla" class="org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor" />
<bean id="bla1" class="org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor" />
<bean id="bla2" class="org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor" />
<bean id="bla3" class="org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor" />
will still generate the following result: 
creating bean B: com.xxx.B@157aa53
creating bean C: com.xxx.C@ec4a87
creating bean A: com.yyy.A@25d2b2
setting A.bbb with com.xxx.B@157aa53
setting A.ccc with com.xxx.C@ec4a87
OK, that about raps it up.

I hope this information along with the responses from @Tomasz Nurkiewicz and @Sean Patrick Floyd are all you need to understand how and work.