Press "Enter" to skip to content

Category: Wacky Ideas

Using Computed Columns to Avoid Scans without Changing Queries

Published 2020-05-08 by Kevin Feasel

Andy Mallon shares a trick you don’t want to use too often, but can get you out of a pinch:

We’ve all been there. You’ve got a query where the JOIN or WHERE predicate is not SARGable. You’ve read about how this can be a problem, and how bad it is for performance.

Alas, you cannot change the query. Sometimes this reason is political, sometimes it’s because you’ve got a third-party app and simply don’t have access to the code. But you do have access to the database…

This is the type of thing you learn about and use maybe twice in your career, and then you get frustrated with the third-party vendor which won’t fix their code.

Comments closed

Showing a Calendar in your Powershell Prompt

Published 2020-02-05 by Kevin Feasel

Jeffrey Hicks has fun with calendars in Powershell:

Some of you may be aware of my PSCalendar module which you can install from the PowerShell Gallery. The module contains commands that you can use to display a console-based calendar.  The calendar commands let you specify days to highlight. These might be days with special events or appointments. I typically use the Show-Calendar command as it writes to the host and colorizes output.

This command also has a parameter that lets you specify a position in your console. In other words, you can tell PowerShell where to display the calendar. I recently fixed a bug with the command that was producing less than optimal results. Now, I can use my PowerShell prompt function to display a calendar. 

The calendar module and functions are quite helpful, and the calendar prompt merits the Wacky Ideas category.

Comments closed

Refreshing Power BI from Your Outlook Calendar

Published 2020-01-16 by Kevin Feasel

Chris Webb has a nice use for Power Automate and Outlook:

The ideal way to view when multiple events are scheduled is a calendar and we’ve got great calendar functionality in Outlook. What if you could schedule refresh of your datasets from a calendar in Outlook? It turns out to be easier than you might think! Here’s how.

Read the whole thing, including Chris’s warning not to put it into production. You wouldn’t want the person with all of those calendar entries to leave your company and have things suddenly break, after all.

Comments closed

Fun With Database Names

Published 2020-01-03 by Kevin Feasel

Jason Brimhall takes us through database names you shouldn’t use:

Let’s figure we have a requirement to create a database with sensitive data. Due to the sensitivity of the data, it is classified confidential (for your eyes only, don’t talk about it and plug your ears if somebody starts talking about it). This is so sensitive that an apt name for the database could be anything like 🙈 or 🙉 or 🙊. Being smart, you know there are two more databases coming down the line so you only want to pick one of those for the name and not all three (though all three could make sense for a single database name).

Just because you can doesn’t mean you should…

Comments closed

Things to Stop Doing

Published 2019-12-31 by Kevin Feasel

Tom LaRock has a list of life-altering recommendations:

RESPONDING IMMEDIATELY

Stop doing that. Trust me on this, your response to that email/text/slack can wait. Don’t believe me? Try an experiment. For one month do not reply immediately to your emails. At the end of the month add up the number of emails you received, and the number of emails that required an immediate response. I’m willing to bet that the number is quite low, much lower than you realize. And once you realize just how few require an immediate reply, you’ll never look at email in the same way again.

It’s a fun list, and I definitely agree in most circumstances on at least 3/4 of them.

Comments closed

Why Disabling the Clustered Index is a Bad Idea

Published 2019-12-17 by Kevin Feasel

Kenneth Fisher has an experiment in mind:

You are probably already aware that you can disable an index. This can be handy when you have a large load and the load + re-enabling the indexes (you have to completely rebuild them) is faster than leaving the indexes in place. I’ve had pretty limited occasions where this has helped but it can be a handy trick at times. That said, this is only true for non-clustered indexes. What happens when you disable the clustered index?

Nothing good, that’s what.

Comments closed

Fun with CHAR(0)

Published 2019-11-18 by Kevin Feasel

Kenneth Fisher learns a bit about the 0 byte:

Ok, now things are getting interesting. An ASCII value of 0? I’ve never heard of that. I honestly didn’t know it was possible. As it happens, yes, it’s a real value and in SSMS it does a few strange things.

In the comments Denis Gobo is right: the 0 byte is the null terminator, which should appear at the end of a variable-length string to indicate that there’s nothing more to read there.

Comments closed

Auto-Deleting SQL Agent Jobs

Published 2019-10-25 by Kevin Feasel

Dave Bland takes us through SQL Agent job auto-deletion:

Have you ever looked at something in SQL Server and wonder why it is there?  That is what I think when I see this option in the SQL Server Agent job properties.  I can not come up with any good reason of why you would want a job to delete itself upon completion.  I even did a Google search and really didn’t find a good reason.  However, if you know of a great reason of why you would want to enable this, I would love to hear about it.

I’ve used it in the past for scheduling ephemeral work, particularly when I didn’t have the ability to control operations otherwise. For example, I need to perform a time-consuming one-time update on data, but I don’t want to tie it to a script on my machine because I wanted to go home that night. Creating a job which auto-deletes upon success lets me schedule it for when I want it to run, kick off the script, and not leave a mess behind in the SQL Agent jobs list. It’s a case where I don’t really care about history and checking the box gives me a quick indicator of success: if the job’s gone in the morning, my work here is done; if not, I need to begin troubleshooting.

3 Comments

Fun with [ as a Function in R

Published 2019-10-04 by Kevin Feasel

John Mount definitely quotes Dr. Ian Malcolm correctly in this one:

How about defining a new [-based function call notation? The ideas is: we could write sin[5] in place of sin(5), thus unifying the notations for function call and array access. Some languages do in fact have unified function call and array access (though often using “(” for both). Examples languages include Fortran and Matlab.

Let’s add R to the list of such languages.

I love the flexibility in the language, almost as much as I would enjoy taking away production rights from the person who ships this in my code base…

Comments closed

3D Effects in Power BI

Published 2019-09-10 by Kevin Feasel

David Eldersveld shows how you can use orthographic projection in Power BI:

The projection from three coordinates to a 2D plane is achieved by adding the following two measures. Be sure to adjust the column references and what-if parameter names at the top to correspond to your own data.

Here’s my “Ortho x” measure. The initial six bold values are what you’d need to adjust to your own data and parameter names.

David lays out a face, which is pretty neat.

Comments closed