The Bit Bucket

How to kill off the Camtasia 2021 Launcher Pop Up

Camtasia is one of my favourite products. I use it regularly. I’ve been so excited to start to get to use Camtasia 2021 that was released just recently. It’s a nice step up from an already great product.

But what I didn’t like after upgrading, is that every time I started Camtasia, instead of the “normal” editing screen, I got a cutesy little popup that asked me what I wanted to do with the product today.

2021-04-30

SQL Interview: 14: Set operations using EXCEPT

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

UNION and UNION ALL are commonly used to combine two sets of rows into a single set of rows.

EXCEPT is another set operator.

Can you explain what it does?

Answer:

EXCEPT is used to remove any rows in the first set of rows, if the same rows appear in the second set.

2021-04-29

SQL: Password complexity rules for Azure SQL

Azure SQL (both Azure SQL Database and Azure SQL Managed Instance) both have different password complexity rules to SQL Server. I was reading an email discussion list and a poster asked where he could find the list of password complexity rules for Azure SQL. I said I’d never seen a list.

Well it turns out that there is a list, but not where you might have thought to look. They’re spelled out in this article:

2021-04-28

SQL Interview: 13: Impact of optimize for adhoc workloads

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Server Tuning Level: Advanced

Question:

SQL Server 2008 introduced a server option for optimize for adhoc workloads.

Can you explain the problem that it is designed to solve, what most commonly causes the problem, and what impact this option has when you enable it?

2021-04-27

T-SQL 101: 96 Choosing from alternatives with IIF in SQL Server T-SQL

In my last T-SQL 101 post, I described the CASE statement. Until SQL Server 2012, that was the only real option that we had for choosing between alternate values. In SQL Server 2012, Microsoft gave us another option with the IIF function.

The IF function is very similar to the IF function in Microsoft Excel. It takes three parameters:

  • A boolean value to check (normally this is an expression)
  • A value that will be returned if the first parameter is true.
  • A value that will be returned if the first parameter is false.

In the main image above, you can see the equivalent CASE statement and the simplified IF function equivalent.

2021-04-26

SDU Tools: Weekday Across Years in SQL Server T-SQL

Another request that I received a while back, for a new function to our free SDU Tools for developers and DBAs, was to be able to find the day of the week, for the same day and month, over a range of years. A simple example would be to find what day Christmas will be each year for the next ten years. So we’ve added a new function WeekdayAcrossYears.

It takes four parameters:

2021-04-23

SQL Interview: 12: Using UNION vs UNION ALL

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Intro

Question:

Consider the following code:

SELECT CustomerID, CustomerName 
FROM Sales.Customers
WHERE BusinessCategory = 'Cafe'

UNION

SELECT CustomerID, CustomerName
FROM Sales.Customers
WHERE City = 'Toronto';

A: What would be the difference in results if the query was changed to use a UNION ALL instead of the UNION?

2021-04-22

SQL Interview: 11 Adding a column in the middle of a table's columns

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

When you add a column to an existing SQL Server table, the column is added to the end of the table. The column will end up with the highest column_id.

If a developer asks you how a column can be added to the middle of a table instead, what is your advice and how would you approach the request?

2021-04-21

SQL: Try the new Cascadia Code as a font in SSMS

The good news is that Visual Studio 2022 has been announced, and if you haven’t read the announcements, the big deal is that it’s finally a 64 bit tool. You can still build 32 bit apps with it, but the tool is now 64 bit. I hope SQL Server Management Server (SSMS) will also follow it to 64 bit but that’s not likely to be known as yet.

However, one interesting part of the announcement is a new font designed to be used for development. It’s called Cascadia Code. And even though Visual Studio 2022 isn’t available yet, the font is. I’ve been trying it in SSMS and quite like it. Previously I was using Consolas.

2021-04-20

T-SQL 101: 95 Choosing options with CASE in T-SQL

You often need to choose from many outcomes, based upon a specific value. You might want to say if the value is 3 then choose Red, but if the value is 4 then choose Blue, and if the value is 5, then choose Green, etc. The way that you apply this type of logic in T-SQL is by using the CASE statement.

There are several ways you can write CASE statements. In the main image above, I have said that if the value is greater than or equal to 25, then say “Large”. If the value was between 20 and 24, then “Medium”. Otherwise (for all other values), choose “Small”.

2021-04-19