Recently I needed access to the list of commits that were included with each of my TeamCity builds. TeamCity provides a pretty big list of Predefined Build Parameters, but it doesn’t provide access to details of the commits it is currently building. Having Powershell and Git on my server, though, I can write some scripts to extract not just information about the latest commit, but about any series of commits that have occurred.

Here is an overview of the articles I published in the third quarter of 2014. INTENSE SCHOOL MCSE Prep (SQL) – Using Parameters in Reporting Services Reports (Part 2) Book Review: SQL Server 2012 Integration Services Design Patterns MSSQLTIPS Adding Custom Reports to SQL Server Management Studio More Text Analysis with the SSIS Term Lookup Component Custom SSMS report to show SQL Server tables per filegroup I have two more tips in the pipeline, one about formatting durations in SSAS and one about creating box plots in SSRS, so keep an eye on the MSSQLTips website.

This month’s T-SQL Tuesday is hosted by Tracy McKibben (blog | twitter) and is all about heroes (not the TV show). Ada Lovelace has been an inspiration to many. In keeping with my blog theme, let’s call her a hero. We all have our heroes, those people who we admire, who inspire us, who we strive to be like. Who is your hero? Your assignment is to acknowledge, in writing, your hero (or heroes). You don’t have to mention them by name if you’re not comfortable doing so, but you do have to tell us how you met them, how they have inspired you, and what qualities or traits of theirs you have striven to adopt.

Some time ago I was writing some windowing functions on a set of data. Basically I was looking for the last date an event had occurred for each type of event. Let's illustrate with an example: CREATE TABLE dbo.TestOver (ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL ,[Group] CHAR(1) NOT NULL ,Value INT NOT NULL); INSERT INTO dbo.TestOver([Group],Value) VALUES ('A',1) ,('A',2) ,('A',3) ,('A',4) ,('B',5) ,('B',6) ,('B',7) ,('B',8) ,('B',9); ```<p style="text-align: justify"> Using the data above, I need to find the value 4 for group A and the value 9 for group B. I first wrote the following T-SQL statement to retrieve the data: </p> ```sql SELECT DISTINCT [Group], MAX(Value) OVER (PARTITION BY [Group] ORDER BY Value) FROM dbo.TestOver; ```<p style="text-align: justify"> <a href="https://lessthandot.z19.web.core.windows.net/wp-content/uploads/2014/10/query1.png"><img class="alignnone size-full wp-image-3013" src="https://lessthandot.z19.web.core.windows.net/wp-content/uploads/2014/10/query1.png" alt="query1" width="595" height="290" srcset="https://lessthandot.z19.web.core.windows.net/wp-content/uploads/2014/10/query1.png 595w, https://lessthandot.z19.web.core.windows.net/wp-content/uploads/2014/10/query1-300x146.png 300w" sizes="(max-width: 595px) 100vw, 595px" /></a> </p> <p style="text-align: justify"> The results are of course incorrect. A little baffled why this was the cause, I changed the ORDER BY to descending which gave me the results I wanted. </p> <p style="text-align: justify"> <a href="https://lessthandot.z19.web.core.windows.net/wp-content/uploads/2014/10/query2.png"><img class="alignnone size-full wp-image-3011" src="https://lessthandot.z19.web.core.windows.net/wp-content/uploads/2014/10/query2.png" alt="query2" width="617" height="142" srcset="https://lessthandot.z19.web.core.windows.net/wp-content/uploads/2014/10/query2.png 617w, https://lessthandot.z19.web.core.windows.net/wp-content/uploads/2014/10/query2-300x69.png 300w" sizes="(max-width: 617px) 100vw, 617px" /></a> </p> <p style="text-align: justify"> I really didn't think twice over this, until I joined the session <a href="http://www.sqlserverdays.be/powerful-t-sql-improvements-that-reduce-query-complexity/">Powerful T-SQL Improvements that Reduce Query Complexity</a> by Hugo Kornelis (<a href="http://sqlblog.com/blogs/hugo_kornelis/">blog</a> | <a href="https://twitter.com/Hugo_Kornelis">twitter</a>) on the SQL Server Days. I learned two things. </p> <ol style="text-align: justify"> <li> You don't need to specify the ORDER BY. </li> </ol> <p style="text-align: justify"> In SQL Server 2005, the <a href="http://msdn.microsoft.com/en-us/library/ms189461(v=sql.90).aspx">OVER clause</a> was introduced and it simplified some aggregations like the one we're doing here. When using the ranking window functions the ORDER BY clause is mandatory, but when using a regular aggregate window function the ORDER BY clause is not allowed. This gives us the following T-SQL which is the perfect solution for our problem here: </p> ```sql SELECT DISTINCT [Group], MAX(Value) OVER (PARTITION BY [Group]) FROM dbo.TestOver; ```<p style="text-align: justify"> To be honest, I completely forgot aggregate functions could be used this way. The PARTITION BY clause is optional as well, so you can have a completely empty OVER clause. </p> <ol style="text-align: justify" start="2"> <li> When you do specify the ORDER BY, defaults come into play. </li> </ol> <p style="text-align: justify"> Starting from SQL Server 2012, the T-SQL windowing functions and the <a href="http://msdn.microsoft.com/en-us/library/ms189461(v=sql.120).aspx">OVER clause</a> were greatly enhanced. Suddenly you can specify an ORDER BY for the aggregate windowing functions (which I did in the first attempts, remember?). However, if you specify an ORDER BY clause but no ROW or RANGE clause, SQL Server will apply the following defaults: RANGE UNBOUNDED PRECEDING as the lower limit and CURRENT ROW for the upper limit of the window. When Hugo explained this, I had my "Eureka" moment (or rather my "How could I have been this stupid?" moment). Because of these defaults, the MAX aggregate was calculated over the wrong windows! Let's illustrate the concept for group A: </p> <p style="text-align: justify"> <a href="https://lessthandot.z19.web.core.windows.net/wp-content/uploads/2014/10/windows.png"><img class="alignnone size-full wp-image-3012" src="https://lessthandot.z19.web.core.windows.net/wp-content/uploads/2014/10/windows.png" alt="windows" width="204" height="189" /></a> </p> <p style="text-align: justify"> Because of the defaults, the first window is limited to only one row. This means the MAX aggregate will return the value 1. In the second window, two rows are included and MAX will return 2 and so on. By reversing the sort order, the value 4 will always be included in the windows, so MAX will return the correct answers. However dropping the ORDER BY is in my opinion the cleanest option to solve the problem. </p> <p style="text-align: justify"> <strong>Conclusion</strong> </p> <p style="text-align: justify"> Learn your T-SQL syntax and be aware of the defaults! Hugo also mentioned that ROWS <del>might</del> <strong>will</strong> have better performance than RANGE, so you better always specify your window frames to avoid the default. </p> <p style="text-align: justify"> <i>Update: I was contacted by the amazing Rob Farley who told me that ROWS will beat RANGE any day of the week and that you should always specify ROWS unless you really need RANGE.</i> </p>

