HoneyHoney

SSMS很讨厌的不支持multiple tab rows，而我又习惯性地开上十几个tabs，找tab成了一个痛苦的事情。最后，我找到这么一个插件，看上去不错，就是新开标签的时候有点慢。

http://visualstudiogallery.msdn.microsoft.com/EBF6137E-AA2D-4DC9-860A-F04168F11CD7

问题

有如下一张表，需要创建一个PIVOT表，用来显示每个registration买了多少个product。

解决

1. 当数据量比较小的时候，可以直接用下面的SQL
   
   
2. 但是当数据量比较多时候，你需要创建动态query去实现。麻烦的地方就是动态生成那一串product_id。
1. QUOTENAME(product_id) 将product_id用标识符包括起来，即[38252013].
2. ', ' + QUOTENAME(product_id) 在每个product_id前面加一个逗点，即', [38252013]'。方便后面拼接用。
3. FOR XML PATH将返回结果拼成一行，即<row>, [38252013]</row><row>, [38252113]</row>...
4. FOR XML PATH将结果的节点名去掉，即, [38252013], [38252113]...
5. STUFF((), 1, 2, '')将第一个逗点去掉。
6. 注意: 参考http://stackoverflow.com/questions/12639948/sql-nvarchar-and-varchar-limits， 避免动态query被截断。
   
   
3. 剩下就是拼接成为PIVOT TABLE的query了。用下面的query拼接起来，依旧要避免被截断。
   

本来是生活，却用歌唱出。

Breathe it and breathe it out, like it's all suddenly forgot
I stopped hanging onto a single thread when I realized that it was meant to end.
I started to feel inside that I was forcing it, if he loved me back then why didn't I feel it?
Scared of facing the world on my own again I chose to live a life that was all pretend.
I wanted it so bad I gave it everything I had

(chorus)
I must be colorblind, didn't see the red flag right in front of my eyes
I must be colorblind, to think the grass was greener on the other side

Held on tight and it crumbled, I couldn't force my life into the right mold
Tried to be the person I thought I should be, instead of giving people a glimpse of the real me
I wanted my life to look picture perfect, afraid to take a risk and really live it
I looked at everybody else with envy, instead of looking in the mirror and seeing me

(chorus)
Disillusioned by the, the glitz and glamour, the lights, the cameras distract from what's really there
I must be colorblind, didn't see the red flag right in front of my eyes
 oo woah oh woah oh woah oh- Open my eyes and see
oo woah oh woah oh woah oh-- my natural instinct, 
The feelin, I'm gettin, inside when I know that somethin just 'aint right
Didn't see the red flag right before my eyes, I must be colorblind, to think the grass was greener
(chorus)
Disillusioned by the, the glitz and glamour, the lights, the cameras distract from what's really there
I must be colorblind, didn't see the red flag right in front of my eyes...
I must be colorblind.....

同事share了一本书，自己瞄了一眼，觉得不怎么样，就搁在一边了。老板后来会上提到这本书，然后挑了几个tip讲解一下，顿时豁然开朗。回来重新读这本书，很大部分读不懂，仅列出自己略微有点概念的部分。

为什么SELECT *会很糟糕？因为这意味着更多的数据，更多的IO和网络消耗。
公司项目里面有个订单管理系统，这个订单管理系统的瓶颈常常在于库存量更新的时候。当有很多人来注册的时候，会有很多订单，这些订单都需要去更新库存量，导致了后面数据库这边非常得慢。不知道把这个库存量存到application里面，到一定时间的时候再回写到数据库里面（有点像transaction log了），这样的话系统会不会好一点。

最近刚好遇到一个例子。之前的query里面用了一个游标，每个游标又去调一个存储过程，那简直是想死的心都有了。直接把那个存储过程里面的query剥离出来，然后去掉游标，性能哗啦啦从30+秒到10秒以内。当然我有时候还是会用游标，比如逐行更新记录，每条记录又都不一样的时候，但是还是尽量避免。

暂时不能够理解为什么，因为还没有遇到过。这也是这本书的缺点，缺乏足够的例子去阐述这些观点，这就需要别人需要有足够的经验才能够理解书里面的东西。

