Voice of the DBA

Tag: syndicated

Remember the Default Window
I ran across a question recently from a user about why they had strange results from a windowing query. This is better explained with an example, so let’s look at one.

I have some data in a table. This is NFL data, and a sample of it looks like this:

What I want to do is compare the passing yards each year with the most current value for that player, showing the plus or minus. This means that for Aaron Rodgers, who threw for 1675 yards in 2017, I’d want to show this for the first few years of his career:
```
  PlayerName  NFLYear PassYards Most Recent Yards Difference

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

Aaron Rodgers 2005    65        1675              -1610

Aaron Rodgers 2006    46        1675              -1629

Aaron Rodgers 2007    218       1675              -1457

Aaron Rodgers 2008    4038      1675              2363
```
This shows
me an easy view of the years where he was better in his career than he is now. Last year was likely a down year because of injury, but we’ll see this year.

In any case, if I run this query using LAST_VALUE() for the final year of his career, I don’t get the right results.

It seems as though in every row, I’m getting the current row as the last value, not the last value of the partition. My partition is by player, so I should only have a window for each player. In this case, I should have the years 2005-2017 for Aaron Rodgers. My ordering is by year, so the last value should be 1675.

Why isn’t it?

The reason has to do with the framing. As the window is consumed, the default values for the framing are between
- start – unbounded preceding
- end – current row
That means the first row for 2005 has the range of 2005-2005. The preceding rows are this row, and the current row is this row. For 2006, we have the first row as 2005 and the current row as 2006. The last value in this case is 46.

What we need to do is specify the entire window if we want that. In this case, we could use the current row as the start, but we certainly need the unbounded following rows.

This is a common mistake when writing window queries. I’d recommend you always include the partition and the framing to avoid any issues.
August 23, 2018
Format a Partial Script with #SQLPrompt
SQL Prompt is amazing and it’s my favorite tool. It’s actually what many people want first from Redgate because it makes coding much easier. One of the most used features is the reformat feature (CTRL+K,Y), since formatting really matters to developers.

One of the neat features of SQL Prompt is that you can format just a portionof your script. That means if you’re working on a large script and want to reformat just a part of it, you can.

Here’s how:
- Highlight the code
- Hit CTRL+K,Y
August 17, 2018
Another Brick in the Wall–T-SQL Tuesday #105

It’s that time again, and this month we have a good topic from Wayne Sheffield. We’re asked about getting stuck, about being blocked, about encountering a brick wall.

I have to say that I don’t think I encounter these very often, usually because I try to be effective, even if things get ugly in code. The important thing for me is to deliver value. That doesn’t mean I build slapdash applications and leave them. I do my best, and try to continue to learn to build things better. However, in the heat of the moment, I might need to do something silly that I end up either rewriting later or having someone else rebuild the solution.

With that…

The Large Aggregate

I once worked for a financial services company. This was decades ago, back in the age of spinning disks, SQL Server 6.5, and flip phones. We had a number of clients on our main application, but one large client that dominated our business.

We needed to provide quarterly summaries of activity and portfolio performance for all clients, but for this main customer, they also wanted us to add monthly reports. They were actually their own management firm, but we provided them with back end services. Within a month, they would have millions of transactions that we had to group by some organization and then calculate performance.

This wasn’t a big deal with other clients, though most of them had relatively simple calculations. For this client, however, they had separate rules for certain types of activity and the result was a calculation that we couldn’t easily get into CASE statements or other types of query workflows.

In trying to handle all their requirements, I (and my junior team), got stuck. We couldn’t come up with a set of queries that would work. We didn’t want to go down the road of writing custom queries for each organization as this would be an ongoing nightmare as new requirements were introduced.

We had delay after delay in trying to implement their monthly reporting and pressure kept mounting. Eventually we turned to using temp tables and making different passes through them to handle specific requirements. We had ugly flags in our temp tables to let us know which set of rules we’d applied and which we hadn’t.

The code worked, but it was slow. Too slow, in fact, and my boss wasn’t pleased. After a particular unpleasant dressing down, I was wondering if this was the career for me. I’d talked with friends, posted questions, but in this pre-SQLServerCentral era, there were limited places to get assistance. I had hit brick wall.

I never got to find a solution to the problems. My boss at the time was one of my worst, and he became petty. I started to get assigned various inane tasks, which were slightly annoying. However, on a night off, while on a date with my wife at a concert, he paged me with an emergency. I had to leave the show to answer and call in. When I did, he told me this was just a test to see if I would respond, then berated me for taking more than 5 minutes to do so.

I quit the next day.

Perhaps not the best reaction, but between the brick wall and poor treatment, I decided to move on. Hopefully someone else managed to solve the the performance issues and help the client.

August 13, 2018
The New SQL Provision Dashboard

As much as I liked the ability to quickly and easily build development and test databases with SQL Provision, I thought the dashboard of cloned databases was hideous. It left a lot to be desired, and frankly, the dark theme is annoying to me. Here’s my old dashboard.

I wasn’t alone, as various customers were asking about enhancements and additions. The team has been listening and I talked with them a few months ago during a meeting about possible ideas and designs. I saw an early mock up, and was hoping it would be released soon.

After coming back from vacation, I saw an update was available, so I applied it. After a few minutes, I saw this:

Once this was done, the page refreshed, and I saw the dashboard. I know, not much has changed, but look at the upper right part of the screen. There’s a blue box that says “Preview new dashboard”.

Once you click this, you get a new dashboard, which thankfully doesn’t use the dark theme. What’s nice is that I also get some information at the top of what my activity is. I can see the total clones and images, and the machines that are working or having issues.

I also have options for resorting the clones and images. I can change the sorting, which is set by the client, not the server. This means one person can see clones by instance, while another can see clones by image.

If I change this, you can see that I get a new view at the bottom.

There are more changes needed, and some coming. There is a feedback item when you switch to give feedback to the team, and I’d encourage you to do so. Certainly I think sizes or some calculation of total sizes for images and clones is needed. It would be nice to get filters for sizing, so I can also tell if someone is using a clone to do a lot of work and growing it’s size. One of the important things here is that you ought to not get to wedded to a particular clone. We want to rebuild these as needed.

I’d also like to see some way to link images to a source and perhaps group them so that I know how many copies I have of some database, like production. While I think we definitely need a couple of images at any time for rotation, we want to get control of our systems and limit the number.

If you have other feedback, let us know, and we’ll build a better dashboard together.

August 13, 2018