-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathindex.html
921 lines (593 loc) · 61.4 KB
/
index.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
<!DOCTYPE html>
<!--[if IEMobile 7 ]><html class="no-js iem7"><![endif]-->
<!--[if lt IE 9]><html class="no-js lte-ie8"><![endif]-->
<!--[if (gt IE 8)|(gt IEMobile 7)|!(IEMobile)|!(IE)]><!--><html class="no-js" lang="en"><!--<![endif]-->
<head>
<meta charset="utf-8">
<title>+1 to BI</title>
<meta name="author" content="Stu Baker">
<meta name="description" content="44 questions, with 2 hours provided to answer the questions and a 700 score needed to pass
To study I primarily used the official training kit ( …">
<!-- http://t.co/dKP3o1e -->
<meta name="HandheldFriendly" content="True">
<meta name="MobileOptimized" content="320">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="canonical" href="http://fountless.github.io">
<link href="/favicon.png" rel="icon">
<link href="/stylesheets/screen.css" media="screen, projection" rel="stylesheet" type="text/css">
<link href="/atom.xml" rel="alternate" title="+1 to BI" type="application/atom+xml">
<script src="/javascripts/modernizr-2.0.js"></script>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script>!window.jQuery && document.write(unescape('%3Cscript src="./javascripts/libs/jquery.min.js"%3E%3C/script%3E'))</script>
<script src="/javascripts/octopress.js" type="text/javascript"></script>
<!--Fonts from Google"s Web font directory at http://google.com/webfonts -->
<link href="http://fonts.googleapis.com/css?family=PT+Serif:regular,italic,bold,bolditalic" rel="stylesheet" type="text/css">
<link href="http://fonts.googleapis.com/css?family=PT+Sans:regular,italic,bold,bolditalic" rel="stylesheet" type="text/css">
<script type="text/javascript">
var _gaq = _gaq || [];
_gaq.push(['_setAccount', 'UA-45959859-1']);
_gaq.push(['_trackPageview']);
(function() {
var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
})();
</script>
</head>
<body >
<header role="banner"><hgroup>
<h1><a href="/">+1 to BI</a></h1>
<h2>business intelligence and web development</h2>
</hgroup>
</header>
<nav role="navigation"><ul class="subscription" data-subscription="rss">
<li><a href="/atom.xml" rel="subscribe-rss" title="subscribe via RSS">RSS</a></li>
</ul>
<form action="http://google.com/search" method="get">
<fieldset role="search">
<input type="hidden" name="q" value="site:fountless.github.io" />
<input class="search" type="text" name="q" results="0" placeholder="Search"/>
</fieldset>
</form>
<ul class="main-navigation">
<li><a href="/">Blog</a></li>
<li><a href="/about">About</a></li>
<li><a href="/blog/archives">Archives</a></li>
</ul>
</nav>
<div id="main">
<div id="content">
<div class="blog-index">
<article>
<header>
<h1 class="entry-title"><a href="/blog/2014/05/01/quick-thoughts-after-taking-microsoft-exam-70-461/">Quick Thoughts After Taking Microsoft Exam 70-461</a></h1>
<p class="meta">
<time datetime="2014-05-01T16:02:08-06:00" pubdate data-updated="true">May 1<span>st</span>, 2014</time>
</p>
</header>
<div class="entry-content"><ul>
<li><p>44 questions, with 2 hours provided to answer the questions and a 700 score needed to pass</p></li>
<li><p>To study I primarily used the official training kit (<a href="http://www.amazon.com/Training-Kit-Exam-70-461-Microsoft/dp/0735666059">Amazon link</a>) and drew from what I learned on the job writing queries for 9 months.</p></li>
<li><p>Learned a lot from the book, but practice tests on the included CD were WORTHLESS in preparing for the test. The only two points they helped on 1) Make sure you pay attention to the actual question asked (e.g. if it asks for a <code>MAX</code> don’t choose the answer with an <code>AVG</code>), and 2) some of the questions/answers deal with vocabulary terms you should become familiar with (transaction isolation levels for me). The thing that bothered me most about the CD practice test: its ease makes you underestimate the real test.</p></li>
<li><p><a href="https://www.microsoft.com/learning/en-us/exam-70-461.aspx#skills">Microsoft’s outline</a> really does a good job of laying out what to expect. The areas I really struggled with were a lot of things I use infrequently or have never used: Triggers, transaction isolation levels, XML output. I’ve pasted in the MS study outline and <strong>bolded</strong> parts that I think would have helped a bunch had I been more familiar with them.</p></li>
</ul>
<h2>Create database objects (24%)</h2>
<ul>
<li>Create and alter tables using T-SQL syntax (simple statements)
<ul>
<li>Create tables without using the built in tools; ALTER; DROP; ALTER COLUMN; CREATE</li>
</ul>
</li>
<li>Create and alter views (simple statements)
<ul>
<li>Create indexed views; create views without using the built in tools; CREATE, ALTER, DROP</li>
</ul>
</li>
<li>Design views
<ul>
<li>Ensure code non regression by keeping consistent signature for procedure, views and function (interfaces); security implications</li>
</ul>
</li>
<li>Create and modify constraints (simple statements)
<ul>
<li>Create constraints on tables; define constraints; unique constraints; default constraints; primary and foreign key constraints</li>
</ul>
</li>
<li>Create and alter DML triggers.
<ul>
<li><strong>Inserted and deleted tables; nested triggers; types of triggers</strong>; update functions; handle multiple rows in a session; performance implications of triggers</li>
</ul>
</li>
</ul>
<h2>Work with data (27%)</h2>
<ul>
<li>Query data by using SELECT statements
<ul>
<li>Use the ranking function to select top(X) rows for multiple categories in a single query; write and perform queries efficiently using the new (SQL 2005/8->) code items such as synonyms, and joins (<strong>except, intersect</strong>); implement logic which uses dynamic SQL and system metadata; write efficient, technically complex SQL queries, including all types of joins versus the use of derived tables; determine what code may or may not execute based on the tables provided; given a table with constraints, determine which statement set would load a table; use and understand different data access technologies; case versus isnull versus coalesce</li>
</ul>
</li>
<li>Implement sub-queries
<ul>
<li>Identify problematic elements in query plans; pivot and unpivot; apply operator; cte statement; with statement</li>
</ul>
</li>
<li>Implement data types
<ul>
<li>Use appropriate data; understand the uses and limitations of each data type; impact of GUID (newid, newsequentialid) on database performance, when to use what data type for columns</li>
</ul>
</li>
<li>Implement aggregate queries
<ul>
<li><strong>New analytic functions; grouping sets; spatial aggregates; apply ranking functions</strong></li>
</ul>
</li>
<li>Query and manage XML data
<ul>
<li><strong>Understand XML datatypes and their schemas</strong> and interop w/, limitations and restrictions; implement XML schemas and handling of XML data; XML data: how to handle it in SQL Server and when and when not to use it, including XML namespaces; import and export XML; XML indexing</li>
</ul>
</li>
</ul>
<h2>Modify data (24%)</h2>
<ul>
<li>Create and alter stored procedures (simple statements)
<ul>
<li>Write a stored procedure to meet a given set of requirements; branching logic; create stored procedures and other programmatic objects; techniques for developing stored procedures; different types of storeproc result; create stored procedure for data access layer; program stored procedures, triggers, functions with T-SQL</li>
</ul>
</li>
<li>Modify data by using INSERT, UPDATE, and DELETE statements
<ul>
<li>Given a set of code with defaults, constraints, and triggers, determine the output of a set of DDL; <strong>know which SQL statements are best to solve common requirements; use output statement</strong></li>
</ul>
</li>
<li>Combine datasets
<ul>
<li><strong>Difference between UNION and UNION all</strong>; case versus isnull versus coalesce; modify data by using MERGE statements</li>
</ul>
</li>
<li>Work with functions
<ul>
<li>Understand deterministic, non-deterministic functions; <strong>scalar and table values</strong>; apply built-in scalar functions; create and alter user-defined functions (UDFs)</li>
</ul>
</li>
</ul>
<h2>Troubleshoot and optimize (25%)</h2>
<ul>
<li>Optimize queries
<ul>
<li>Understand statistics; read query plans; plan guides; DMVs; hints; statistics IO; dynamic vs. parameterized queries; describe the different join types (HASH, MERGE, LOOP) and describe the scenarios they would be used in</li>
</ul>
</li>
<li>Manage transactions
<ul>
<li>Mark a transaction; understand begin tran, commit, and rollback; implicit vs explicit transactions; <strong>isolation levels</strong>; scope and type of locks; trancount</li>
</ul>
</li>
<li>Evaluate the use of row-based operations vs. set-based operations
<ul>
<li>When to use cursors; <strong>impact of scalar UDFs</strong>; combine multiple DML operations</li>
</ul>
</li>
<li>Implement error handling
<ul>
<li><strong>Implement try/catch/throw</strong>; use set based rather than row based logic; transaction management</li>
</ul>
</li>
</ul>
</div>
</article>
<article>
<header>
<h1 class="entry-title"><a href="/blog/2014/03/28/t-sql-tips-transition-school-to-industry/">T-SQL Tips for Transitioning From School to Industry</a></h1>
<p class="meta">
<time datetime="2014-03-28T15:53:57-06:00" pubdate data-updated="true">Mar 28<span>th</span>, 2014</time>
</p>
</header>
<div class="entry-content"><p>It’s been 9 months since I’ve gone from a pure student who had only learned T-SQL in a classroom to someone who uses it everyday as a part of my job. In that time I’ve learned a few lessons that I think are worth sharing.</p>
<p>How helpful this will be to you will of course depend upon the way your program chose to teach this subject, as well as your previous background experience.</p>
<h2>Conditions on JOINs</h2>
<p>With a standard <code>INNER JOIN</code>, you can list additional conditions after the <code>INNER JOIN</code> or in the <code>WHERE</code> clause and you’ll have the same query generating the same results:</p>
<figure class='code'><figcaption><span></span></figcaption><div class="highlight"><table><tr><td class="gutter"><pre class="line-numbers"><span class='line-number'>1</span>
<span class='line-number'>2</span>
<span class='line-number'>3</span>
<span class='line-number'>4</span>
<span class='line-number'>5</span>
<span class='line-number'>6</span>
<span class='line-number'>7</span>
<span class='line-number'>8</span>
<span class='line-number'>9</span>
<span class='line-number'>10</span>
<span class='line-number'>11</span>
</pre></td><td class='code'><pre><code class='sql'><span class='line'><span class="c1">--Conditions in the WHERE clause</span>
</span><span class='line'><span class="k">SELECT</span> <span class="o">*</span>
</span><span class='line'><span class="k">FROM</span> <span class="n">People</span> <span class="k">AS</span> <span class="n">p</span>
</span><span class='line'> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">Addresses</span> <span class="k">AS</span> <span class="n">a</span> <span class="k">ON</span> <span class="n">p</span><span class="p">.</span><span class="n">PeopleID</span> <span class="o">=</span> <span class="n">a</span><span class="p">.</span><span class="n">PeopleID</span>
</span><span class='line'><span class="k">WHERE</span> <span class="n">p</span><span class="p">.</span><span class="n">LastName</span> <span class="o">=</span> <span class="s1">'Baratheon'</span>
</span><span class='line'>
</span><span class='line'><span class="c1">--Same query/results using conditions on the INNER JOIN</span>
</span><span class='line'><span class="k">SELECT</span> <span class="o">*</span>
</span><span class='line'><span class="k">FROM</span> <span class="n">People</span> <span class="k">AS</span> <span class="n">p</span>
</span><span class='line'> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">Addresses</span> <span class="k">AS</span> <span class="n">a</span> <span class="k">ON</span> <span class="n">p</span><span class="p">.</span><span class="n">PeopleID</span> <span class="o">=</span> <span class="n">a</span><span class="p">.</span><span class="n">PeopleID</span>
</span><span class='line'> <span class="k">AND</span> <span class="n">p</span><span class="p">.</span><span class="n">LastName</span> <span class="o">=</span> <span class="s1">'Baratheon'</span>
</span></code></pre></td></tr></table></div></figure>
<p>However, when working with an <code>OUTER JOIN</code>, conditions in the <code>WHERE</code> clause and conditions on the <code>OUTER JOIN</code> can result in very different results:</p>
<figure class='code'><figcaption><span></span></figcaption><div class="highlight"><table><tr><td class="gutter"><pre class="line-numbers"><span class='line-number'>1</span>
<span class='line-number'>2</span>
<span class='line-number'>3</span>
<span class='line-number'>4</span>
<span class='line-number'>5</span>
<span class='line-number'>6</span>
<span class='line-number'>7</span>
<span class='line-number'>8</span>
<span class='line-number'>9</span>
<span class='line-number'>10</span>
<span class='line-number'>11</span>
</pre></td><td class='code'><pre><code class='sql'><span class='line'><span class="c1">--Conditions in the WHERE clause</span>
</span><span class='line'><span class="k">SELECT</span> <span class="o">*</span>
</span><span class='line'><span class="k">FROM</span> <span class="n">People</span> <span class="k">AS</span> <span class="n">p</span>
</span><span class='line'> <span class="k">LEFT</span> <span class="k">JOIN</span> <span class="n">Addresses</span> <span class="k">AS</span> <span class="n">a</span> <span class="k">ON</span> <span class="n">p</span><span class="p">.</span><span class="n">PeopleID</span> <span class="o">=</span> <span class="n">a</span><span class="p">.</span><span class="n">PeopleID</span>
</span><span class='line'><span class="k">WHERE</span> <span class="n">p</span><span class="p">.</span><span class="n">LastName</span> <span class="o">=</span> <span class="s1">'Baratheon'</span>
</span><span class='line'>
</span><span class='line'><span class="c1">--Very different using conditions on the LEFT JOIN</span>
</span><span class='line'><span class="k">SELECT</span> <span class="o">*</span>
</span><span class='line'><span class="k">FROM</span> <span class="n">People</span> <span class="k">AS</span> <span class="n">p</span>
</span><span class='line'> <span class="k">LEFT</span> <span class="k">JOIN</span> <span class="n">Addresses</span> <span class="k">AS</span> <span class="n">a</span> <span class="k">ON</span> <span class="n">p</span><span class="p">.</span><span class="n">PeopleID</span> <span class="o">=</span> <span class="n">a</span><span class="p">.</span><span class="n">PeopleID</span>
</span><span class='line'> <span class="k">AND</span> <span class="n">p</span><span class="p">.</span><span class="n">LastName</span> <span class="o">=</span> <span class="s1">'Baratheon'</span>
</span></code></pre></td></tr></table></div></figure>
<p>The key difference here is that the <code>WHERE</code> clause filters (removes) rows, while the condition on the join specifies that all rows from the left table should be returned but data from the right table should only be displayed when the two conditions (PeopleID and LastName = ‘Baratheon’) match.</p>
<p>Using our examples above, the first with the <code>WHERE</code> clause will <code>LEFT JOIN</code> Addresses to People and will then remove all rows where the LastName is not ‘Baratheon’. Our second example with the condition on the join will <code>LEFT JOIN</code> Addresses to People but will display all rows in the People table and only show addresses when LastName is ‘Baratheon’. Here is an <a href="http://sqlfiddle.com/#!3/39efc/5">SQL Fiddle</a> where you can see some example output. <a href="http://sqlfiddle.com/#!3/39efc/6">Query 1</a> gives us two rows (for Robert and Joffrey Baratheon) with an address for Robert, while <a href="http://sqlfiddle.com/#!3/39efc/5">query 2</a> lists all People but again only lists an address for Robert.</p>
<p>The main point to note: though these queries look similar, their results are vastly different.</p>
<h2>Be careful with <code>NOT</code> and <></h2>
<p>Sometimes you forget a little of the theory that you are drilled on in school when putting knowledge into practice. Case in point for me was the use of <code>NOT</code>. When I first started out writing queries at my job, it seemed like the simplest thing in the world when someone asked to see all results except for one or two particular types. I would just throw my <code>NOT IN</code> in the <code>WHERE</code> clause and go on my merry way. However, remember in SQL we are working with 3-valued logic which means that an expression can evaluate to true, false or undefined. Due to this, I started to run into problems when I used a <code>NOT</code> on columns that could contain <code>NULL</code>s, because when you compare NULL to any value the result is undefined and that row is not included in your result set.</p>
<p>Example time: here is another <a href="http://sqlfiddle.com/#!3/f7197b/1">SQL Fiddle</a> listing a few US Presidents and their first, middle, and last names. When we run our query for presidents who do not have a middle name of Jefferson (using <code>WHERE MiddleName NOT IN ('Jefferson')</code>), we don’t quite get what we expect. Why? Because we are working with 3-valued logic, and comparing NULL to a value results in undefined and will not include that row in our result set. So since Abe and Woodrow Wilson do not have middle names, we do not see them in our results. This is a very simple example (and one easily corrected by adding <code>OR MiddleName IS NULL</code> to our <code>WHERE</code> clause), but often you will have more complicated problems involving multiple tables when you want to use <code>NOT</code>. My go-to solution in these situations is to use a <code>LEFT JOIN</code>:</p>
<figure class='code'><figcaption><span></span></figcaption><div class="highlight"><table><tr><td class="gutter"><pre class="line-numbers"><span class='line-number'>1</span>
<span class='line-number'>2</span>
<span class='line-number'>3</span>
<span class='line-number'>4</span>
<span class='line-number'>5</span>
<span class='line-number'>6</span>
</pre></td><td class='code'><pre><code class='sql'><span class='line'><span class="c1">--Using LEFT JOIN instead of NOT keyword</span>
</span><span class='line'><span class="k">SELECT</span> <span class="n">p1</span><span class="p">.</span><span class="n">FirstName</span><span class="p">,</span> <span class="n">p1</span><span class="p">.</span><span class="n">MiddleName</span><span class="p">,</span> <span class="n">p1</span><span class="p">.</span><span class="n">LastName</span>
</span><span class='line'><span class="k">FROM</span> <span class="n">Presidents</span> <span class="k">AS</span> <span class="n">p1</span>
</span><span class='line'> <span class="k">LEFT</span> <span class="k">JOIN</span> <span class="n">Presidents</span> <span class="k">AS</span> <span class="n">p2</span> <span class="k">ON</span> <span class="p">(</span><span class="n">p1</span><span class="p">.</span><span class="n">PresidentID</span> <span class="o">=</span> <span class="n">p2</span><span class="p">.</span><span class="n">PresidentID</span><span class="p">)</span>
</span><span class='line'> <span class="k">AND</span> <span class="n">p2</span><span class="p">.</span><span class="n">MiddleName</span> <span class="o">=</span> <span class="s1">'Jefferson'</span>
</span><span class='line'><span class="k">WHERE</span> <span class="n">p2</span><span class="p">.</span><span class="n">PresidentID</span> <span class="k">IS</span> <span class="k">NULL</span>
</span></code></pre></td></tr></table></div></figure>
<p>There are several other options you can use (here are a <a href="http://www.dbatodba.com/sql-server/how-tos/typical-solutions-to-avoid-using-not-in-on-sql-server/">few more solutions</a> to try).</p>
<h2>Some developers alias without the keyword <code>AS</code></h2>
<p>When you are trying to find a solution to a problem by searching online, you can quickly get confused by the coding styles some developers use. The biggest one for me was that the <code>AS</code> keyword for aliasing is optional and some developers do not choose to use it.</p>
<figure class='code'><figcaption><span></span></figcaption><div class="highlight"><table><tr><td class="gutter"><pre class="line-numbers"><span class='line-number'>1</span>
<span class='line-number'>2</span>
<span class='line-number'>3</span>
<span class='line-number'>4</span>
<span class='line-number'>5</span>
</pre></td><td class='code'><pre><code class='sql'><span class='line'><span class="c1">--Optional AS for aliasing can get confusing</span>
</span><span class='line'><span class="k">SELECT</span> <span class="n">peep</span><span class="p">.</span><span class="n">FirstName</span> <span class="n">GivenName</span><span class="p">,</span> <span class="n">peep</span><span class="p">.</span><span class="n">LastName</span> <span class="n">SurName</span><span class="p">,</span> <span class="n">addr</span><span class="p">.</span><span class="n">Street</span> <span class="n">HighRoad</span><span class="p">,</span> <span class="n">addr</span><span class="p">.</span><span class="n">City</span> <span class="n">Castle</span><span class="p">,</span> <span class="n">addr</span><span class="p">.</span><span class="k">State</span> <span class="n">Continent</span>
</span><span class='line'><span class="k">FROM</span> <span class="n">People</span> <span class="n">peep</span>
</span><span class='line'> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">Addresses</span> <span class="n">addr</span> <span class="k">ON</span> <span class="n">peep</span><span class="p">.</span><span class="n">PeopleID</span> <span class="o">=</span> <span class="n">addr</span><span class="p">.</span><span class="n">PeopleID</span>
</span><span class='line'><span class="k">WHERE</span> <span class="n">peep</span><span class="p">.</span><span class="n">LastName</span> <span class="o">=</span> <span class="s1">'Stark'</span>
</span></code></pre></td></tr></table></div></figure>
<p>You can puzzle this mess out if you’ve been developing for a while, but as a relative beginner this can start to look like gibberish in a hurry. While there is little you can do to fix most of the examples on the internet, I’d encourage you to continue using <code>AS</code> for aliasing even as you improve your query writing. Think of it as a way to pay it forward to future students who will use your code to learn. So to make the above a little more clear:</p>
<figure class='code'><figcaption><span></span></figcaption><div class="highlight"><table><tr><td class="gutter"><pre class="line-numbers"><span class='line-number'>1</span>
<span class='line-number'>2</span>
<span class='line-number'>3</span>
<span class='line-number'>4</span>
<span class='line-number'>5</span>
</pre></td><td class='code'><pre><code class='sql'><span class='line'><span class="c1">--AS for aliasing = good!</span>
</span><span class='line'><span class="k">SELECT</span> <span class="n">peep</span><span class="p">.</span><span class="n">FirstName</span> <span class="k">AS</span> <span class="n">GivenName</span><span class="p">,</span> <span class="n">peep</span><span class="p">.</span><span class="n">LastName</span> <span class="k">AS</span> <span class="n">SurName</span><span class="p">,</span> <span class="n">addr</span><span class="p">.</span><span class="n">Street</span> <span class="k">AS</span> <span class="n">HighRoad</span><span class="p">,</span> <span class="n">addr</span><span class="p">.</span><span class="n">City</span> <span class="k">AS</span> <span class="n">Castle</span><span class="p">,</span> <span class="n">addr</span><span class="p">.</span><span class="k">State</span> <span class="k">AS</span> <span class="n">Continent</span>
</span><span class='line'><span class="k">FROM</span> <span class="n">People</span> <span class="k">AS</span> <span class="n">peep</span>
</span><span class='line'> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">Addresses</span> <span class="k">AS</span> <span class="n">addr</span> <span class="k">ON</span> <span class="n">peep</span><span class="p">.</span><span class="n">PeopleID</span> <span class="o">=</span> <span class="n">addr</span><span class="p">.</span><span class="n">PeopleID</span>
</span><span class='line'><span class="k">WHERE</span> <span class="n">peep</span><span class="p">.</span><span class="n">LastName</span> <span class="o">=</span> <span class="s1">'Stark'</span>
</span></code></pre></td></tr></table></div></figure>
<h2>Using the <code>APPLY</code> operator</h2>
<p>I learned how to use the <code>APPLY</code> operator my first day on the job, and I’ve been amazed since then that it was never covered in my formal education. You can attempt to make sense of the <a href="http://technet.microsoft.com/en-us/library/ms175156.aspx">MSDN article on Using APPLY</a>, but I typically use it in a simple way: if I’m looking to use a <code>TOP 1</code> subquery in my <code>SELECT</code> clause, then it’s time to use an <code>APPLY</code>. But why bother you ask? One word: performance. Whenever I’ve compared execution times and plans, the version with the <code>APPLY</code> outperforms a <code>TOP 1</code> subquery almost every time.</p>
<p>Going back to our people and addresses example from earlier, I’ve added a column, IsPreferred, for people with multiple addresses. Now using a typical <code>TOP 1</code> subquery, our query would look like this: <a href="http://sqlfiddle.com/#!3/42ceb/1">SQL Fiddle</a></p>
<figure class='code'><figcaption><span></span></figcaption><div class="highlight"><table><tr><td class="gutter"><pre class="line-numbers"><span class='line-number'>1</span>
<span class='line-number'>2</span>
<span class='line-number'>3</span>
<span class='line-number'>4</span>
<span class='line-number'>5</span>
<span class='line-number'>6</span>
<span class='line-number'>7</span>
<span class='line-number'>8</span>
<span class='line-number'>9</span>
<span class='line-number'>10</span>
</pre></td><td class='code'><pre><code class='sql'><span class='line'><span class="c1">--TOP 1 subquery</span>
</span><span class='line'><span class="k">SELECT</span> <span class="n">p</span><span class="p">.</span><span class="n">FirstName</span>
</span><span class='line'> <span class="p">,</span> <span class="n">p</span><span class="p">.</span><span class="n">LastName</span>
</span><span class='line'> <span class="p">,</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">TOP</span> <span class="mi">1</span> <span class="n">Street</span> <span class="o">+</span> <span class="s1">' '</span> <span class="o">+</span> <span class="n">City</span> <span class="o">+</span> <span class="s1">' '</span> <span class="o">+</span> <span class="k">State</span>
</span><span class='line'> <span class="k">FROM</span> <span class="n">Addresses</span> <span class="k">AS</span> <span class="n">a</span>
</span><span class='line'> <span class="k">WHERE</span> <span class="n">a</span><span class="p">.</span><span class="n">PeopleID</span> <span class="o">=</span> <span class="n">p</span><span class="p">.</span><span class="n">PeopleID</span>
</span><span class='line'> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">a</span><span class="p">.</span><span class="n">IsPreferred</span><span class="p">)</span> <span class="k">AS</span> <span class="n">Address</span>
</span><span class='line'><span class="k">FROM</span> <span class="n">People</span> <span class="k">AS</span> <span class="n">p</span>
</span><span class='line'><span class="k">WHERE</span> <span class="n">p</span><span class="p">.</span><span class="n">LastName</span> <span class="o">=</span> <span class="s1">'Stark'</span>
</span><span class='line'> <span class="k">AND</span> <span class="n">p</span><span class="p">.</span><span class="n">FirstName</span> <span class="o">=</span> <span class="s1">'Ned'</span>
</span></code></pre></td></tr></table></div></figure>
<p>Now here’s the <code>APPLY</code> version: <a href="http://sqlfiddle.com/#!3/42ceb/2">SQL Fiddle</a>:</p>
<figure class='code'><figcaption><span></span></figcaption><div class="highlight"><table><tr><td class="gutter"><pre class="line-numbers"><span class='line-number'>1</span>
<span class='line-number'>2</span>
<span class='line-number'>3</span>
<span class='line-number'>4</span>
<span class='line-number'>5</span>
<span class='line-number'>6</span>
<span class='line-number'>7</span>
<span class='line-number'>8</span>
<span class='line-number'>9</span>
<span class='line-number'>10</span>
<span class='line-number'>11</span>
</pre></td><td class='code'><pre><code class='sql'><span class='line'><span class="c1">--Using APPLY</span>
</span><span class='line'><span class="k">SELECT</span> <span class="n">p</span><span class="p">.</span><span class="n">FirstName</span>
</span><span class='line'> <span class="p">,</span> <span class="n">p</span><span class="p">.</span><span class="n">LastName</span>
</span><span class='line'> <span class="p">,</span> <span class="n">addr</span><span class="p">.</span><span class="n">Address</span>
</span><span class='line'><span class="k">FROM</span> <span class="n">People</span> <span class="k">AS</span> <span class="n">p</span>
</span><span class='line'> <span class="k">OUTER</span> <span class="n">APPLY</span><span class="p">(</span><span class="k">SELECT</span> <span class="n">TOP</span> <span class="mi">1</span> <span class="n">Street</span> <span class="o">+</span> <span class="s1">' '</span> <span class="o">+</span> <span class="n">City</span> <span class="o">+</span> <span class="s1">' '</span> <span class="o">+</span> <span class="k">State</span> <span class="k">AS</span> <span class="n">Address</span>
</span><span class='line'> <span class="k">FROM</span> <span class="n">Addresses</span> <span class="k">AS</span> <span class="n">a</span>
</span><span class='line'> <span class="k">WHERE</span> <span class="n">a</span><span class="p">.</span><span class="n">PeopleID</span> <span class="o">=</span> <span class="n">p</span><span class="p">.</span><span class="n">PeopleID</span>
</span><span class='line'> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">a</span><span class="p">.</span><span class="n">IsPreferred</span><span class="p">)</span> <span class="k">AS</span> <span class="n">addr</span>
</span><span class='line'><span class="k">WHERE</span> <span class="n">p</span><span class="p">.</span><span class="n">LastName</span> <span class="o">=</span> <span class="s1">'Stark'</span>
</span><span class='line'> <span class="k">AND</span> <span class="n">p</span><span class="p">.</span><span class="n">FirstName</span> <span class="o">=</span> <span class="s1">'Ned'</span>
</span></code></pre></td></tr></table></div></figure>
<p>These two queries look very similar, so you may be wondering why one is generally more performant than the other. Part of the difference is due to the order of statement execution. <code>SELECT</code> is one of the last clauses that get processed, so any subqueries in the <code>SELECT</code> list will take place well after the <code>FROM</code> and <code>WHERE</code> clauses have been processed. But the <code>APPLY</code> version moves this subquery to the <code>FROM</code> clause, meaning it is executed very early and gives the query optimizer a lot more flexibility to produce an ideal execution plan. (For a more complete list of the order of statement execution, see <a href="http://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/">SQL Authority</a>).</p>
</div>
</article>
<article>
<header>
<h1 class="entry-title"><a href="/blog/2013/12/19/t-sql-date-range-from-current-quarter/">T-SQL Date Range From the Current Quarter</a></h1>
<p class="meta">
<time datetime="2013-12-19T01:43:38-07:00" pubdate data-updated="true">Dec 19<span>th</span>, 2013</time>
</p>
</header>
<div class="entry-content"><p>A <a href="http://stackoverflow.com/q/20672016/1657324">recent question on StackOverflow</a> got me pondering about dates and quarters. The user wanted to return data where the date is in the current quarter. After some Google searches, I found this great solution to <a href="http://www.sqlservercurry.com/2009/05/find-first-and-last-day-of-current.html">find the first and last day of the quarter</a> from SQL Server Curry. From there I just added 1 day to the last day of the quarter (to make sure we are <a href="http://www.kebabshopblues.co.uk/2009/08/30/one-second-to-midnight-datetimes-in-sql-server-2005/">truly capturing all date/time values</a> in the quarter) and put it all in the <code>WHERE</code> clause:</p>
<figure class='code'><figcaption><span></span></figcaption><div class="highlight"><table><tr><td class="gutter"><pre class="line-numbers"><span class='line-number'>1</span>
<span class='line-number'>2</span>
<span class='line-number'>3</span>
<span class='line-number'>4</span>
</pre></td><td class='code'><pre><code class='sql'><span class='line'><span class="k">SELECT</span> <span class="o">*</span>
</span><span class='line'><span class="k">FROM</span> <span class="n">YourTable</span>
</span><span class='line'><span class="k">WHERE</span> <span class="n">DateColumn</span> <span class="o">>=</span> <span class="n">DATEADD</span><span class="p">(</span><span class="n">qq</span><span class="p">,</span><span class="n">DATEDIFF</span><span class="p">(</span><span class="n">qq</span><span class="p">,</span><span class="mi">0</span><span class="p">,</span><span class="n">GETDATE</span><span class="p">()),</span><span class="mi">0</span><span class="p">)</span>
</span><span class='line'> <span class="k">AND</span> <span class="n">DateColumn</span> <span class="o"><</span> <span class="n">DATEADD</span><span class="p">(</span><span class="n">dd</span><span class="p">,</span><span class="mi">1</span><span class="p">,</span><span class="n">DATEADD</span><span class="p">(</span><span class="n">qq</span><span class="p">,</span><span class="n">DATEDIFF</span><span class="p">(</span><span class="n">qq</span><span class="p">,</span><span class="o">-</span><span class="mi">1</span><span class="p">,</span><span class="n">GETDATE</span><span class="p">()),</span><span class="o">-</span><span class="mi">1</span><span class="p">))</span>
</span></code></pre></td></tr></table></div></figure>
<p>You may be wondering ‘why not just compare the quarter and year of the DateColumn to the current date?’ e.g. <code>WHERE YEAR(DateColumn) = YEAR(GETDATE()) AND DATEPART(QUARTER,DateColumn) = DATEPART(QUARTER,GETDATE())</code> You can do that and it works fine for small datasets, but using functions on your column in a <code>WHERE</code> clause will result in a non-<a href="http://en.wikipedia.org/wiki/Sargable">sargable</a> query, meaning it can’t take advantage of an index on <code>DateColumn</code>. So go with the uglier, sargable query if you are looking to optimize performance.<sup id="fnref:1"><a href="#fn:1" rel="footnote">1</a></sup></p>
<div class="footnotes">
<hr/>
<ol>
<li id="fn:1">
<p>I think sargability is a super interesting optimization topic. In case you were curious, sargable is short for Search ARGument ABLE, and here’s a few more articles on the topic if you would like to know more: <a href="http://msmvps.com/blogs/robfarley/archive/2010/01/22/sargable-functions-in-sql-server.aspx">Sargable functions in SQL Server by Rob Farley</a> and <a href="http://stackoverflow.com/q/799584/1657324">stackoverflow: what makes a sql statement sargable</a>.<a href="#fnref:1" rev="footnote">↩</a></p></li>
</ol>
</div>
</div>
</article>
<article>
<header>
<h1 class="entry-title"><a href="/blog/2013/12/13/customizing-octopress/">Customizing Octopress</a></h1>
<p class="meta">
<time datetime="2013-12-13T02:40:21-07:00" pubdate data-updated="true">Dec 13<span>th</span>, 2013</time>
</p>
</header>
<div class="entry-content"><p>I recently made a few customizations to my Octopress environment using resources/blog articles scattered about the web; all were surprisingly easy and definitely added value. Here are my favorites if you want to take a stab at any of these yourself:</p>
<h2><a href="http://arshad.github.io/blog/2012/05/10/recipe-hiding-posts-from-the-octopress-front-page/">Hiding specific categories of posts from the front page</a></h2>
<p>I particularly wanted to do this for my Snippets category, as sometimes I want to take notes or describe the logic of a concept (or simply have a basic example that I can copy in the future), but I don’t particularly want that showing up as the top article on my front page either. Best of both worlds by following the simple changes in this article.</p>
<h2><a href="http://blog.geoffpetrie.com/blog/2012/10/14/octopress-change-the-default-preview-server/">Change the preview server to Ruby Thin Server</a></h2>
<p>I did this trying to solve a different problem; it didn’t help then but I liked the server so much that I’ve stuck with it. Very easy to get it up and running too.</p>
<h2><a href="http://hiltmon.com/blog/2013/05/08/octopress-now-has-footnotes/">Footnote styling</a></h2>
<p>I really liked the idea of using footnotes for my sources, but by default they are rather gigantic and widely spaced. Some simple additions to <code>sass/custom/_styles.css</code> and your footnotes look like they are supposed to: compact and well-spaced. I’ll even add an example footnote<sup id="fnref:1"><a href="#fn:1" rel="footnote">1</a></sup> for you to see what these changes look like.</p>
<h2><a href="http://blog.codebykat.com/2013/05/20/three-octopress-tweaks/#github-aside">Filter repositories from Github sidebar</a></h2>
<p>As the author of that linked post so aptly points out, it doesn’t make a lot of sense to show off the repository your blog is on if you are being hosted by Github Pages. Also, some projects you just don’t want to show off, and a few lines of code will make sure they don’t appear under the Github Repos of your blog.</p>
<h2><a href="https://github.com/ragle/Monokai-Sublime-Text-Octopress">Use Monokai color scheme for code block syntax highlighting</a></h2>
<p>Seems like lots of people want to slightly alter the built in syntax highlighting. I like the Monokai theme from Sublime Text 2, and luckily I found a <a href="https://github.com/ragle/Monokai-Sublime-Text-Octopress">nice patch</a> that will switch to that color scheme without much additional work. You can also find great, detailed posts if you are more interested in the <a href="http://blog.alestanis.com/2013/02/04/octopress-and-the-twilight-color-scheme/">Twilight color scheme</a> or a more <a href="http://blog.codebykat.com/2013/05/23/gorgeous-octopress-codeblocks-with-coderay/">Github-style color scheme</a>. Keep in mind though that some of these will require you to change your markdown processor and swap in a different syntax highlighter. I preferred to stick with the default Pygments syntax highlighting and rdiscount markdown processor, and I was lucky to find a great resource already created. Check out the prettiness:</p>
<figure class='code'><figcaption><span> (sample.js)</span> <a href='/downloads/code/sample.js'>download</a></figcaption>
<div class="highlight"><table><tr><td class="gutter"><pre class="line-numbers"><span class='line-number'>1</span>
<span class='line-number'>2</span>
<span class='line-number'>3</span>
<span class='line-number'>4</span>
<span class='line-number'>5</span>
<span class='line-number'>6</span>
<span class='line-number'>7</span>
<span class='line-number'>8</span>
<span class='line-number'>9</span>
<span class='line-number'>10</span>
<span class='line-number'>11</span>
<span class='line-number'>12</span>
<span class='line-number'>13</span>
<span class='line-number'>14</span>
<span class='line-number'>15</span>
<span class='line-number'>16</span>
<span class='line-number'>17</span>
<span class='line-number'>18</span>
<span class='line-number'>19</span>
<span class='line-number'>20</span>
<span class='line-number'>21</span>
<span class='line-number'>22</span>
</pre></td><td class='code'><pre><code class='js'><span class='line'><span class="kd">var</span> <span class="nx">github</span> <span class="o">=</span> <span class="p">(</span><span class="kd">function</span><span class="p">(){</span>
</span><span class='line'> <span class="k">return</span> <span class="p">{</span>
</span><span class='line'> <span class="nx">showRepos</span><span class="o">:</span> <span class="kd">function</span><span class="p">(</span><span class="nx">options</span><span class="p">){</span>
</span><span class='line'> <span class="nx">$</span><span class="p">.</span><span class="nx">ajax</span><span class="p">({</span>
</span><span class='line'> <span class="nx">url</span><span class="o">:</span> <span class="s2">"https://api.github.com/users/"</span><span class="o">+</span><span class="nx">options</span><span class="p">.</span><span class="nx">user</span><span class="o">+</span><span class="s2">"/repos?sort=pushed&callback=?"</span>
</span><span class='line'> <span class="p">,</span> <span class="nx">dataType</span><span class="o">:</span> <span class="s1">'jsonp'</span>
</span><span class='line'> <span class="p">,</span> <span class="nx">error</span><span class="o">:</span> <span class="kd">function</span> <span class="p">(</span><span class="nx">err</span><span class="p">)</span> <span class="p">{</span> <span class="nx">$</span><span class="p">(</span><span class="nx">options</span><span class="p">.</span><span class="nx">target</span> <span class="o">+</span> <span class="s1">' li.loading'</span><span class="p">).</span><span class="nx">addClass</span><span class="p">(</span><span class="s1">'error'</span><span class="p">).</span><span class="nx">text</span><span class="p">(</span><span class="s2">"Error loading feed"</span><span class="p">);</span> <span class="p">}</span>
</span><span class='line'> <span class="p">,</span> <span class="nx">success</span><span class="o">:</span> <span class="kd">function</span><span class="p">(</span><span class="nx">data</span><span class="p">)</span> <span class="p">{</span>
</span><span class='line'> <span class="kd">var</span> <span class="nx">repos</span> <span class="o">=</span> <span class="p">[];</span>
</span><span class='line'> <span class="k">if</span> <span class="p">(</span><span class="o">!</span><span class="nx">data</span> <span class="o">||</span> <span class="o">!</span><span class="nx">data</span><span class="p">.</span><span class="nx">data</span><span class="p">)</span> <span class="p">{</span> <span class="k">return</span><span class="p">;</span> <span class="p">}</span>
</span><span class='line'> <span class="k">for</span> <span class="p">(</span><span class="kd">var</span> <span class="nx">i</span> <span class="o">=</span> <span class="mi">0</span><span class="p">;</span> <span class="nx">i</span> <span class="o"><</span> <span class="nx">data</span><span class="p">.</span><span class="nx">data</span><span class="p">.</span><span class="nx">length</span><span class="p">;</span> <span class="nx">i</span><span class="o">++</span><span class="p">)</span> <span class="p">{</span>
</span><span class='line'> <span class="k">if</span> <span class="p">(</span><span class="nx">options</span><span class="p">.</span><span class="nx">skip_forks</span> <span class="o">&&</span> <span class="nx">data</span><span class="p">.</span><span class="nx">data</span><span class="p">[</span><span class="nx">i</span><span class="p">].</span><span class="nx">fork</span><span class="p">)</span> <span class="p">{</span> <span class="k">continue</span><span class="p">;</span> <span class="p">}</span>
</span><span class='line'> <span class="k">if</span> <span class="p">(</span> <span class="nx">jQuery</span><span class="p">.</span><span class="nx">inArray</span><span class="p">(</span> <span class="nx">data</span><span class="p">.</span><span class="nx">data</span><span class="p">[</span><span class="nx">i</span><span class="p">].</span><span class="nx">name</span><span class="p">,</span> <span class="nx">options</span><span class="p">.</span><span class="nx">skip_repos</span> <span class="p">)</span> <span class="o">!=</span> <span class="o">-</span><span class="mi">1</span><span class="p">)</span> <span class="p">{</span> <span class="k">continue</span><span class="p">;</span> <span class="p">}</span>
</span><span class='line'> <span class="nx">repos</span><span class="p">.</span><span class="nx">push</span><span class="p">(</span><span class="nx">data</span><span class="p">.</span><span class="nx">data</span><span class="p">[</span><span class="nx">i</span><span class="p">]);</span>
</span><span class='line'> <span class="p">}</span>
</span><span class='line'> <span class="k">if</span> <span class="p">(</span><span class="nx">options</span><span class="p">.</span><span class="nx">count</span><span class="p">)</span> <span class="p">{</span> <span class="nx">repos</span><span class="p">.</span><span class="nx">splice</span><span class="p">(</span><span class="nx">options</span><span class="p">.</span><span class="nx">count</span><span class="p">);</span> <span class="p">}</span>
</span><span class='line'> <span class="nx">render</span><span class="p">(</span><span class="nx">options</span><span class="p">.</span><span class="nx">target</span><span class="p">,</span> <span class="nx">repos</span><span class="p">);</span>
</span><span class='line'> <span class="p">}</span>
</span><span class='line'> <span class="p">});</span>
</span><span class='line'> <span class="p">}</span>
</span><span class='line'> <span class="p">};</span>
</span><span class='line'><span class="p">})();</span>
</span></code></pre></td></tr></table></div></figure>
<h2><a href="http://blog.jonathanrwallace.com/blog/2013/09/21/octopress-love-twitter-update/">Add tweets to the sidebar without the ugly widget</a></h2>
<p>I don’t tweet a lot, but when I do, I want them to look like they belong on the page. After Twitter changed its API to require authentication, it effectively got dropped from Octopress. But a <a href="https://github.com/imathis/octopress/pull/1311">nice Github contributor</a> figured out a way to continue displaying tweets. Essentially, you create a Twitter widget and pull out the data-widget-id from the generated code and plop that into the _config.yaml file. Looks great on the sidebar too!</p>
<div class="footnotes">
<hr/>
<ol>
<li id="fn:1">
<p>I really like these footnotes. You can even put <code>code blocks</code> in them and everything. Great place to put <a href="http://imgur.com/r/cats/YYYC3bW">ancillary links</a> that people may or may not care to look at/read.<a href="#fnref:1" rev="footnote">↩</a></p></li>
</ol>
</div>
</div>
</article>
<article>
<header>
<h1 class="entry-title"><a href="/blog/2013/11/25/octopress-generating-empty-html-pages/">Octopress Generating Empty Html Pages</a></h1>
<p class="meta">
<time datetime="2013-11-25T22:05:19-07:00" pubdate data-updated="true">Nov 25<span>th</span>, 2013</time>
</p>
</header>
<div class="entry-content"><p>So you probably just tried a <code>rake generate</code> on your Octopress blog only to end up with a blank index.html in your public folder and a few empty posts in your blog folder, ya?</p>
<p>When this occurred for me, the problem happened right as I started trying out the fancy syntax highlighting for code blocks. Posts with plain vanilla code blocks generated just fine, but as soon as I added languages for highlighting at the start of each block I saw empty posts/pages. After many searches for almost every synonym of ‘empty’ you can think of, I finally solved my problem through a combination of the following:</p>
<h2>Check if Python is installed</h2>
<p>“But Octopress is in Ruby, why do I need Python?” Octopress uses <a href="http://pygments.org/">Pygments</a> for syntax highlighting, which is built in Python, and it uses the pygments.rb gem to use said Python file.</p>
<h2>Ensure Pygments is installed in Python</h2>
<p>This was one of my problems. I already had Python from some earlier messing around, but didn’t know I needed to separately download Pygments and install it. Well, ya do. Here’s the <a href="http://pygments.org/download/">download site</a>, or you can use some <code>easy_install</code> fanciness if you prefer.</p>
<h2>Make sure your PATH for Python is set correctly</h2>
<p>Another one of my problems. I happened upon this from a <a href="http://stackoverflow.com/a/14202515/1657324">stackoverflow user having similar problems with code blocks</a>. Here are some good instructions for <a href="http://stackoverflow.com/a/4855685/1657324">updating PATH in Windows</a> from another SO question, and yet another SO answer on <a href="http://stackoverflow.com/a/12592280/1657324">possible mistakes when updating PATH</a>. Most useful to me: be sure to <strong>exit and restart the window you are testing in</strong>. I didn’t do that, and it caused a lot more headaches.</p>
<h2>Update pygments.rb to a newer version</h2>
<pre><code>bundle update pygments.rb
</code></pre>
<p>Not sure if I really needed to do this, but one of my searches led me to this <a href="http://stackoverflow.com/a/14088936/1657324">SO answer on octopress generating blank files</a>, and figured updating pygments.rb couldn’t hurt. Running the above resulted in pygments.rb updating from 0.3.4 to 0.3.7. I then updated the version in my Gemfile in the octopress main folder and was good to go.<sup id="fnref:1"><a href="#fn:1" rel="footnote">1</a></sup></p>
<p>Doing all this did not initially solve my problem, because I never exited my command-line window to let my PATH do its magic. But the next day everything was working again, and I was scratching my head (until I found that comment on closing and re-opening the window). Hopefully something here will help you with this horrible problem!</p>
<div class="footnotes">
<hr/>
<ol>
<li id="fn:1">
<p>incidentally, I am so new to Ruby I didn’t know how to update a Gemfile. If you are in the same boat, just open Gemfile in a text editor to see all the gems Octopress is using and their version. When you update a gem with <code>bundle update</code>, you download the new version but also keep a copy of the old version in your gems folder. So to update what Octopress uses, you need to update the version number in your Gemfile<a href="#fnref:1" rev="footnote">↩</a></p></li>
</ol>
</div>
</div>
</article>
<article>
<header>
<h1 class="entry-title"><a href="/blog/2013/11/20/getting-started-octopress-windows-no-ruby/">Getting Started With Octopress: On Windows and With No Ruby Experience?!</a></h1>
<p class="meta">
<time datetime="2013-11-20T22:48:35-07:00" pubdate data-updated="true">Nov 20<span>th</span>, 2013</time>
</p>
</header>
<div class="entry-content"><p>So you just found <a href="http://octopress.org/">Octopress</a> where you can write in <a href="http://daringfireball.net/projects/markdown/syntax">Markdown</a> and get awesome free hosting for your static html via <a href="http://pages.github.com/">Github pages</a>. But it’s built with Ruby and you are on Windows, can you still make this work?</p>
<p>Yes, with a little patience you definitely can! I am just getting my blog up on Octopress and thought I’d help by documenting a couple of the <em>gotchas</em> you may run into.</p>
<p>Head over to the <a href="http://octopress.org/docs/setup/">Octopress setup page</a> to give you an outline of what you need to do.</p>
<p>As the setup page mentions, first you’ll need to install Git if you don’t already have it. I recommend the <a href="http://msysgit.github.io/">msysgit</a> as it comes with a very convenient Git Bash console. If you’re unsure which installation settings to select, see the <a href="https://help.github.com/articles/set-up-git">setup page on github</a> and click the “Not sure what to pick on each screen?” info box. Then head over to the <a href="https://help.github.com/articles/generating-ssh-keys">generating SSH keys</a> page to make sure you can connect to Github.</p>
<p>Back to the Octopress setup guide, the next step is to install Ruby 1.9.3 or greater. Since we’ve got Windows awesomeness going on, you will want to get the <a href="http://rubyinstaller.org/downloads/">RubyInstaller for Windows</a>. After installation you will now have the Start Command Prompt with Ruby which you can run Ruby commands on.</p>
<p>Next clone the Octopress project so you have a local copy and change to that directory using your Start Command Prompt with Ruby. Here is about the point where I ran into some problems. The setup guide has you install dependencies, so you run:</p>
<figure class='code'><div class="highlight"><table><tr><td class="gutter"><pre class="line-numbers"><span class='line-number'>1</span>
<span class='line-number'>2</span>
</pre></td><td class='code'><pre><code class=''><span class='line'>gem install bundler
</span><span class='line'>bundle install</span></code></pre></td></tr></table></div></figure>
<p>But if you are lucky like me when running <code>bundle install</code> you get an error about native extensions and that you need the Development Kit. Head on over to the <a href="https://github.com/oneclick/rubyinstaller/wiki/Development-Kit">RubyInstaller Dev Kit page</a> and follow their instructions for downloading and installing the Dev Kit. After that’s done, change directories back to Octopress and run those two Ruby commands again, which should both complete this time.</p>
<p>Following the guide, you are now ready to run the next bit <code>rake install</code> and, of course, that doesn’t work either. If you’re like me and <a href="http://stackoverflow.com/q/12400185/1657324">this guy at stackoverflow</a>, you got a:</p>
<figure class='code'><div class="highlight"><table><tr><td class="gutter"><pre class="line-numbers"><span class='line-number'>1</span>
<span class='line-number'>2</span>
</pre></td><td class='code'><pre><code class=''><span class='line'>rake aborted!
</span><span class='line'>No such file or directory - git remote -v</span></code></pre></td></tr></table></div></figure>
<p>which <a href="http://stackoverflow.com/a/12401196/1657324">the question answerer points out</a> is a PATH problem. Following his link will lead to a <a href="http://stackoverflow.com/a/9793850">short and sweet description on updating your PATH</a>. The basic problem here is that Ruby can’t find git, and you will be updating your PATH so it can find it in the future. After the update, run that <code>rake install</code> again and it works! Hooray!</p>
<p>Now jump on over to <a href="http://octopress.org/docs/deploying/github/">deploying to Github pages</a> and follow the instructions to get your Octopress pages pushed up to a Pages site. Those instructions are perfect and caused me no problems so I won’t bother going into them.</p>
<p>One last gotcha: you will generally use the two commands listed on that page <code>rake generate</code> and <code>rake deploy</code> to generate your markdown to html and push them up to Github, but one other command you’ll definitely want to use is <code>rake preview</code> which will mount a webserver for you to view your site before pushing it live. <a href="http://octopress.org/docs/blogging/">The Octopress docs</a> say your webserver is mounted at <a href="http://localhost:4000,">http://localhost:4000,</a> which it is, but if it’s late and your brain’s not working right like mine and <a href="http://stackoverflow.com/q/17465404">this stackoverflow user’s</a>, you may try that path exactly and wonder why nothing is showing up. If you deployed to a Github Project Page, the generated files you are trying to see are in /public/ProjectName, so you will need to add your project’s name to the URL to see the preview (a la <a href="http://localhost:4000/ProjectName">http://localhost:4000/ProjectName</a>).</p>
<p>Hope some of that helps my Windows friends out there; happy blogging!</p>
</div>
</article>
<div class="pagination">
<a href="/blog/archives">Blog Archives</a>
</div>
</div>
<aside class="sidebar">
<section>
<h1>Recent Posts</h1>
<ul id="recent_posts">
<li class="post">
<a href="/blog/2014/05/01/quick-thoughts-after-taking-microsoft-exam-70-461/">Quick Thoughts After Taking Microsoft Exam 70-461</a>
</li>
<li class="post">
<a href="/blog/2014/03/28/t-sql-tips-transition-school-to-industry/">T-SQL Tips for Transitioning From School to Industry</a>
</li>
<li class="post">
<a href="/blog/2013/12/19/t-sql-date-range-from-current-quarter/">T-SQL Date Range From the Current Quarter</a>
</li>
<li class="post">
<a href="/blog/2013/12/13/customizing-octopress/">Customizing Octopress</a>
</li>
<li class="post">
<a href="/blog/2013/12/10/asp-dot-net-repeaters-and-the-itemdatabound-event/">ASP.NET Repeaters and the ItemDataBound Event</a>
</li>
</ul>
</section>
<section>
<h1>GitHub Repos</h1>
<ul id="gh_repos">
<li class="loading">Status updating…</li>
</ul>
<a href="https://github.com/fountless">@fountless</a> on GitHub
<script type="text/javascript">
$(document).ready(function(){
if (!window.jXHR){
var jxhr = document.createElement('script');
jxhr.type = 'text/javascript';
jxhr.src = '/javascripts/libs/jXHR.js';
var s = document.getElementsByTagName('script')[0];
s.parentNode.insertBefore(jxhr, s);
}
github.showRepos({
user: 'fountless',
count: 3,
skip_forks: true,
skip_repos: [ "fountless.github.io" ],
target: '#gh_repos'
});
});
</script>
<script src="/javascripts/github.js" type="text/javascript"> </script>
</section>
<section>
<h1>Latest Tweets</h1>
<ul id="tweets">
<a class="twitter-timeline" data-dnt="true" href="https://twitter.com/stueth" data-widget-id="411983651094536192" data-link-color="#1863a1" data-tweet-limit="3" data-chrome="noheader nofooter transparent noscrollbar">Tweets by @stueth</a>
<script>!function(d,s,id){var js,fjs=d.getElementsByTagName(s)[0],p=/^http:/.test(d.location)?'http':'https';if(!d.getElementById(id)){js=d.createElement(s);js.id=id;js.src=p+"://platform.twitter.com/widgets.js";fjs.parentNode.insertBefore(js,fjs);}}(document,"script","twitter-wjs");</script>
</ul>
<a href="http://twitter.com/stueth" class="twitter-follow-button" data-show-count="false">Follow @stueth</a>
</section>
</aside>
</div>
</div>
<footer role="contentinfo"><p>
Copyright © 2015 - Stu Baker -
<span class="credit">Powered by <a href="http://octopress.org">Octopress</a></span>
</p>
</footer>
<script type="text/javascript">
var disqus_shortname = 'plusonetobi';
var disqus_script = 'count.js';
(function () {
var dsq = document.createElement('script'); dsq.type = 'text/javascript'; dsq.async = true;
dsq.src = '//' + disqus_shortname + '.disqus.com/' + disqus_script;
(document.getElementsByTagName('head')[0] || document.getElementsByTagName('body')[0]).appendChild(dsq);
}());
</script>
<div id="fb-root"></div>
<script>(function(d, s, id) {
var js, fjs = d.getElementsByTagName(s)[0];
if (d.getElementById(id)) {return;}
js = d.createElement(s); js.id = id; js.async = true;
js.src = "//connect.facebook.net/en_US/all.js#appId=212934732101925&xfbml=1";
fjs.parentNode.insertBefore(js, fjs);
}(document, 'script', 'facebook-jssdk'));</script>
<script type="text/javascript">
(function() {
var script = document.createElement('script'); script.type = 'text/javascript'; script.async = true;
script.src = 'https://apis.google.com/js/plusone.js';
var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(script, s);
})();
</script>
<script type="text/javascript">
(function(){
var twitterWidgets = document.createElement('script');
twitterWidgets.type = 'text/javascript';
twitterWidgets.async = true;
twitterWidgets.src = '//platform.twitter.com/widgets.js';
document.getElementsByTagName('head')[0].appendChild(twitterWidgets);
})();
</script>
</body>
</html>