Posts

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

A few weeks back, I mentioned how useful templates can be. I said that I’d discuss how to create them later. Well that’s today.

I thought I’d take dropping a database as an example. The supplied template doesn’t work for me, so let’s create a new one.

Note: SQL Server 2016 SP1 introduced DROP DATABASE IF EXISTS but I find that option quite useless. It fails if anyone is connected to the database. And to disconnect people beforehand, you need to first check if it exists, so the statement is pointless.

2018-01-04

Is 2018 be a leap year?

Was 2000 a leap year?

Will 2100 be a leap year?

When I was a young student at school, we learned that leap years were every four years. Then as I got older, I learned that there was more to it than that.

It’s important to understand that any calendar is just an approximation, and there is a lot more to calendars than most people realize.

2018-01-03

Let me make a potentially bold statement:

People who can’t type write worse code than they could be writing

I’m sure that will upset some people (probably those who can’t type or who are two or four finger typists) but it’s a conclusion that I’ve come to over many years. Coding is clearly not the same thing as typing but the reason is simple:

To write good code, you need to be prepared to constantly refactor and rework the code that you write, and if you can’t do that quickly, you’ll be more reluctant to do it.

2018-01-01

In a recent blog post, I described converting strings to Proper Case and Title Case. And more recently, I described how to convert them to Pascal Case and Camel Case.

The final option (for now) in this set is conversion to Snake Case and Kebab Case.

In Snake Case, all words are lower-cased, then all spaces between words are replaced by underscores.

You can see it in this example:

2017-12-29

Everyone who’s worked with SQL Server for any length of time, has had the experience of executing a T-SQL script, and then noticing, with horror, that they’ve just executed the script against the wrong server.

You know the feeling. It even happens at Christmas time, just when you were hoping to get away from work for a few days, or when you are the unlucky one who’s doing on call work.

2017-12-28

When you first start working with SQL Server, you’re often working with smaller databases and every backup seems to happen quickly. SQL Server is fast at creating backups.

When you get to larger databases, backups start to take a while. Fortunately, backup is one of the commands that sets a percentage complete value that can be seen in the sys.dm_exec_requests system view.

And when the databases get even larger, the question quickly becomes:

2017-12-27

Over the years, I’ve had a surprising number of questions on how to right-align a set of numbers using T-SQL.

The first concept that people seem to miss is that numbers aren’t strings. So there’s no concept of actually aligning a number, it’s only a string representation of a number that can be right-aligned or left-aligned. Or how it’s displayed in a client application.

But if you really want to create a string that has right-aligned numbers, then left padding of the number is what you want.

2017-12-22

I’ve written before about how important it is to find code smells.

One of these relates to foreign keys. I’ve argued in previous posts about the importance of foreign keys but if you do have them in place, you need to index them.

When SQL Server creates a primary key, it creates an index under the covers to support the primary key. The index has the same name as the key. (And is one of the reasons why you should name your primary keys and not let the system do it for you). A primary key needs to be both unique and NOT NULL. So SQL Server creates an index so it can quickly check if a value already exists.

2017-12-20