SQL Server Days 2014 is over, but it has been one great event. A very big thanks to the organizers! My session went pretty well, somewhere around 60 attendees I would guess and they didn’t seem too bored. I saw some other great sessions as well, such as Buck Woody (twitter) on Big Data (and a fantastic keynote) and a demo session on Azure ML. I also attended Grants (twitter | blog) precon on execution plans and I definitely learned a lot.

I’ve been working with transactional replication in SQL Server a lot this year. A lot. I understood the basics of how it worked, but I wanted to know more – particularly about security, how log readers work, monitoring, and troubleshooting. So, I downloaded a free e-book from Simple Talk, Fundamentals of SQL Server 2012 Replication by Sebastian Meine. [ ]1 The description says the book “will walk you through setting up different replication scenarios. All hands-on exercises are designed with security best practices in mind.”

This year is flying by! September has been an excellent month. My 24 Hours of PASS Summit preview session went well, speaking for user groups is always encouraging, and IT/Dev Connections was a blast! Now, on to October! First up, I’m presenting at my user group, FoxPASS, in Appleton, WI on Wednesday, October 1. I’ll be delivering “Are Your Indexes Helping You or Hurting You’?” This session, which I’ll also be giving at PASS Summit in November, covers checking index basics such as which indexes support WHERE clauses, using included columns, statistics, and sargability to make sure your indexes support your workload. I hope to see you there!

It’s time for the annual PASS Board of Directors elections! If you work with SQL Server and aren’t familiar with the PASS organization, please take a few minutes to check it out. (Also note that if you aren’t a member yet, you won’t be able to vote this year.) PASS offers data professionals networking and training through local user groups; virtual user groups; in-person training events like SQL Saturdays, PASS BA Conference, SQL Rally, and PASS Summit; and virtual training events like 24 Hours of PASS. PASS has been instrumental in my professional growth over the last five years, and I give back to the organization as much as I can. I’m glad this a community-supported, community-run, volunteer-driven organization – it’s very grassroots.

All this negative junk flying around makes for a waste of a lot of time. Negative is good but there is always a positive. Although, I’m thinking…how about the real reason of me telling you I am voting for my friend, Grant Fritchey? Honestly, look at him Such a cuddly little teddy bear. How can you not want this guy leading? Even more, the way that light gleams off the top of his head. Don’t get me started about the sword.

Lately I have been using more and more the awesomeness of the Alt-button in SQL Server Management Studio (SSMS). What do I mean with this? While holding the Alt-button on your keyboard, you can select a block of text instead of lines of text. Note that this is not an exclusive feature, you can do this in some other applications as well (like Word and Notepad++). An alternative is holding Alt+Shift and moving the cursor with the arrow keys. This can be useful for example when working on a laptop without a mouse present and you don't really trust yourself holding Alt, the select button and moving the touchpad at the same time. This alternative doesn't work in every application though. In Word this keyboard shortcut is used to move the current paragraph.