The Bit Bucket

In a relational database like SQL Server, transactions are the mechanism used to ensure that entire operations either complete or are rolled back. The obvious example used to be that if you transfer funds from one place to another, that both the debit and the credit need to occur, or neither occurs.

Fair enough and straightforward enough.

Computers also try to give you the illusion that you are the only one using them. Concurrent transactions are a place where that illusion breaks. While you are working in a transaction, you are potentially affecting other users of the system. So we try to manage how long transactions are held open for. The aim is to always have transactions protect what’s needed but be as short as possible.

2018-04-30

One of the discussions that comes up from time to time when working with DevOps is branching strategy.

One of the main features of Git that’s often claimed is that it’s so good at branching and merging. And indeed, it’s pretty good at that. But the bigger question is whether lots of branching is desirable in the first place.

One argument says that if you are using branches (let’s say to build features), that you really aren’t doing Continuous Integration (CI). The downside of being features in separate branches is that at some point, you’ll have to merge the code back in, and there’s probably going to be nothing automated about that. One software house that I’ve been mentoring in has a very large number of active live branches.

2018-04-27

SQL Server Management Studio (SSMS) is a highly configurable tool. One of the areas that’s often ignored but which can be quite important is color configuration.

SSMS color codes SQL scripts (and other types of files that it understands) as you type.

This is really useful but I’ve found on some systems that some of the color selections aren’t great. Here’s an example:

On many systems that I work with, the color for sys.tables in the query above is quite a fluoro green and almost unreadable. But if you don’t like this, you can change it.

2018-04-26

Having up to date statistics is critical for SQL Server choosing appropriate query plans. Poor statistics can lead to poor query plan outcomes.

Generally, SQL Server manages this well by itself. As a rough rule, most versions auto-update statistics when the number of changes is about twenty percent of a count of the number of rows in the table. (Some recent changes have slightly altered how this works but the ball park is pretty good).

2018-04-25

I’m so pleased to now have this book out. You’ll find it here:

http://ssmsbook.sqldownunder.com

I’ve worked with SQL Server for decades and have compiled this list of tips and tricks over that time. This eBook is a compilation of a series of blog posts that I have either made or are scheduled to be made as part of my Thursday “Shortcut” series, for shortcuts that apply to SSMS.

There are a lot of tips and tricks. It’s now not far from 200 pages.

2018-04-24

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