The Bit Bucket

I often see people using SQL Server Management Studio (SSMS) and doing very repetitive editing tasks that could easily be carried out by using the selection and changing of rectangular regions of code.

The simplest example of doing this is to insert a bit of text on a number of rows. Take the following code as an example:

I’ve got the skeleton of a list of columns in a CREATE TABLE statement but let’s assume that I’m a “comma in front” person and want to put a few spaces and a comma, etc. in front of each column after the second. I could just put the cursor on the InvoiceID line and type what I wanted, then do the same again on the next line. I could do it on the first line, then select and copy it, and insert it into the front of every other line. But what I should do is put the cursor in front of InvoiceID, and while holding Alt-shift, use the down arrow to select the beginning of every line, then just type what I want.

2017-11-23

This post covers another pair of useful functions that are part of the SDU Tools collection.

The first tool AsciiOnly deals with situations where you need to convert Unicode data (multi-byte data) to ASCII data (single byte). It processes each character and if it’s not in the ASCII range, it replaces the character with the value that you supply.  If your second parameter is an empty string, you’ll effectively strip out the non-ASCII characters, but your replacement can also be one or more characters.

2017-11-22

I work with data. I understand that for most organizations, that the data they own is the most valuable asset the company owns.

One thing I’ve learned from working with data is that unlike application software, data generally outlives generations of applications, is often used by many different applications, and typically just morphs from shape to shape over time. It almost never gets totally refreshed.

This is a good thing.

2017-11-21

Memory is a precious resource on most systems today. SQL Server loves memory, so it’s important to avoid wasting it.

One common cause of wasted memory is what we call plan cache pollution. The plan cache is the region where SQL Server stores compiled query plans (and query plan stubs).

There are some nice new DMVs that give you details of what’s in that cache but nothing still compares to one of the older compatibility views sys.syscacheobjects. You can use it to see what’s in your plan cache:

2017-11-20

SQL Server 2012 added the function EOMONTH. It returns the end of the month. That’s useful but there are two things that bug me about it:

First: What’s with the name? Do we really have to save 3 characters from ENDOFMONTH, particularly when the same version added functions like DATETIMEOFFSETFROMPARTS? When I asked, I was told it was the name of the function in Excel. I can’t admit to being happy that SQL Server and T-SQL is following the naming of a function that someone added to Excel in the 1980’s in some adhoc way.

2017-11-17

Have you ever started to create an object using T-SQL in SQL Server, and thought, what’s the right syntax for this? I’ve worked with SQL Server since 1992 (version 4.2) and yet almost every time I go to create a function, I have to spend a few moments thinking about what the correct syntax is, because there are different types of functions (scalar vs table-valued, inline vs multi-statement).

SQL Server Management Studio has had templates for a long time, and they are useful. In fact, you can create your own. I’ll show that in another blog post soon.

2017-11-16

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