# SQL Musings

ORDER BY RAND()

## 2011-11-08

### The command line is dead... long live the command line!

So, MS wants us to believe that Powershell is what separates effective admins from non-effective ones. I'm on board. I'll take a CLI over a GUI most days. But seriously, give us a good place to run it. The Windows console sucks. Why can't I change the width of the window by dragging the side? I'm not even asking for dynamic word wrapping here, just the ability to widen the damn window. Why can't I select lines of text? Or double-click on a word and have the word highlight? Or extend my selection? Perhaps I'm being spoiled by my Unix roots where xterm and friends have been doing this for years (and by that, I mean decades). If you want me to take you seriously MS, make your CLI environment not suck.

## 2011-09-28

### Modulo difficulties, life is easy

The other day, I had a request from a BA. The gist of it was that we had a column in the database defined as a decimal(5,4) and yet the data as defined by the regulatory body only provided three places after the decimal point. He wanted to know if any data had somehow crept into the database that was utilizing the (seemingly superfluous) fourth place. On its face, this seems like a difficult problem. Admittedly, when I first heard it, I couldn't come up with something off the top of my head. But then I remembered that I have a plaque on my wall that says "B.S. Math", so I decided to leverage that. Enter the modulo operator.

Before we can talk about the modulo operator, we're going to have to flash back to when you learned long division. I know that it was traumatic for you, but trust me, this will bear fruit. If you recall, when you divide one number by another and that division doesn't result in "even division" (that is when the divisor goes into the dividend an integer number of times), you have the option of either expanding it into a decimal or just saying "screw it... here's what's left". By way of a worked example, if I divide 5 by 2, I can either say that it's 2.5, or 2 with a remainder of 1. In simplest terms, the modulo operator takes as arguments a divisor and a dividend and returns the remainder.

So how does that apply to the stated problem? That is, if I have an arbitrary number that's of the form 0.1234, how do I tell if there is a digit in the ten thousandths place? Here's what I did. If I multiply all such numbers by 10,000 and then evaluate that number modulo (mod for short) 10, that will return the ones place to me. If the result of that operation is not 0, then I have a non-zero digit in the ten thousandths place. For you code junkies out there:

The mod operator is useful in a lot of other situations. For instance, let's say that you need to break up a group into n smaller groups. Mod is your friend here. The following code will add a column to the result set called "g" that gives you the group number. In the example, I chose 2 as my modulus, so "g" will take values 0 and 1.

Have you ever needed to make a report where alternating rows had different formatting (and your reporting tool of choice doesn't support this natively)? The above code makes this almost trivial.

So there you have it. The mod function can be a useful tool in your toolbox. Have fun!

Before we can talk about the modulo operator, we're going to have to flash back to when you learned long division. I know that it was traumatic for you, but trust me, this will bear fruit. If you recall, when you divide one number by another and that division doesn't result in "even division" (that is when the divisor goes into the dividend an integer number of times), you have the option of either expanding it into a decimal or just saying "screw it... here's what's left". By way of a worked example, if I divide 5 by 2, I can either say that it's 2.5, or 2 with a remainder of 1. In simplest terms, the modulo operator takes as arguments a divisor and a dividend and returns the remainder.

So how does that apply to the stated problem? That is, if I have an arbitrary number that's of the form 0.1234, how do I tell if there is a digit in the ten thousandths place? Here's what I did. If I multiply all such numbers by 10,000 and then evaluate that number modulo (mod for short) 10, that will return the ones place to me. If the result of that operation is not 0, then I have a non-zero digit in the ten thousandths place. For you code junkies out there:

```
select value
from table
where convert(int, 10000*value) % 10 > 0
```

The mod operator is useful in a lot of other situations. For instance, let's say that you need to break up a group into n smaller groups. Mod is your friend here. The following code will add a column to the result set called "g" that gives you the group number. In the example, I chose 2 as my modulus, so "g" will take values 0 and 1.

```
select row_number() over (order by Id) as [g] % 2, *
from table
```

Have you ever needed to make a report where alternating rows had different formatting (and your reporting tool of choice doesn't support this natively)? The above code makes this almost trivial.

So there you have it. The mod function can be a useful tool in your toolbox. Have fun!

## 2011-08-26

### On why accurate testing conditions are vital

So, I was just reading an article detailing the account of how Osama bin Laden was killed. You can read it for yourself here. What struck me in reading it was when they explained why the helicopter crashed into the courtyard on initial entry. To make a long story short, when they set up a simulation for the Navy SEALs to practice on, the fence was chain link. The actual compound had solid walls. The problem is that the former allows the rotor wash to dissipate. The latter doesn't, and it causes interference with the helicopter. So, the testing conditions and the actual conditions were not the same. As a result, the live run didn't go like the tests had. Fortunately, the pilot was quick on his feet and was able to salvage the mission.

We as IT professionals can learn from this. When you test something, try your best to make the testing environment as much like the production environment as possible. OS patch level, size, quality of data, etc can all affect performance in one way or another. You can try to predict how, but often times it's just easier to simulate your situation as accurately as possible. Because if you don't, you may find yourself crashing into a wall. Will you be able to recover?

We as IT professionals can learn from this. When you test something, try your best to make the testing environment as much like the production environment as possible. OS patch level, size, quality of data, etc can all affect performance in one way or another. You can try to predict how, but often times it's just easier to simulate your situation as accurately as possible. Because if you don't, you may find yourself crashing into a wall. Will you be able to recover?

## 2011-08-12

### Converting between SID and account name

In a previous post, I mentioned the need to convert between Windows SID and the account name. A little Google-ing helped me create the following little powershell script arose

Save that in a file (I called mine "account_from_sid.ps1"). Now, you can say "account_from_sid" and get an account name back. Handy!

param(
[string] $sid
)
$objSID = New-Object System.Security.Principal.SecurityIdentifier ( $sid )
$User = $objSID.Translate( [System.Security.Principal.NTAccount] )
$User.Value

Save that in a file (I called mine "account_from_sid.ps1"). Now, you can say "account_from_sid

### I ♥ WinDirStat

So, part of my duties as an administrator entail being a janitor. Specifically, cleaning up files when a disk fills up. In the interest of working smarter and not harder, I like to use WinDirStat to find me the heavy hitters quickly. I especially like that it shows me what's in people's recycle bin (which, can we disable that universally on servers please?). The problem is that it gives the recycle bin's owner by SID. That's less than helpful when you need to find someone to ~~put the hurt on~~ have a conversation with. In a future post, I'll show you the powershell script that I came up with to do the conversion.

## 2011-05-13

### MS 70-432 passed

So... I have grand aspirations. With the reworking of the MCM program, I decided it was time to get some certifications under my belt (since they go towards something more grand now). Today, I took my first MS test and have to say that the experience wasn't horrible. I have a long way to go to the MCM, but you have to start somewhere.

## 2011-05-09

### Blaze your own trail

Not to brag, but I've been reading xkcd since before it was cool. The one from today struck a chord with me. Specifically, the second-to-last panel. It's been said many times, many ways before. I can think of a couple of other phrasings of it off of the top of my head: "Seek not to follow in the footsteps of the masters. Seek what they sought." and even "You cannot become that which you admire." The point of all of this is that I see so many people trying to succeed (by whatever metric success is measured by) by trying to mimic the actions of those that are considered successful. A lot of success, in my opinion, is circumstance. Being in the right place at the right time. Knowing the right people. So rather than try to duplicate success, try to figure out

*what*those you consider successful were trying to accomplish rather than*how*they did it. You have talents that they don't. Use them to blaze your own trail.
Subscribe to:
Posts (Atom)