The Bit Bucket

For many years now, it has been important to script the configuration and deployment of systems, particularly virtual machines. Infrastructure as Code is now a common requirement but as the required configuration has become more complex, scripting in language like PowerShell has become more difficult.

It’s all very well to write code to add say a network adapter, but how do you check the current state of the machine?

• Did that adapter already exist?
• Is something else using the IP address?
• How do you write a script to a cater for all the situations?

This leads to ever-more complex code and this is where my concerns start. Writing code for creating infrastructure needs the same discipline that writing any other code does. This includes code quality, coding conventions, error handling, source code control and versioning. Yet, who is writing this code?

2018-01-19

Two key combinations used by SQL Server T-SQL developers every day are Ctrl-C and Ctrl-V for copy and paste.

But many users of SQL Server Management Studio (SSMS) don’t realize that it has a clipboard ring and can deal with several objects in the clipboard at the same time.

Let’s see an example.

In this screen shot, I’ve opened a query window with the source code of the AnalyzeTableColumns procedure from SDU Tools.

2018-01-18

Ever since I started working with SQL Server back in 1992, the #1 requested feature that I kept hearing about was a separate date data type. SQL Server had a smalldatetime and a datetime but these included both date and time within the same type.

Having date and time often led to odd bugs or performance issues, where people didn’t realize that times were included and tried to work with the values as dates.

2018-01-17

Many database developers (and other developers) seem to regard the endless use of abbreviations as some badge of honor. Don’t be one of these people.

Avoid abbreviations almost all the time.

I’ve written before about my dislike for the EOMONTH T-SQL statement. Given the same version introduced names like DATETIMEOFFSETFROMPARTS, surely we didn’t have to save 3 characters and could have had ENDOFMONTH. (I heard it was named this way to match the Excel function but matching something from another language that was created a long time ago isn’t the right answer here).

2018-01-16

When I’m looking at a database schema for the first time, there are a number of tell-tale signs that give me the hint that the developers really haven’t done much work with SQL Server before. They’ve made a “newbie” mistake.

One of those is the extensive use of the float data type.

Most times that I see this, the developers have come from a C or Java background and they assume that something that needs a decimal point in it, needs to be float. There are some situations where float makes sense, but 99% of the time what they should have used was decimal.

2018-01-15

One of the principles of DevOps is that you should be able to deploy regularly, with smaller low-risk changes. This addresses a key problem that I see in many sites:

• Each code release is large
• The code is difficult to test
• Code merges are painful
• Deployments take a long time
• Deployments often fail
• Deployments are considered to be high-risk procedures

The end result of this situation is that deployments are avoided and then a snowball effect occurs where:

2018-01-12

In an earlier post, I showed how to create a DROP DATABASE template in SQL Server Management Studio (SSMS). At the time, I mentioned that a template wasn’t the best option because a command like this is normally inserted into a script; it’s not the whole script.

That’s where snippets shine. Let’s create a snippet for it.

First let’s open Code Snippets Manager (Tools > Code Snippets Manager):

You’ll see the existing snippet folders. I’ve clicked Add, then created a new folder called GL_Snippets.

2018-01-11

 

When I first start to work with a database that I haven’t seen before, one of the things that I check immediately is the type of data being stored. The list of existing data types often gives me interesting insights into the design of the database, and how it interacts with application code.

The list of data types also shows me details of the consistency (or lack of) in the design.

2018-01-10

There’s a nasty trend that I’ve seen at a number of sites in recent years. It’s the tendency to try to block and or censor anything that the company thinks might be an issue. Some companies are so concerned about their IP (intellectual property) that they even try to stop any potential leak of that property.

While on the surface, that all might seem to make sense, it’s not sensible. It’s unproductive.

2018-01-09

I see so much written about how to scale SQL Server systems, and this generally starts with needing to improve SQL Server database performance. When I read articles from the SQL Server field support teams with titles like Top 10 Performance Problems for SQL Server, I often just smile.

The problem is one of perspective. If you are looking at the performance problems that are brought to the support teams to solve, you get a very, very skewed view of what’s typical.

2018-01-08