Skip to content

Mercenary

I was listening to a few people talk at an event about the software they were working on. For some of them, there was a true passion in solving the problems they faced. However these weren’t applications that eased poverty with cleaner water, or more efficient transportation. These weren’t applications that dealt with medical issues impacting human health. In many cases these were simple, consumer applications that entertained people, but these developers were quite proud of the applications they worked on.

However the challenge of practicing their craft well elicited quite a bit of passion from these developers. They not only took a lot of pride in building an efficient set of methods in Java or C# quickly, they really enjoyed the process of taking a development task, writing code and tests, and delivering software that worked. They would even find pleasure in receiving a bug report, finding the issue, and delivering a corrected version of code. Most of all, however, they believed their software was making the world better in some way.

There were also plenty of developers that didn’t really care what they worked on. They didn’t find anything special about software development, and were happy to get assigned a task, write some code, and deliver it. They weren’t looking to produce shoddy applications quickly, and many of them were talented developers. They just didn’t much care where they worked or what the function the software performed.

Is this you? Are you willing to work on whatever application comes your way? Or do you have a passion to build something in particular, to work on projects that have some meaning to you? I think I’ve usually been one of the former, willing to work on any application. I’ve always been more concerned about the coworkers I have than the project we work on, but there are times I’ve felt our applications were a little special, and made a difference in some way.

Without a doubt I think most of us would prefer to work in an area that means something to us, an area that elicits some passion. I just wonder how many of us actively seek out such positions or projects.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and LibSyn.

Final Prep for SQL in the City

I can’t believe this will be the fourth SQL in the City in London next week. It’s amazing to me that we’ve managed to build this great series of one day events that is so popular with SQL Server professionals.

I’m heading back to London next week, a quick trip, but one I enjoy (once I’m there), as I get to see lots of old friends and meet new ones at the event. It’s always fun, and despite the long trip, it’s worth it.

However I still have lots of prep. A few days off  this week to spend with my kids (I’ve been gone too much lately), so I’ll be pressed to finish my talks this week, and then have lots of rehearsals next week before I fly (and probably more while flying).

If you’re in the UK and can get to London next Friday, we still have some space, and we’d love to talk to you about better database development, and administration, for your SQL Servers.

Document, then Install

I saw someone post a note that they had installed a new SQL Server and wanted to document the install. Did anyone have a good script or process for doing this?

I’ve done this myself, but lately it seems to be the wrong way of actually building systems. One of the things I have started to feel is important is that I should strive for consistency and known states for systems rather than works of art.  However if I’m installing systems and then documenting them, I think that I’m doing things backwards.

There has been a trend towards declarative actions in technology, whereby we tell the system what we want and it configures itself to arrive in that state. An example of how some this can be done is with Puppet. This is a case of the administrator essentially documenting what they want to be done first, and then letting the system put itself in that state. It’s almost like programming the installation and configuration of software, but with tools to make the process much smoother.

In this model, administrators don’t need to document the installs. They’ve already declared what needs to be done. If vendors change defaults in the future it doesn’t matter, as the installations will configure themselves to the same state, ignoring defaults and human expectations. This also results in much more consistently configured systems, something that’s critical for building a smooth software delivery pipeline.

The closest thing I’ve seen to this for SQL Server is the Finebuild project that Ed Vassie set up. That’s much better than unattended installs and I’ve seen Finebuild demo’d, and it looks good. I need to set it up so that I can ensure that every instance I set up is in a known configuration. I can have multiple configurations ready, but I’d rather choose one of my choices and then let the system apply the settings than remember to click a particular check box or change some item manually. I’d also appreciate this for DR and auditing, with some list of settings for each instance or host.

That just seems more like a 21st century way of working to me.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.6MB) podcast or subscribe to the feed at iTunes and LibSyn. 

Intermediate T-SQL: Window Functions

This is the third hour of a three hour set of presentations on intermediate T-SQL techniques and features.

