0

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

Posted on Wednesday, August 27, 2014 by 醉·醉·鱼 and labeled under
早上例会提到了这么一个事情，大概知道老大说的什么，但是不是很明白。回来做了一个测试，用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


0

慎用Pivot

Posted on Tuesday, August 26, 2014 by 醉·醉·鱼 and labeled under ,
刚刚瞄了一眼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操作。

0

Tipping Point In SQL SERVER

Posted on Monday, August 25, 2014 by 醉·醉·鱼 and labeled under ,
原文在这里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啊！



0

嘟嘟 - 我要飞得更高

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


0

Multiple tab rows in SSMS

Posted on Thursday, August 21, 2014 by 醉·醉·鱼 and labeled under
SSMS很讨厌的不支持multiple tab rows，而我又习惯性地开上十几个tabs，找tab成了一个痛苦的事情。最后，我找到这么一个插件，看上去不错，就是新开标签的时候有点慢。

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


0

创建动态PIVOT TABLE

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

问题

有如下一张表，需要创建一个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拼接起来，依旧要避免被截断。
注：

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

0

Colorblind - Carie Vejvoda

Posted on Wednesday, August 13, 2014 by 醉·醉·鱼 and labeled under
本来是生活，却用歌唱出。

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