HoneyHoney: August 2014

Optional parameter in SQL - WHERE (@param IS NULL OR col = @param)

Posted on Wednesday, August 27, 2014 by 醉·醉·鱼 and labeled under sql

早上例会提到了这么一个事情，大概知道老大说的什么，但是不是很明白。回来做了一个测试，用WHERE (@param IS NULL OR col = @param) 的确会比较糟糕。放狗一搜，文章不是很多，好在也能够找到几篇文章提到了这个。

这个文章算是比较全面的，里面提到了两个解决方案，都还不错。
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

问题

如下图所示，当用到WHERE (@param IS NULL OR col = @param) 的时候，会用到Index Scan，即使你在last_name上定义好了index。

解决方案

就现在而言，有两种解决方式。

1. OPTION（RECOMPILE）（SQL 2008 SP1/SP2/Later）

2. Dynamical SQL

可以改成

DECLARE @sql nvarchar(2000) = N'';

IF @param IS NOT NULL

SET @sql = @sql + 'select * from people p where p.last_name = @_lastName'

EXEC sys.sp_executesql @sql, N'@_lastName varchar(255)', @_lastName = @param

慎用Pivot

Posted on Tuesday, August 26, 2014 by 醉·醉·鱼 and labeled under pivot , sql

刚刚瞄了一眼http://www.sqlpassion.at/archive/2014/08/25/the-dangerous-beauty-of-the-pivot-operator-in-sql-server/?awt_l=KDBpM&awt_m=3eQDtEE5mzYUUTS 有点危言耸听了。其实里面就讲到一个东西，那就是pivot是如何去做grouping的。

借用http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx 的例子。先创建如下table和数据。

create table DailyIncome(VendorId nvarchar(10), IncomeDay nvarchar(10), IncomeAmount int)

--drop table DailyIncome

insert into DailyIncome values ('SPIKE', 'FRI', 100)

insert into DailyIncome values ('SPIKE', 'MON', 300)

insert into DailyIncome values ('FREDS', 'SUN', 400)

insert into DailyIncome values ('SPIKE', 'WED', 500)

insert into DailyIncome values ('SPIKE', 'TUE', 200)

insert into DailyIncome values ('JOHNS', 'WED', 900)

insert into DailyIncome values ('SPIKE', 'FRI', 100)

insert into DailyIncome values ('JOHNS', 'MON', 300)

insert into DailyIncome values ('SPIKE', 'SUN', 400)

insert into DailyIncome values ('JOHNS', 'FRI', 300)

insert into DailyIncome values ('FREDS', 'TUE', 500)

insert into DailyIncome values ('FREDS', 'TUE', 200)

insert into DailyIncome values ('SPIKE', 'MON', 900)

insert into DailyIncome values ('FREDS', 'FRI', 900)

insert into DailyIncome values ('FREDS', 'MON', 500)

insert into DailyIncome values ('JOHNS', 'SUN', 600)

insert into DailyIncome values ('SPIKE', 'FRI', 300)

insert into DailyIncome values ('SPIKE', 'WED', 500)

insert into DailyIncome values ('SPIKE', 'FRI', 300)

insert into DailyIncome values ('JOHNS', 'THU', 800)

insert into DailyIncome values ('JOHNS', 'SAT', 800)

insert into DailyIncome values ('SPIKE', 'TUE', 100)

insert into DailyIncome values ('SPIKE', 'THU', 300)

insert into DailyIncome values ('FREDS', 'WED', 500)

insert into DailyIncome values ('SPIKE', 'SAT', 100)

insert into DailyIncome values ('FREDS', 'SAT', 500)

insert into DailyIncome values ('FREDS', 'THU', 800)

insert into DailyIncome values ('JOHNS', 'TUE', 600)

数据应该是这样的。

VendorId IncomeDay IncomeAmount

---------- ---------- ------------

SPIKE FRI 100

SPIKE MON 300

FREDS SUN 400

SPIKE WED 500

SPIKE TUE 200

JOHNS WED 900

SPIKE FRI 100

JOHNS MON 300

SPIKE SUN 400

...

SPIKE WED 500

FREDS THU 800

JOHNS TUE 600

执行如下query，就可以进行一次简单的pivot操作。

select * from DailyIncome

