sqlsunday.com downloads are now on GitHub!

Regular readers of my blog will know that I occasionally share some useful scripts on my Downloads page. And even though I update some of those scripts regularly when new versions of SQL Server come out, or if I run into a bug feature, there really hasn’t been a practical way for readers to subscribe to those updates or to contribute with good ideas.

I recently attended the annual PASS Summit conference in Seattle, and as part of my personal goal to try to learn new (and scary) things, I took a precon on working with Git.

So as of now, a bunch of downloads are available on GitHub (which is, really, a much better place to host scripts than a shared Dropbox link). You can download them as usual, and if you want, you can add your improvements and send me a pull request. I know I’ve received a ton of good ideas and suggestions over the years, but more often than not, I haven’t had the proper environment to test those changes in, or I just haven’t had the time to dig into my old code.

But now you can:

Conference hack: how to get great screenshots from demos

I take all my conference notes on my laptop, or occasionally on a tablet. Sometimes, I’ll want to take a screenshot of a powerpoint slide or a demo to add to my notes.

Here’s a trick to beautify those screenshots very easily:

The Microsoft Office Lens app (App store | Google Play) is an excellent document scanner, that you can also use to snap pictures of business cards, signs or basically anything rectangular with a little contrast around the edges – like a projector screen.

It’ll identify the framing and correct the image, so you can save it as a picture or a PDF, or beam it to another mobile device or computer.

Point it at the screen:

… and once you’ve taken the picture, it’ll beautify the image:

This is a killer app for conferences.

Make a Windows shortcut to compare files in Visual Studio

I like that there is a “Compare” function right out-of-the-box in Visual Studio, and even though many regular developers will choose to download a third-party application for the job, it’s perfectly fine for me.

Two problems: First off, I couldn’t find a straightforward way to open “compare” in the Visual Studio IDE without right-clicking an existing item in a source control repository. And second, wouldn’t it be cool if we could put a shortcut to it on the Windows “Send to” context menu?

Continue reading

The SQL Server Calendar project

I’m the type of developer that invents wheels. Yes, every wheel I design is unique in its own way, and hand-crafted for a specific purpose. And so it has also been with calendar dimensions (typically when I do data warehousing work).

This got me thinking – why not design the mother of all calendar dimensions? One that includes every conceivable calendar and property that I and others could use and re-use. One that could save me a ton of coding, and lessen the burden of having to validate it each and every time?

And that’s how I got started designing my one calendar script to rule the all.

Continue reading

Quick and dirty: How to right-align numeric columns in SSMS

Here are 50 random numbers:

--- 50 random numbers
WITH cte AS (
SELECT 0 AS i, 100000.*POWER(RAND(CHECKSUM(NEWID())), 3) AS n
UNION ALL
SELECT i+1, 100000.*POWER(RAND(CHECKSUM(NEWID())), 3)
FROM cte WHERE i<50)

SELECT *
FROM cte;

And in SSMS, with the variable-width default font, the output looks… slightly-less-than-readable in the grid view:

We could use STR() to format the output, but the indent looks a little off:

--- 50 random numbers
WITH cte AS (
SELECT 0 AS i, 100000.*POWER(RAND(CHECKSUM(NEWID())), 3) AS n
UNION ALL
SELECT i+1, 100000.*POWER(RAND(CHECKSUM(NEWID())), 3)
FROM cte WHERE i<50)

SELECT *, STR(n, 12, 2) AS with_str
FROM cte;

Here’s something I’ve found: the space character is roughly about half the width of a typical number character. So replace every leading space with two spaces, and it will look really neat in the grid:

--- 50 random numbers
WITH cte AS (
SELECT 0 AS i, 100000.*POWER(RAND(CHECKSUM(NEWID())), 3) AS n
UNION ALL
SELECT i+1, 100000.*POWER(RAND(CHECKSUM(NEWID())), 3)
FROM cte WHERE i<50)

SELECT *, STR(n, 12, 2) AS with_str,
       REPLACE(STR(n, 12, 2), ' ', '  ') AS with_replace
FROM cte;

(and terrible everywhere else, obviously.)