This presentation covers the window functions that are available in SQL Server. Some of these functions were introduced in SQL Server 2005 and some in SQL Server 2015. We start with a brief introduction to what a window is, how a partition works, and then look at ordering and framing of rows. Then we cover the following in demonstrations:

ROW_NUMBER()

RANK(), DENSE_RANK(), and NTILE()

The OVER clause and windowing in SQL Server 2012

Using PARTITION BY and ORDER BY in the OVER clause

Default framing with RANGE and how ROWS are different

PERCENTILE() functions

This covers a lot of information in an hour, but I try to use small data sets to explain what is happening with the window functions and how they can improve the performance, as well as simplify the code, for a number of common queries.

Slides: TSQL_Windowing.pptx

Code: TSQL_Windowing_code.zip

Intermediate T-SQL: PIVOT, UNPIVOT, and APPLY

This is the second of a series of talks I put together on intermediate T-SQL topics that many database developers might not understand. In this talk, we cover the other join-type operators that exist in the FROM clause, outside of the INNER, OUTER, FULL, and CROSS join clauses. We break this hour into three main areas, covering:

  • PIVOT and crosstabs
  • UNPIVOT
  • APPLY

Most of the time is spent on the APPLY operator, which is arguably the most useful of the three. I show how APPLY is like an inner or outer join, and also how it can be used to improve performance of a scalar function, how it can be used to run some queries that are difficult with INNER JOINs, and how we can use this to easily find query plans and code in performance tuning.

The PIVOT operator is compared to a crosstab, which is similar. We see how to write a query using either method. We also show the UNPIVOT operator.

Slides: TSQL_IntermediateQueries.pptx

Code: TSQLPIVOT_Unpivot_Apply.zip

Intermediate T-SQL: Writing Cleaner Code

This session is an intermediate T-SQL session that helps users learn how to write better T-SQL code by covering a few items that many database developers might not be aware of. In an hour, users will lean how to:

  • write and use CTEs to simplify complex queries.
  • learn basic error handling, including recommendations for SQL Server 2012 and beyond with THROW.
  • use templates to store common code elements
  • tricks in SSMS to speed up code development
  • understand what a tally table is and how to use it to perform a few common tasks.

This is the beginning of a three hour series I have on intermediate T-SQL code.

Slides: TSQL_WritingCleanerCode.pptx

Code: TSQL_CleanerCode.zip

Fun Data Analysis

The first part of this article is very true: “… correlation doesn’t always equal causality.” It’s a fun look at statistics and there’s a short video from a news station that contains this other great quote: “… numbers can be tricky.”

As data professionals, we are often tasked with helping business people understand the information hidden in data. We look to identify patterns and relationships between the data that our applications collect or our companies purchase. However it’s not as simple as it might appear.

This week, I’m wondering if you have some fun stories, or perhaps some scary stories, on conclusions that you or others have reached with data.

When has a correlation been mistaken for causation by you?

I know that most people in business don’t have extensive statistics backgrounds, and I’ve often seen people fooled by finding simple or obvious patterns in data that didn’t necessarily match reality. I’ve seen plenty of money lost because of bad decisions made by incorrect data interpretations.

I once worked in a restaurant where a new chef decided to use sales data from the previous year to predict how many meals would be served. Using a growth factor from sales over the previous year, he increased his purchasing as the summer season approached. A month into summer, we realized that we were wasting almost twice as much food as we had in the past. I had a similar story where purchasing managers used a straight average of the previous three months that left is woefully short on imported wood.

Let us know this week if you’ve seen mistakes made from naive interpretations of statistics.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.0MB) podcast or subscribe to the feed at iTunes and LibSyn. 

SQL in the City 2014 – Two countries in two weeks

I have had a fairly busy travel schedule the last few years. While I don’t love sitting on airplanes and being away from home, I do look forward to the events and seeing the amazing #SQLFamily and community we have for SQL Server. I’ve also learned a few tricks to make travel smoother, which reduces stress.