UNION会排序并去重，不是简单去重。曾经做过数据修复，数据修复的时候，需要找出需要修复的数据，而这些数据的顺序是不会影响后续过程的。我习惯性地加了一个ORDER BY，被DBA发现以后支出这个是没有必要的。


这个会有点有趣。这里有个详细的文章讨论Table variable: http://sqlhints.com/2014/02/10/difference-between-temporary-table-and-table-variable-in-sql-server/ 由于table variable上面没有statistics，SQL会认为它只有一条记录，对这个表操作都是table scan。数据小，表关联少的情况下，情况不是很严重，但是如果表关联多的时候，这个将是performance糟糕的原因。

这里有个局限，一次INSERT最多只能够有1000个VALUES。
PK上面创建clustered index是默认behavior，但是FK上面却不会自动创建index。一般情况下，我们是需要对FK创建index，除了一些情况，比如这张表很小。
再次建议读一次 http://sqlhints.com/2014/02/10/difference-between-temporary-table-and-table-variable-in-sql-server/。Table variable 同样可以创建index。

本来想查查varchar和nvarchar在存储上面的区别的，结果走远了。。。

书上说过，nvarchar可以存储unicode data，但是每个字符需要两个字节。varchar则只需要一个字节。对于英文字符，varchar足够了，但是系统做大了，一般都会i18n（国际化），所以需要用nvarchar。网上很多人都推荐nvarchar，至少这样可以减少各种转换之间的痛苦。

1. 那在存储上，到底有什么特殊的地方么？


创建如上table，结果如下。
可以看到，char和varchar不支持中文，显示为两个问号，即乱码了。下面会说为什么会乱码。
那具体的存储上面的区别呢？

执行如下命令，可以发现，char(50)是固定长度，varchar(50)是根据数据的实际长度，nvarchar(50)则是varchar(50)的两倍。中文依旧乱码，除了nvarchar.



2. 如果进行row data compression呢？数据长度则被很好的压缩了。

3. 为什么会乱码呢？因为在插入的时候，系统会自动把字符转换成为column所对应的类型(以及Collation)。然而，'中国'会被转换成为not defined（因为在对应的code page上面找不到），即0x3f3f。而0x3f就是问号，所以查询出来的就是问号。如果只知道问号，是没有办法查到原来的数据的，即垃圾数据。
4. 那如何确保中文能够正确存进去呢？有两种方法，一是用nvarchar或者nchar，二是用正确的collation。如下图，ItemName3是nvarchar, ItemName4是varchar with collation Chinese_PRC_CI_AS。


5. 那ItemName3和ItemName4在存储上面有什么不同呢？用上面的命令可以发现，ItemName3存储的是2d4e fd56，对应的是unicode table上面的4e2d 56fd。ItemName4存储的是d6d0 b9fa，对应的是gb2312上面的d6d0 b9fa。所以，varchar是支持中文的，只要你的collation是支持中文的(database, table, column level都可以设置collation）。





http://unicode-table.com/en/#4E2D
http://ash.jp/code/cn/gb2312tbl.htm

故事源于最近boss在review script的时候提到了Data compression, 我查了一下，别人的介绍几乎都是

ALTER TABLE dbo.Table REBUILD WITH (DATA_COMPRESSION = PAGE);

但是boss却只是在Clustered Index（一般是primary key）上面做了data compression. 我还以为对表和对clustered index做data compression是两回事，而boss只是估计只在index上面做compression。而事实上，他们其实是一回事。查了一下，别人说是一样的。http://dba.stackexchange.com/questions/49757/clustered-index-compression-vs-table-compression-are-they-the-same-thing

测试一下，创建一张表

对clustered index加个data compression page.
验证一下
Drop掉primary key，对表进行row data compression，再试一次。


结论是，两者是一样的效果。其实也可以通过SSMS右键查看属性确认。

都说index越多，insert的语句越慢。无图无真相。

那么执行下面的query，你就能够看到第一个query cost是80%，第二个query cost是20%。