0
Posted on Wednesday, October 22, 2014 by 醉·醉·鱼 and labeled under


[Source Information]
Source Location : .
Source Provider : SQLNCLI11
Table: [Query]
Column: VariableCharacterField
Column Type: 200
SSIS Type: (Type unknown ...)
Mapping file (to SSIS type): C:\Program Files (x86)\Microsoft SQL Server\110\DTS\MappingFiles\MSSQLToSSIS10.XML

官方已经在SQL SERVER 2012 SP2里面修好了问题。不想升级的话,有个work around就是自己去更改MSSQLToSSIS10.XML文件。具体见
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/97ff1f01-c02a-4c9a-b867-8eaecc464cfb/2012-sp1-no-longer-recognizes-common-data-types?forum=sqlintegrationservices

官方文档https://connect.microsoft.com/SQLServer/feedback/details/772761/dtswizard-in-sql-2012-sp1-no-longer-recognizes-nvarchar-varchar-data-types-when-source-is-a-query
0
Posted on Monday, October 20, 2014 by 醉·醉·鱼 and labeled under
源于一次数据库的面试题目,老板对别人的答题给了90分,我看了一下,没有看出理由。后来查了一下,原来如此。

简而言之,LEFT OUTER JOIN是先进行JOIN,拿出所有match的records,然后再做过滤。而NOT EXISTS是一旦有匹配就进行下一个比对。

这里有很详细的解释。
http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/
http://stackoverflow.com/questions/6777910/sql-performance-on-left-outer-join-vs-not-exists

0
Posted on Monday, October 20, 2014 by 醉·醉·鱼 and labeled under
从SQL SERVER 2012开始,Alter table column from NULL to NOT NULL with default value is metadata change.

http://msdn.microsoft.com/en-us/library/ms190273.aspx

Adding NOT NULL Columns as an Online Operation

Starting with SQL Server 2012 Enterprise Edition, adding a NOT NULL column with a default value is an online operation when the default value is a runtime constant. This means that the operation is completed almost instantaneously regardless of the number of rows in the table. This is because the existing rows in the table are not updated during the operation; instead, the default value is stored only in the metadata of the table and the value is looked up as needed in queries that access these rows. This behavior is automatic; no additional syntax is required to implement the online operation beyond the ADD COLUMN syntax. A runtime constant is an expression that produces the same value at runtime for each row in the table regardless of its determinism. For example, the constant expression "My temporary data", or the system function GETUTCDATETIME() are runtime constants. In contrast, the functions NEWID() or NEWSEQUENTIALID() are not runtime constants because a unique value is produced for each row in the table. Adding a NOT NULL column with a default value that is not a runtime constant is always performed offline and an exclusive (SCH-M) lock is acquired for the duration of the operation.
除了文中说的例外,data compression也会有影响的。所以,还是得小心啊。
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6784c3d8-2e40-47fd-956a-910c576a2266/changing-column-in-not-null-why-is-there-so-much-transaction-log-activity-on-compressed-tables?forum=sqldatabaseengine

此外,它依旧还是会有3个操作,所以http://dba.stackexchange.com/a/29526/37769 里面描述的依旧有效。

  1. 创建一个新的column
  2. 为新的column赋值
  3. 将前一个column标记dropped