pivot (avg (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as AvgIncomePerDay

但是，如果，这个时候DailyIncome table多增加了一个column，那情况就不会是这样的了。像http://www.sqlpassion.at/archive/2014/08/25/the-dangerous-beauty-of-the-pivot-operator-in-sql-server/?awt_l=KDBpM&awt_m=3eQDtEE5mzYUUTS 说的那样，得到的结果就不会像想象中的那样了。原因就在于pivot里面会用除了IncomeDay和IncomeAmount的column进行group操作，进而导致了pivot的结果显示的乱糟糟的。

解决方法

将DailyIncome换成Table express，即

select * from (select VendorId, IncomeDay, IncomeAmount from DailyIncome) AS t

pivot (avg (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as AvgIncomePerDay

这个问题可能会出现的比较隐蔽些，因为table的改动很难想到会影响pivot操作。

Tipping Point In SQL SERVER

Posted on Monday, August 25, 2014 by 醉·醉·鱼 and labeled under sql , tipping point

原文在这里http://www.sqlpassion.at/archive/2013/06/12/sql-server-tipping-games-why-non-clustered-indexes-are-just-ignored/，我顺带翻译成中文吧。

有时候，SQL SERVER会忽略掉定义好的Non-Clustered Index，而选用Clustered Index Scan，这种行为在SQL SERVER里面叫做‘Tipping Point’。而且，最重要的是，这个Tipping Point和Table Page有关，而和Table records无关。Tipping point大概在24%-33%之间。

开始如下测试。创建一个Table，每条记录会有40bytes，每个page会有200条记录。

插入80000条记录。所以会有400个page。所以Tipping point大概是在100 - 133 之间（实际上这个测试里面会高于这个值），那相当于说，用NonClustered Index的话，你只能够取到这张表0.125% - 0.167%的记录。这相当于还不用这个Index。

实际上这次的Tipping Point会在158。如下，第一个query会用到NonClustered Index，而第二个query直接用Clustered Index Scan了。

执行计划

更加有趣的是，如果我强制使用NonClustered Index，下面的这段query将会达到165148！这可是只有420page的table啊！

嘟嘟 - 我要飞得更高

Posted on Sunday, August 24, 2014 by 醉·醉·鱼 and labeled under 嘟嘟

Multiple tab rows in SSMS

Posted on Thursday, August 21, 2014 by 醉·醉·鱼 and labeled under SSMS

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

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

创建动态PIVOT TABLE

Posted on Thursday, August 14, 2014 by 醉·醉·鱼 and labeled under sql

问题

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

解决

当数据量比较小的时候，可以直接用下面的SQL
但是当数据量比较多时候，你需要创建动态query去实现。麻烦的地方就是动态生成那一串product_id。

QUOTENAME(product_id) 将product_id用标识符包括起来，即[38252013].
', ' + QUOTENAME(product_id) 在每个product_id前面加一个逗点，即', [38252013]'。方便后面拼接用。
FOR XML PATH将返回结果拼成一行，即<row>, [38252013]</row><row>, [38252113]</row>...
FOR XML PATH将结果的节点名去掉，即, [38252013], [38252113]...
STUFF((), 1, 2, '')将第一个逗点去掉。
注意: 参考http://stackoverflow.com/questions/12639948/sql-nvarchar-and-varchar-limits，避免动态query被截断。

剩下就是拼接成为PIVOT TABLE的query了。用下面的query拼接起来，依旧要避免被截断。

注：

根据 http://support.microsoft.com/kb/310378，SELECT ID FROM A FOR XML PATH 返回的结果会被截断到2033字符。
在EXEC(@sql)之后，里面创建的临时表会自动被drop掉，这也就意味着你没法继续在后续query中引用这个临时表。一个解决方法是创建全局临时表，即##TABLE。
在用户这个问题里面，显示[38252013]是没有什么意义的。所以还需要把这些column给重命名。这里用了一个游标，在用tempdb..sp_rename去重命名。具体如下。

Colorblind - Carie Vejvoda

Posted on Wednesday, August 13, 2014 by 醉·醉·鱼 and labeled under music

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

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.....

"45 Database Performance Tips for Developers"

Posted on Tuesday, August 12, 2014 by 醉·醉·鱼 and labeled under sql

同事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。

SQL SERVER中varchar和nvarchar存储区别（以及部分Collation & Unicode）

Posted on Monday, August 04, 2014 by 醉·醉·鱼 and labeled under sql

本来想查查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

HoneyHoney

醉醉鱼·涂鸦志