It’s a good thing since the SQL in the City 2014 event schedule is going to be short in duration, but long in miles. We have two days coming this fall: London and Seattle. We open in London on Fri, Oct 24, 2014 and then a little over a week later will be in Seattle on Mon, Nov 3, 2014. It’s a packed schedule that has me flying 6,000 miles to the UK before returning home and getting ready for a 1,300 mile trip up to the Pacific Northwest.

However I’m excited for both events. London draws a bit crowd, with a variety of SQL Server professionals that are excited to learn new ideas, tips, tricks, and tools to help them in their professional work. We’ll once again be near the center of town, at St Grange, just off the St Pauls and Blackfriars Underground stations.

Seattle is a great town, and while I’m sure I’ll see some Summit attendees, I look forward to meeting lots of local SQL Server data professionals that won’t attend the Summit. I hope that if you’re in the area, you come to our event. After all, how can your boss say "no" to a free day of training? Especially if you’re not attending the Summit later that week. We’ll be at McCaw Hall, near the Space Needle.

It’s a short tour this year, but an exciting one. We’ll also continue to expand our SQL in the City seminars to other cities in the future, and hope we get the chance to meet more of you as we look to support, educate, and participate in this amazing SQL Server community.

Continuous Delivery for Windows?

I read a bit about the next version of Windows, which is coming in 2015 as Windows 10. I’m not sure how much I care about some of the changes coming in the OS, though having CTRL+V working in a command prompt is very welcome. However I did read this piece about the people testing the Technical Previews and was a little intrigued by one quote: “… Threshold testers … will have those features and fixes pushed automatically to them…”

Does that mean that Microsoft has re-engineered Windows to be integrated with a Continuous Delivery process? If so, then I think this is a good move. We’ve already seen SQL Server move to a pace that releases new versions every two years and bimonthly patches to fix issues. Imagine that we could get patches even more often, as bugs are fixed.

Also imagine that we could get those bugs quickly rolled back and patches pulled if there are issues.

I think that’s one of the interesting things for me. There have been patches in the past which caused, issues and were sometimes hard to remove. If new changes can be pushed out quickly, I’d hope they could be removed quickly. And with all the feedback that Microsoft gets from existing installations, I could even start to see custom patches built that are deployed to only certain configurations that are compatible with the patch.

Of course, that’s an ideal view. I suspect that we’ll still see overworked developers releasing patches that not only fix issues, but cause other problems, and at times, can’t be removed. At least we’ll probably get the patch to fix the patch, a little faster than in the past.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.0MB) podcast or subscribe to the feed at iTunes and LibSyn.

Version Control for Databases

This is based on my Team Based Development with Version Control talk, which has evolved a bit.

Abstract

Getting your database under version control is ultimately a way of communicating changes and collaborating with team members, maintaining an audit trail and ensuring you always have a version to roll back to.

In this session, we’ll cover why this gives you and your team a crucial advantage in reducing the risk in your development processes. We’ll also discuss another door that your team opens by adopting solid version control: setting up the fundamentals of continuous integration. You will learn what build automation means as a first step towards continuous integration and the value it brings as part of your evolving deployment process. We’ll also discuss branching strategies, and how to manage this for databases as part of your evolving deployment process.

Goals

  • Understand the value of version/source control for databases
  • Learn the tools, standards, patterns and best practices needed to manage a database from source control
  • Identify the necessary flow within a team needed to develop a database with source control

This talk includes the following demos:

  • Move database DDL into VCS manually
  • Link database DDL and lookup data with SQL Source Control
  • Make development changes and check into VCS
  • Setup a new developer DB linked to VCS
  • Changes flowing back and forth between developers
  • Resolving conflicts between development changes made by two people
  • Basic branching of code for bug fixes and linking a database for development work to an old branch

Length: 45-75 minutes. Shorter lengths with fewer demos.

Slides: Get Database Under Source Control.pptx (Slideshare)

Follow

Get every new post delivered to your Inbox.

Join 4,774 other followers