The Bit Bucket

There is a SQL Server T-SQL function that calculates the number of days (or months, or years, or other time periods) between any two dates.

SELECT DATEDIFF(day, ‘firstdate’, ‘seconddate’);

But I regularly get asked how to work out the number of days between two dates, excluding the weekends (Saturday and Sunday).

I’ve seen a few functions around that do a pretty good job at this but many don’t work properly if you have DATEFIRST changed in your session ie: if you change the first day of the week.

2017-11-15

Something I really struggle with in this industry is when newcomers to a system want to change standards within existing systems because they think something else is better. It’s a sign of immaturity yet it often applies to people who should be senior. Many system architects fall into this category.

For example, a vendor system that I’ve been working with has single column primary keys in all tables, and all the primary key columns are named PKey_ID (I’ve changed it a bit to protect the guilty). Now I can’t say I like that naming at all, but that’s not the point. There are a large number of tables that already have that naming scheme.

2017-11-14

When writing and working with T-SQL in SQL Server, you’ll often come across the number 1033. In many cases, that will have the word English associated with it. But what are these numbers, and which ones does SQL Server know about?

The number 1033 comes from the reference to a Windows Locale ID. You’ll find the list of Windows Locale IDs here:

https://msdn.microsoft.com/en-us/library/ms912047(v=winembedded.10).aspx

It’s a big list with entries that look like this:

2017-11-13

Most of the time, we want SQL Server T-SQL queries to run as fast as possible, just the same as we want programs in any programming language to run as fast as possible.

But there are times when you want SQL Server to just sleep for a while, and without tying up system resources (like spinning around on the CPU).

Example of this would be waiting for new entries in Service Broker queues, or waiting to retry deadlocks.

2017-11-10

This is a really popular shortcut in SQL Server Management Studio (SSMS) but I continue to be amazed how many people aren’t aware of it.

Object Explorer is a very useful part of SSMS and you can drag pretty much any name that you see in it, across to a query window.

You could do the same for each column in the Columns list.

You might also realize that you can hover over the asterisk and see a list of columns:

2017-11-09

I love it when SQL Server gets brand new T-SQL language features.

SQL Server 2017 added a great new language feature called TRANSLATE. It’s been in some other products like Oracle for a while but it’s just been added to SQL Server.

Imagine I have a string like this:

’[02] 9992:2343’ 

But what I really want is one like this:

’(02) 9992-2343’

Up to SQL Server 2016, I could do this with a series of replace statements:

2017-11-08

Many people know that I have an issue with ORMs (object relational mappers). It’s worth spending a blog post to describe why.

Unfortunately, I spend my life on the back end of trying to deal with the messes involved. The following are the key issues that I see:

Potentially horrid performance

I’ve been on the back end of this all the time. There are several reasons. One is that the frameworks generate horrid code to start with, the second is that they are typically quite resistant to improvement, the third is that they tend to encourage processing with far too much data movement.

2017-11-07

I’m not a great fan of linked servers in SQL Server but they are often necessary. I really wish the on-premises product supported External Data Sources and External Tables. But in the meantime, what I see all the time, is people hardcoding server names like this:

SDUPROD2016.WWIDB.Payroll.Employees

That makes your code really hard to manage. One option to get around that is to use synonyms.

Instead of sprinkling references to that table all through the code, you can create a synonym for it like this:

2017-11-06

Occasionally I run into a need to clear out all objects in a schema in a SQL Server database. Sometimes it’s because I want to remove the schema itself. It’s often painful to do as I have to find all the types of T-SQL objects, and then often have to remove them in a particular order.

So I added a tool to my SDU Tools collection to do just that.

2017-11-03

The on-premises versions of SQL Server have the ability to connect one server to another via a mechanism called Linked Servers.

Azure-based SQL Server databases can communicate with each other by a mechanism called External Tables. I’ll write more about External Tables soon.

With Linked Servers though, I often hear people describing performance problems and yet there’s a configuration setting that commonly causes this. In Object Explorer below, you can see I have a Linked Server called PARTNER.

2017-11-02