The Bit Bucket

For anyone who’s worked with SQL Server for a long time, you’ll know that one of the common requests is that a table of numbers would be useful. Yes, just a table with 1, 2, 3, 4 and so on. Often in a query, you need just a certain set of values.

In our free SDU Tools, we’ve added a function to do that in a reasonably efficient way.

In the main screenshot, you can see it in use. We made it a little more flexible in that you can specify the starting number, and then specify the number of numbers.

2018-04-18

I just spent ages trying to work out why I couldn’t connect to Power BI Report Server using the Reporting Services Configuration Manager.

Amazingly, I hadn’t had to reconfigure it since I installed it, but now I needed to change the email account.

And it just wouldn’t see that it existed. If I put MACHINENAME only, it told me it couldn’t find the server, and if I put MACHINENAME\PBIRS, it said it was an invalid namespace.

2018-04-17

In my previous opinion post, I was discussing why magic values where a problem. They are often added by developers when they are trying to avoid having any NULL data in a database.

While discussing this, I think it’s worth also highlighting the overall dangers of placeholder values. These are values that aren’t magic values but are ones that are intended to be replaced at a later time.

If you use any of these, you need to have a solid process in place to:

2018-04-17

Today, we were having trouble saving a PowerPoint slide deck as a set of PNG files.

The error message said:

Sorry we couldn’t find slide1.PNG. Is it possible that it was moved, renamed, or deleted?

After trying to copy the slides into another deck to replace the original deck, the same problem existed. Saving in PPTX format was fine. Curiously, saving individual slides was also fine.

I found a few blog posts online that said it might be to do with an embedded period in the filename. That wasn’t the case but it gave me the clue that I needed.

2018-04-16

If you are aware of Betteridge’s law of headlines, you already know the answer, but let me explain.

There are a lot of odd myths that surround SQL Server. One of the more persistent ones is related to indexes on columns that hold bit values.

A SQL Server column that’s a bit can be in three states. It can have the value zero; it can have the value one; and it can be NULL (ie: have no value at all).

2018-04-16

Some time back, I decided to use a WordPress host for my blog because the time for me at sqlblog.com was coming to an end, community service was on its last legs, and WordPress seemed the obvious platform for a blog. Fellow MVP Adam Machanic made it really easy for me to migrate to a WordPress site with a tool that he had created.

Along with another site, I put them onto BlueHost and had reasonable hopes for them. However, a few weeks back I found that my blog had simply stopped working. I couldn’t even log into the control panel to work out what was wrong.

2018-04-13

Over the years, I’ve had a surprising number of questions on how to output all the data in a table as a series of INSERT statements. SQL Server Management Studio has had the ability to do this for a long time. Here’s an example.

In Object Explorer, I’ve expanded the WideWorldImporters database, then expanded Tables. Where people come unstuck is they right-click the table, and look at the scripting options:

2018-04-12

When I first start working with any SQL Server system, one of the first things I want to look at is how the server has been configured.

More particularly, what I’m interested in is:

What has been changed from the default value?

None of the standard SQL Server system views, however, provides me with this info. We do have the sys.configurations view. It shows you the configurations and what’s been chosen, but it doesn’t show you what the default value was:

2018-04-11

In  a recent blog post, I wrote about the ANSI_NULLS option for tables and after that I had several more questions related to NULL values.

Note that I mentioned NULL values, not values of NULL, and not values equal to NULL. That’s because “being NULL” is a state that a value is in, not itself a value. That’s why our queries say IS NULL and not = NULL.

Now because of that, many developers see values that  are NULL as a pain in the neck as they have to be dealt with separately, unlike other values. This can lead to many poor design decisions but I want to start today with the decision to use magic values.

2018-04-10

I’m involved in a lot of performance tuning work for SQL Server based applications. These are mostly your typical OLTP accounting, financial, or record keeping applications. One thing that constantly surprises me is that many developers and DBAs really aren’t sure about where their tuning efforts need to be focused.

In particular, what really surprises me is how much focus most people have on write/update behavior and how little on read behavior. I think there’s a perception problem.

2018-04-09