Sql-Server

In an earlier post, I discussed why bit columns can be useful in SQL Server Indexes.

I used an example of a transaction table that had an IsFinalized column. And if it did, then it’s very likely that I’d want to have it indexed. But what I didn’t discuss was whether that bit column made sense in the first place.

An IsFinalized column in a transaction table seems simple enough. You might think of it as “yes it’s complete”. But what does that actually mean? Chances are that something has occurred to make it finalized. Perhaps it has been posted, or it’s an invoice that has been paid, or it’s a payment that has been applied against another transaction.

2018-04-24

As I’ve pointed out in other posts, Betteridge’s Law of Headlines says you already know that the answer to this is no.

There is a persistent myth among SQL Server users that having more indexes always slows things down, particularly anything that needs to modify data (INSERT, UPDATE, DELETE, MERGE).

However, there are two aspects of this to consider:

The first is that you need to decide how much the modifications actually matter in the overall performance of the system. In an earlier blog post, I noted that when I trace typical transactional systems, I see reads making up about 99% of page I/O’s. Writes barely even appear. But I’m not saying they are irrelevant, just that you have to focus on what the system is spending most of its time doing.

2018-04-23

The other day I posted a shortcut about how to use SQL Server Management Studio (SSMS) to generate INSERT statements for data.

In one of the comments, Tom Corrigan asked if there was a way to do that from the command line.

The answer is yes. Apart from a variety of 3rd party tools, in May last year, Microsoft released tools to do just that.

The new tool is mssql-scripter and you’ll find an intro to it here:

2018-04-20

I spend a lot of time delivering presentations of various types. Many of those presentations involve showing code in either SQL Server Management Studio (SSMS) or Visual Studio (VS).

I’ve become quite fast at taking a default setup of SSMS and changing it to the fonts, etc. that I want to use for a presentation. Given how large these fonts are, I don’t want to use them for day to day work.

2018-04-19

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

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

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